Print Page | Close Window

SQLDAOLoader.mdb (advanced)

Printed From: Roger's Access Library
Category: Roger's Access Library
Forum Name: Roger's Download Samples
Forum Description: A compendium of Microsoft Access 97 and 2000+ routines, each separated into its own sample database.
URL: www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=266
Printed Date: 28 Mar 2024 at 1:44pm
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com


Topic: SQLDAOLoader.mdb (advanced)
Posted By: Roger Carlson
Subject: SQLDAOLoader.mdb (advanced)
Date Posted: 22 Jul 2008 at 10:28pm

Please consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm - find out why here .
 

SQLDAOLoader.mdb

This sample shows how to modify the structure of a Back-End database using SQL DDL (data definition language) statements and DAO code.  This sample does much the same as "SQLLoader.mdb", but does not require text files to hold the SQL.  Further, it utilizes DAO to accomplish some tasks that Access SQL does not allow, like Default Values (and other field attributes), creating Autonumber and Hyperlink fields, and modifying Field Names.

All of the code is stored in modules this sample and behind this form.  The changes are stored in the UpdateSteps table also in this database.  Each record in UpdateSteps has the name of a subroutine and the necessary arguments.  They are as follows:

RunSQLDDL: a SQL String
RunModifyFieldName: Tablename, OldFieldName, NewFieldName
RunSetDefaultValue:Tablename, FieldName, DefaultValue
RunSetRequired: Tablename, FieldName, True/False
RunSetZeroLength: Tablename, FieldName, True/False
RunCreateAutoNumber:Tablename, FieldName
RunCreateHyperText:Tablename, FieldName
RunSetAutonumberPrimaryKey: Tablename, FieldName

Notes: 
Not ALL table properties are addressed above (ValidationRule and ValidationRuleText being two), but the process would be similar.  Autonumber and Hypertext attributes cannot be set programmatically after the table has been appended to the Tables collection.  Therefore, you have to create the field and set the Autonumber (or hypertext) attribute at the same time.   THEN set the primary key constraint.

Download
Access 97 (89 KB):
http://www.rogersaccesslibrary.com/forum/uploads/5/SQLDAOLoader97.zip - uploads/5/SQLDAOLoader97.zip
Access 2000 DAO (98 KB): http://www.rogersaccesslibrary.com/forum/uploads/5/SQLDAOLoader2K.zip - uploads/5/SQLDAOLoader2K.zip

-------------
Consider donating $1 per sample downloaded, http://www.rogersaccesslibrary.com/whydonations.htm" rel="nofollow - find out why here .<



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.03 - http://www.webwizforums.com
Copyright ©2001-2019 Web Wiz Ltd. - https://www.webwiz.net