Connecting to Access
 

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?

Connecting to Access
 

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.
--
(please remember to click yes if replies you receive are helpful to you)


"MikeH2" wrote:

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?

Connecting to Access
 

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





"MikeH2" wrote in message
...
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?