CreateTable not working

[jessa_lee]

This is the code I took directly from a book I'm learning VBA from, and when I run it, I get a "Compile Error: User-defined type not defined" on the second line - "Dim Table As New Table", as well as any of the lines with "ADOX" or "ADODB" on them.

Sub CreateTable()
Dim Table As New Table
Dim Catalog As New ADOX.Catalog
Dim Key As New ADOX.Key
Catalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=H:/Projects/contacts.mdb"
Table.Name = "NewContacts"
Table.ParentCatalog = Catalog
Table.Columns.Append "ID", adInteger
Table.Columns("ID").Properties("AutoIncrement") = True
Table.Columns.Append "FName", adVarWChar, 20
Table.Columns.Append "LName", adVarWChar, 20
Table.Columns.Append "PhNum", adVarWChar, 36
Table.Columns.Append "Email", adVarWChar, 50
Table.Columns.Append "WWW", adVarWChar, 50
Catalog.Tables.Append Table
Key.Name = "ID"
Key.Type = adKeyPrimary
Key.Columns.Append "ID"
Catalog.Tables("NewContacts").Keys.Append Key, kyPrimary
Set Catalog.ActiveConnection = Nothing
End Sub

What am I missing?

 

[dcx693]

Are you using Access 97? If so, you may not be able to run that code unless you have installed some other software that will allow you to run ADO code.

If you're running Access 2000+, it sounds like you don't have a reference to the ADO library in Visual Basic. In the VB Editor, go to Tools, References and look for lines that say Microsoft ActiveX Data Objects 2.x Library amd Microsoft ADO Ext 2.x for DDL and Security.

 

[jessa_lee]

Thanks! I actually found that answer shortly after posting - I'm on 2000, just needed to set up the necessary references. I managed to get my CreateTable working

Now I'm on to a new problem. I made a new post for it... "DisplayFields" trouble.

 

[yeatmanj]

I've searched and searched, but this is the best example of how to create a table using code that I have found. The problem that I have is that the number of fields for the table will be variable. There might be 10 or 100 fields and the number will be determined by the number of records in a certain table.

I'll try to explain better.

2 tables (normalized), one contains a single record for a site, the other multiple records. I want to export this information together, but don't want to have 10 records for one site in the export. So I'm thinking I could create a loop function that would add fields to a recordset and create a table with a variable number of fields to contain the data, then dump the whole thing when the export is complete.

Any ideas?