Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Connecting to Access Database from Excel | Excel Programming | |||
Connecting Excel, Access and SQL Server | Excel Programming | |||
Connecting to access | Excel Programming | |||
Connecting to Access using macro | Excel Programming |