[AccessD] Build tables programatically

Jim,

Thanks, that syntax is what I am looking for.

John W. Colby
www.ColbyConsulting.com


Hewson, Jim wrote:
> John,
> I have an Access 2003 database that uses A2007 runtime on a client's
> machine.
> The data store is located on the client's C:\ Drive so any changes made
> to the data store needs to be done through the front end.
> I use a function called from a Module... I still haven't wrapped my head
> around a class.
> When I modify the front end the links to the tables are created before I
> place a new copy on the client's machine.  Therefore all I need to do is
> "relink" the tables.
> 
> Here it is - HTH...
> 
> Function UpdateTable()
>     
>     Dim dbRemote As Database
>     Dim db As Database
>     Dim tDef As TableDef
>     Dim tdefs As TableDefs
>     Dim strFileName As String
>     
>     strFileName = "C:\DiaconateDatabase\DiaconateData.mdb"
>     
>     Set dbRemote = OpenDatabase(strFileName)
>     
>     dbRemote.Execute "CREATE TABLE tblConEdGroups (ConEdGroupID
> AutoIncrement, ConEdGroup Text(50), MaxPerYr Single, CONSTRAINT
> tblConEdGroups_PK PRIMARY KEY(ConEdGroupID));"
>     dbRemote.Execute "CREATE TABLE tblConEdQualifiers (ConEdQualifierID
> AutoIncrement, ConEdGroupID Long, ConEd Text(50), ConEdQualifier Single,
> ConEdUnits Text(50), ConEdPoints Single, Ordinal Single, CONSTRAINT
> tblConEdQualifiers_PK PRIMARY KEY(ConEdQualifierID));"
>     dbRemote.Execute "CREATE TABLE tblConEdReq (ConEdReqID
> AutoIncrement, Year Long, Requirements Single, CONSTRAINT tblConEdReq_PK
> PRIMARY KEY(ConEdReqID));"
>     dbRemote.Execute "CREATE TABLE tblContinuingEducation (ConEdID
> AutoIncrement, DeaconID Long, DateCompleted DateTime, Title Text(50),
> Location Text(50), ConEdQualifierID Long, Qualifier Single, CONSTRAINT
> tblContinuingEducation_PK PRIMARY KEY(ConEdID));"
>     dbRemote.Execute "CREATE TABLE tblDuties (DutyID Long, Duty
> Text(50), Ordinal Single, CONSTRAINT tblDuties_PK PRIMARY KEY(DutyID));"
>     dbRemote.Execute "CREATE TABLE tblParishDuties (ParishDutiesID Long,
> ParishID Long, DutyID Long, CONSTRAINT tblParishDuties_PK PRIMARY
> KEY(ParishDutiesID));"
>     
> 'Add Field -- BIT equals Yes/No
>     dbRemote.Execute "ALTER TABLE tblDeacon ADD COLUMN OrdinationYear
> Text(10);"
>     dbRemote.Execute "ALTER TABLE tblDeacon ADD COLUMN BackgroundCheck
> DateTime;"
>     dbRemote.Execute "ALTER TABLE tblDeanery ADD COLUMN DeaconID Long;"
>     dbRemote.Execute "ALTER TABLE tblParish ADD COLUMN DeaconsNeeded
> Byte;"
>     dbRemote.Execute "ALTER TABLE tblParish ADD COLUMN FutureDeacons
> Byte;"
>     
> 'Delete Field
> '    dbRemote.Execute "ALTER TABLE tblDeacon DROP COLUMN
> WPhoneExtension;"
> '    dbRemote.Execute "DROP INDEX PastorID ON tblParish"
> 
> 'Change the type or size of a field
>     ' dbRemote.Execute "ALTER TABLE tblDeacon ALTER COLUMN MyField
> TEXT(100);"
>     
> 'DROP Table - delete table no longer used
> '    dbRemote.Execute "DROP Table tblSurvey"
>         
>     dbRemote.Close
>     Set db = CurrentDb
>     Set tdefs = db.TableDefs
>     
> 'relink tables
>     For Each tDef In tdefs
>         If tDef.SourceTableName <> "" Then 'If the table source is other
> than a base table
>             tDef.Connect = ";Database=" & strFileName
>             tDef.RefreshLink 'refresh link
>         End If
>     Next
>     
>     Set dbRemote = Nothing
>     
> 'update queries. 
>    DoCmd.SetWarnings False
> 
>         DoCmd.OpenQuery "zUpdateConEdGroupsFields"
>         DoCmd.OpenQuery "zUpdateConEdQualifiersFields"
>         DoCmd.OpenQuery "zUpdateConEdReqFields"
>         DoCmd.OpenQuery "zUpdateContinuingEducationFields"
> 
>     DoCmd.SetWarnings True
> '
> End Function
> 
> Jim
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Wednesday, February 11, 2009 7:43 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Build tables programatically
> 
> For the class lecture it would be handy to be able to build Access
> tables programatically.  I do not 
> know how to do this, though the authors of books usually do so.
> 
> Does anyone on this list have any code library that would allow me to
> create field X with data type 
> Y, that is a PK etc etc.
> 
> Having that, I can build append queries to append specific data into
> these tables, but if anyone has 
> such a thing, code to extract actual data from a table and write the SQL
> Statements that would 
> append that into a specific table would be nice.
> 
> IOW (for example) I have an existing state table and an agency table
> with a state ID.  In order to 
> minimize the impact on the readers having to go manually create a state
> and agency table and 
> manually input data into those tables, it would be nice to provide code
> that builds those two tables 
> and then populates those tables with a few data elements.
> 
> Ideas anyone?
>