Connecting to Access 2000

[bontyboy]

Can you use this code to connect to a Access 2000 database. I was told that you have to use DAO 3.6. I am trying to connect to Access 2000 using ADO.

'references: 'ActiveX Data Objects 2.1 'ADO 2.1 for DDL and security

Dim tbl As ADOX.Table Dim cn As ADODB.Connection
Dim ax As ADOX.Catalog

Set cn = New ADODB.Connection 'connect

cn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=Nwind.mdb"

Set ax = New ADOX.Catalog ax.ActiveConnection = cn

'loop thru tables
For Each tbl In ax.Tables
Debug.Print tbl.Name & " " & tbl.Type
Next tbl

'close connection
cn.Close
Set cn = Nothing

[jsimpson]

You are using the correct Dll (ado 2.1) but your connection string is wrong for Access 2000 databases. It should be:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind.mdb"

In my opinion using DAO is better, if you look at the layering of data access components, ADO uses DAO which uses ODBC. ADO offers no real enhancement to database access, it is relatively new technology and contains the subsequent drawbacks of new technology. DAO is a much more mature technology and is quite powerful.

[bontyboy]

Thanks j

I was told that ADO is a better way to connect and manipulate the database ans offers more features using ADO.

thanks again for the tip.