| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
error displayed on rs.open when using late binding
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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. -- HTH... Jim Thomlinson "sam" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "sam" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
Thanks for the help ron,
Do you know where I could find this number associations? and how to use them here? Thanks in advance "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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. -- HTH... Jim Thomlinson "sam" wrote: 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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "sam" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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 "sam" wrote in message ... Thanks for the help ron, Do you know where I could find this number associations? and how to use them here? Thanks in advance "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
Oops
If you not see the Immediate window Press Ctrl g -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Thanks for the help ron, Do you know where I could find this number associations? and how to use them here? Thanks in advance "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "sam" wrote: 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 "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "sam" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
error displayed on rs.open when using late binding
Thanks ron, this worked out for me. you can check my reply for Jim to know
what worked for me. "Ron de Bruin" wrote: Oops If you not see the Immediate window Press Ctrl g -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... Thanks for the help ron, Do you know where I could find this number associations? and how to use them here? Thanks in advance "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "sam" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| From Early binding to Late binding | Excel Programming | |||
| Late binding to Excel from Access causing Object error | Excel Discussion (Misc queries) | |||
| Run-time error using late binding | Excel Programming | |||
| Late Binding examples of binding excel application | Excel Programming | |||
| EARLY binding or LATE binding ? | Excel Programming | |||
Linear Mode
