[AccessD] Build tables programatically

Wow.  Thanks!

John W. Colby
www.ColbyConsulting.com


Stuart McLachlan wrote:
> 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
> 
>