Automatically create tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Could somebody help me with a code to automatically create tables. I have a table that i regularly import in Access. In that table i have a couple of fields where i could specify the properties of a table. For instance in field1 i have the tablenames, in field2 the fieldnames, in field3 the datatypes and field4 the characterlength.
How can i automate this by using vba to create tables? I would be very glad to achieve this.

Greetings,

Ezkiël
 
Ezekiël said:
Could somebody help me with a code to automatically
create tables. I have a table that i regularly import in
Access. In that table i have a couple of fields where i
could specify the properties of a table. For instance in
field1 i have the tablenames, in field2 the fieldnames,
in field3 the datatypes and field4 the characterlength.
Click to expand...
How can i automate this by using vba to create tables?
Click to expand...


Open a recordset to the table with the table name and field
specifications. Use the DAO method CreateTableDef to create
the table and loop through the recordset using CreateField
to add each field to the tabledef's Fields collection.
There are examples in Help (at least there was in A97) that
demonstrate what you want to do.
 
do you also know a ado method?

Marshall Barton said:
Open a recordset to the table with the table name and field
specifications. Use the DAO method CreateTableDef to create
the table and loop through the recordset using CreateField
to add each field to the tabledef's Fields collection.
There are examples in Help (at least there was in A97) that
demonstrate what you want to do.
Click to expand...
 
do you also know a ado method?
Click to expand...

This is in pure ADO code -- but it has not been tested, so treat it with
caution!!

' set up the SQL string
strSQL = "CREATE TABLE MyTable (" & vbCrLf

' look through each record
Do While Not rs.EOF
' comma unless it's the first one
If Right$(strSQL,1) <> "(" Then
strSQL = strSQL & ", "

End If

' put in the field name and type
strSQL = strSQL & rs!FieldName & " " & rs!FieldType

' add the size if it's needed
If rs!FieldType = "VARCHAR" Then
strSQL = strSQL & "(" & rs!FieldSize & ")"

End If

' Add in the Primary Key, Indexes, ValidationRules etc
strSQL = strSQL & rs!Constraints & vbCrLf

' Go round for the next one
Loop

' finish off the command and check it
strSQL = strSQL & " )"
MsgBox strSQL, vbOk

' and send it to the Connection object
' check these parameters!!
conn.Execute strSQL, adoCommandText, adoNoResultSetReturned

' all done


Hope that helps


Tim F
 
Back
Top