[AccessD] Build tables programatically

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?

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
################################################################################
If you have received this message in error, please contact the sender
immediately and be aware that the use, copying, or dissemination of 
this information is prohibited. This email transmission contains 
information from NCI Information Systems, Inc. that may be considered 
privileged or confidential and is intended solely for the named 
recipient.
################################################################################