error displayed on rs.open when using late binding

[sam]

Hi All,

I am getting an error on this line, when I used late binding.

rs.Open "Sheet1", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Am I missing something or doing something wrong? I am using ADO to connect.

Thanks in advance

 

[Jim Thomlinson]

What is the error? How did you declare rs? Did cn successfully connect?

Post the entire routine so we can see what is going on.

 

[sam]

Sorry for not posting it before. Here is my code.

Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=H:\Demo\Demo.accdb; Jet OLEDBatabase Password=pass; "

rs.Open "Sheet1", cn, adOpenKeyset, adLockOptimistic, adCmdTable


Thanks in advance

 

[Ron de Bruin]

Hi Sam

You must use the number for

adOpenKeyset

adLockOptimistic
....................


Set a reference to activex data objects and enter this in the Immediate window in the VBA editor

? adOpenKeyset

After you press enter you see the number



Maybe this page will help
http://www.rondebruin.nl/ado.htm

 

[sam]

Thanks for the help ron,

Do you know where I could find this number associations? and how to use them
here?

Thanks in advance

 

[Jim Thomlinson]

Assuming your table name to be sheet1 your code looks fine. One thing to note
is that with late binding the constant values that you normally get from the
library references no longer exist. You need to either hard code the numbers
or declare the constants yourself...

Public Const adCmdTable As Long = 2
Public Const adOpenKeyset As Long = 1
Public Const adLockOptimistic As Long = 3


Sub test()
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=H:\Demo\Demo.accdb; Jet OLEDBatabase Password=pass; "

rs.Open "Sheet1", cn, adOpenKeyset, adLockOptimistic, adCmdTable

End Sub

When I use late binding I always get my code runnig with early binding and
then modify it to work with late binding. Compiling shows me the things I
need to fix.

 

[Ron de Bruin]

You can do it like this

Set a reference to activex data objects and enter this in the Immediate window in the VBA editor

? adOpenKeyset

After you press enter you see the number

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[Ron de Bruin]

Oops

If you not see the Immediate window
Press Ctrl g




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

 

[sam]

Thanks for your help jim, I was getting an error with :

Public Const adCmdTable As Long = 2
Public Const adOpenKeyset As Long = 1
Public Const adLockOptimistic As Long = 3

But putting it this way worked:

rs.Open "Sheet1", cn, 1, 3, 2

The above example is live and working fine as of now, I havnt declared
"adOpenKeyset, adLockOptimistic, adCmdTable" But it is working as of now,
Also I tried it like this and it didnt give me any compile errors:

Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=H:\Demo.accdb; Jet OLEDBatabase Password=pass; "

Dim adOpenKeyset
Dim adLockOptimistic
Dim adCmdTable

adOpenKeyset = 1
adLockOptimistic = 3
adCmdTable = 2

rs.Open "Sheet1", cn, adOpenKeyset, adLockOptimistic, adCmdTable

 

[sam]

Thanks ron, this worked out for me. you can check my reply for Jim to know
what worked for me.