error displayed on rs.open when using late binding

  • Thread starter Thread starter sam
  • Start date Start date
S

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
 
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.
 
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 OLEDB:Database Password=pass; "

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


Thanks in advance
 
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
 
Thanks for the help ron,

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

Thanks in advance
 
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 OLEDB:Database 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.
 
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 OLEDB:Database Password=pass; "

Dim adOpenKeyset
Dim adLockOptimistic
Dim adCmdTable

adOpenKeyset = 1
adLockOptimistic = 3
adCmdTable = 2

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