Creating a new table in an external database using code

I often wondered how to do this - especially if you needed a new table with an Autonumber ID!  This is how I did it. (For some reason the line breaks refuse to display, and I’ve put in double breaks to be sure - I hope you can make it all out!

Sub CreateNewTable(datapath) ’datapath is the full directory path of the database where the table is to be created

Dim dbsData As Database

Dim tdfnew As TableDef

If right(datapath,1)<>”\” then datapath = datapath & “\”

Set dbsData = OpenDatabase([datapath] & “data.mdb”)

‘ Create a new TableDef object.

Set tdfnew = dbsData.CreateTableDef(”NewTable”)

With tdfnew

.Fields.Append .CreateField(”NewTableAutoID”, dbLong, 0)

.Fields(”NewTableAutoID”).Attributes = dbAutoIncrField

.Fields.Append .CreateField(”TextField”, dbText, 0)

.Fields.Append .CreateField(”MemoField”, dbMemo)

.Fields.Append .CreateField(”DateField”, dbDate)

dbsData.TableDefs.Append tdfnew

End With

‘Link with newly-created table

DoCmd.TransferDatabase acLink, “Microsoft Access”, [datapath] & “data.mdb”, acTable, “NewTable”, “NewTable”, True

End Sub

This entry was posted on 04/01/2011 at 07:18 pm and is filed under Code. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.