[AccessD] Build tables programatically

Here's something off the top of my head to build an Agency table and populate it from string 
of values (pipe and comma delimited).  Not tested, so may require debugging.

Extractring current data from a table and building the string of values is left as an exercise 
for the reader :-)  

strSQL = "CREATE TABLE tblAgency " _  
    & "(AgencyID INTEGER NOT NULL, " _ 
    & "AgencyName TEXT(255) NOT NULL, " _
    & "StateFK INTEGER NOT NULL)" 
CurrentDB.Execute strSQL

strSQL = "CREATE INDEX idxAgencyID " _
               & "ON tblAgency (AgencyID)" _ 
                & "WITH PRIMARY"
CurrentDB.Execute strSQL

strSQL1 = "INSERT INTO tblAgency " -
               & "(AgencyID,AgencyName,StateID)" _
               & "VALUES ("

strData = "1|Agency1|1,2|Agency2|1,3|Agency3|2,4|Agency4|1"
arrData = Split(strData,",")

For lngLoop = 0 to UBound(arrData)
   arrFields = split(arrData(lngLoop)
   strSQL2 = arrFields(0) & ",'" _
                 & arrFields(1) & "','" _ 
                 & arrFields(2) & ")"
   CurrentDb.Execute strSQL1 & strSQL2
Next

Here's an excellent primer on using SQL in Access. It gives a fair bit of detail about these 
sorts of operations:

Fundamental Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/en-us/library/aa140011(office.10).aspx


On 11 Feb 2009 at 8:42, jwcolby wrote:

> 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