Create Access Table with ADO in Excel 2000

[MrExcel]

From Excel VBA, I am trying to create a table in an existing Access database.

I had been using ADO in the project. The only MSDN articles I could find on creating a table used ADOX. In Excel 2002, I added a reference to the Microsoft ADO Ext 2.7 library, and the code works. This is code that creates a new ADOX.Catalog, defines a new ADOX.Table, then does cat.Tables.Append.

When I try to run this on Excel 2000, it does not work. The reference to ADO Ext 2.7 is missing. The same DLL is there, but it is an older version. I tried copying the new DLL over the old DLL, but that does not work.

I've got to believe there had to be a way before Excel 2002 to create a table in an Access database programatically.

I've found documentation on how to do this with DAO. Is there any hassle (other than increased overhead) in having 98% of the project use ADO, but this one-time call to create the table use DAO?

Any suggestions greatly appreciated.

Bill

 

[dk]

From Excel VBA, I am trying to create a table in an existing Access database.

I had been using ADO in the project. The only MSDN articles I could find on creating a table used ADOX. In Excel 2002, I added a reference to the Microsoft ADO Ext 2.7 library, and the code works. This is code that creates a new ADOX.Catalog, defines a new ADOX.Table, then does cat.Tables.Append.

When I try to run this on Excel 2000, it does not work. The reference to ADO Ext 2.7 is missing. The same DLL is there, but it is an older version. I tried copying the new DLL over the old DLL, but that does not work.

I've got to believe there had to be a way before Excel 2002 to create a table in an Access database programatically.

I've found documentation on how to do this with DAO. Is there any hassle (other than increased overhead) in having 98% of the project use ADO, but this one-time call to create the table use DAO?

Any suggestions greatly appreciated.

Bill

Hi Bill,

Other than the overhead (as you said) the only problem might be if the objects haven't been properly identified when you were Dimming them e.g. if you had:-

Dim r as RecordSet

Both DAO and ADO have a Recordset object and the type used would be determined by which came first in Tools, References. Obviously, this can be easily clarified by doing this:-

Dim adoRS As ADODB.Recordset
Dim daoRS as DAO.Recordset

Sorry if that was obvious

Anyway, here is some code which creates a table in an Access 2000 database using Excel 2000. I've used ADO version 2.5 (2.1 also works) and ADOX version 2.5 (I don't have version 2.7 at the moment).

I hope it helps,

Option Explicit

Sub CreateTable()
Dim adoxCatalog As ADOX.Catalog
Dim adoxTable As ADOX.Table
Dim adoCN As ADODB.Connection


'Establish connection to the database
Set adoCN = New Connection
With adoCN
    .Provider = "Microsoft.Jet.OLEDB.4.0"   '4.0 for Access 2000, 3.51 for Access 97
    .ConnectionString = "C:temptest.mdb"
    .Open
End With
    
    
'Now associate our connection to a Catalog object
Set adoxCatalog = New ADOX.Catalog
Set adoxCatalog.ActiveConnection = adoCN


'Create the table definition
Set adoxTable = New ADOX.Table
With adoxTable
    .Name = "CreatedByADOX"
    .Columns.Append "ItemID", adInteger
    .Columns.Append "ItemDescription", adVarWChar, 100
    .Columns.Append "ItemValue", adCurrency
    .Keys.Append "PrimaryKeyItemID", adKeyPrimary, "ItemID"
End With


'Append the table to the database
adoxCatalog.Tables.Append adoxTable


'Clean up and close

End Sub

This message was edited by dk on 2002-03-13 05:08

 

[ajmorales]

MsAccess from MsExcel2003

I am new to this ADOX stuff! Any recommendations for a compile error; "User Defined Type not identified" when attempting to run the above CreateTable() code? I have MsExcel2003 & MsAccess2003 installed from Office2003.
Thanks
AJ