Connecting to Access

[MikeH2]

I am trying these commands:
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=" &
DataS
Set rs = New ADODB.Recordset
rs.Open "Transfers", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
Sometimes It works and in another module on another spreadsheet, I get a
compile error: User-Defined Type not Defined and it highlights the cn As
ADODB.Connection on the first line. Why do I get two different situations.
I think my code is pretty much the same otherwise. I did the one that works
several months ago and the new code just today and I can't find any
difference! Ideas?

 

[SeanC UK]

Hi Mike,

One thing you can check is that in each spreadsheet in which you are using
the code, the correct Library has been refernced for using ADODB. If you have
copied modules or code from one spreadsheet to the next then the copied
versions will not have the relevant references. In the VBE goto Tools >>
References and make sure one of the Microsoft ActiveX Data Object libraries,
or equivalent, is being referenced.

I imagine that is the problem.

Hope this helps,

Sean.

 

[Conan Kelly]

MikeH2,

I'm guessing you are missing a reference to one of the ADO libraries.

In the VBE, make sure you have an object/item from your problematic workbook
selected in the project explorer. Then Tools > References..., then scroll
through the list for Microsoft ActiveX Data Objects #.# Library and put a
check mark in the check box. I'm not sure which one will work best for
you...I'd just select the highest version.

HTH,

Conan