Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
I have an issue with the different recordsets from the same DB. I'm trying to
establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Slightly mysterious!
Does the second recordset open on the 'other' computer if opened first? I assume that it is rstData.Open that is failing and not rstData.RecordCount. You are using early bound ADO; so you would have a particular version checked-in: does that version exist on the other computer? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Thank you very much for your quick response. That's right some mistery.
Probably, some simple mistake involved. The code cannot open the second recordset on that 'other' computer. I've checked References on the both computers there is the same ActivX Data Object 2.5 Library. Can it be that early binding? How could I use the late binding here? Please, advise. "AA2e72E" wrote: Slightly mysterious! Does the second recordset open on the 'other' computer if opened first? I assume that it is rstData.Open that is failing and not rstData.RecordCount. You are using early bound ADO; so you would have a particular version checked-in: does that version exist on the other computer? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Some suggestions:
1. The current version is 2.8; I would recommend that you download this and install on both/all PCs. (2.5 is quite old; sounds like your OS is not XP). Look for MDAC2.8. 2. Use the JET provider instead of the Access ODBC driver; it is more robust. 3. Unless you need the connection object specifically, use the Recordset object only: the first argument is your SQL, the second is your Connection string (does not have to be a connection object). 4. With Early Binding (what you are using) you have the advantage that Intellisense helps you out; however, if you distribute your code, the specific instance of your ActiveX must exist on the target PCs. With Late Binding, there is no need to check-in any references and you do not get Intellisense (except sometimes when you are tracing the code using F8 in the IDE). At runtime, you use the version of the ActiveX that exists. Set rstData = CreateObject("ADODB.RecordSet") rst.Data.Open "SELECT * FROM TABLE" "DSN=.." where "SELECT * FROM TABLE" is your SQL "DSN=.." is your connection string (as you have it) or a provider or DSN etc If you use a DSN, use a SYSTEM DSN and create it on the target machines as well. DSN-less connections (as you have it) are less troublesome. .... at the end rstDAta.Close set rstData = Nothing 5. Watch out for table and field names that have a space (quite common in Access but inadvisable): the Access Query turns the space into an underscore. If you retrieve the SQL from ACCESS, and use it elsewhere, this substitution causes problems. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
I forgot to mention another important downside of Late Binding: the
constants, like adOpenStatic, adOpenKeyset etc cannot be used unless you assign them their corresponding values. As well as MDAC2.8, you may want to download the SDK: it contains of several help files that contain code for several languages including VB (VBA can just use such code) AND you get the values of all the ADO constants. PS: If you are using Options Explicit, you would Dim rstData as Object |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Thank you very much. I'll try to implement it.
"AA2e72E" wrote: I forgot to mention another important downside of Late Binding: the constants, like adOpenStatic, adOpenKeyset etc cannot be used unless you assign them their corresponding values. As well as MDAC2.8, you may want to download the SDK: it contains of several help files that contain code for several languages including VB (VBA can just use such code) AND you get the values of all the ADO constants. PS: If you are using Options Explicit, you would Dim rstData as Object |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Define "not working". Does it error: if so what is the error number and description ?
Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Thank you very much for your response, Tim.
Now, on that 'other' computer it's saying something as 'ODBC drivers cannot provide required functionality". I think the problem is with the adOpenKeyset in the recordset. I cannot be accepted on that 'other' computer. Do you have ani idea how to get rid of it? Thanks "Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Instead of returning all of the records only so you can count them, why not just count them in SQL ?
SELECT count(dbo_Product.[Product_ID]) AS NumRecs... Then : rstNum = rstData.Fields("NumRecs").value and use adOpenStatic and not adOpenKeyset. That should work unless you cut out some code in that second section and you're doing something else with the second RS. -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Thank you very much for your response, Tim. Now, on that 'other' computer it's saying something as 'ODBC drivers cannot provide required functionality". I think the problem is with the adOpenKeyset in the recordset. I cannot be accepted on that 'other' computer. Do you have ani idea how to get rid of it? Thanks "Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
The exact message is "ODBC driver doesn't support the requested properties."
"Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Hi Tim,
You're right I continue to use the second recordset: If rstNum 0 Then With rstData .MoveFirst .MoveLast .MoveFirst Do Until .EOF strProdCode = rstData![CutProd] ..... I was trying your approach and the late binding but nothing helps. Any further ideas how to get it owrking? Thanks "Tim Williams" wrote: Instead of returning all of the records only so you can count them, why not just count them in SQL ? SELECT count(dbo_Product.[Product_ID]) AS NumRecs... Then : rstNum = rstData.Fields("NumRecs").value and use adOpenStatic and not adOpenKeyset. That should work unless you cut out some code in that second section and you're doing something else with the second RS. -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Thank you very much for your response, Tim. Now, on that 'other' computer it's saying something as 'ODBC drivers cannot provide required functionality". I think the problem is with the adOpenKeyset in the recordset. I cannot be accepted on that 'other' computer. Do you have ani idea how to get rid of it? Thanks "Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
No need to use RecordCount if all you want to find out is whether you have any records. Were you using it for anything else?
'***************** if not rs.eof then do while not rs.eof 'do stuff rs.movenext loop end if '***************** Tim -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Hi Tim, You're right I continue to use the second recordset: If rstNum 0 Then With rstData .MoveFirst .MoveLast .MoveFirst Do Until .EOF strProdCode = rstData![CutProd] .... I was trying your approach and the late binding but nothing helps. Any further ideas how to get it owrking? Thanks "Tim Williams" wrote: Instead of returning all of the records only so you can count them, why not just count them in SQL ? SELECT count(dbo_Product.[Product_ID]) AS NumRecs... Then : rstNum = rstData.Fields("NumRecs").value and use adOpenStatic and not adOpenKeyset. That should work unless you cut out some code in that second section and you're doing something else with the second RS. -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Thank you very much for your response, Tim. Now, on that 'other' computer it's saying something as 'ODBC drivers cannot provide required functionality". I think the problem is with the adOpenKeyset in the recordset. I cannot be accepted on that 'other' computer. Do you have ani idea how to get rid of it? Thanks "Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection
Thank you very much, guys.
I'm using both approaches (late binding from AA2e72E and Tim's advice) and everything is working now. Thanks a lot. "Tim Williams" wrote: No need to use RecordCount if all you want to find out is whether you have any records. Were you using it for anything else? '***************** if not rs.eof then do while not rs.eof 'do stuff rs.movenext loop end if '***************** Tim -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Hi Tim, You're right I continue to use the second recordset: If rstNum 0 Then With rstData .MoveFirst .MoveLast .MoveFirst Do Until .EOF strProdCode = rstData![CutProd] .... I was trying your approach and the late binding but nothing helps. Any further ideas how to get it owrking? Thanks "Tim Williams" wrote: Instead of returning all of the records only so you can count them, why not just count them in SQL ? SELECT count(dbo_Product.[Product_ID]) AS NumRecs... Then : rstNum = rstData.Fields("NumRecs").value and use adOpenStatic and not adOpenKeyset. That should work unless you cut out some code in that second section and you're doing something else with the second RS. -- Tim Williams Palo Alto, CA "Alex" wrote in message ... Thank you very much for your response, Tim. Now, on that 'other' computer it's saying something as 'ODBC drivers cannot provide required functionality". I think the problem is with the adOpenKeyset in the recordset. I cannot be accepted on that 'other' computer. Do you have ani idea how to get rid of it? Thanks "Tim Williams" wrote: Define "not working". Does it error: if so what is the error number and description ? Tim "Alex" wrote in message ... I have an issue with the different recordsets from the same DB. I'm trying to establish one connection and open and close one recordset and after that to open and close another recordset. On my computer everything is working very well. However, on another computer the only first recordset is working. Dim Cnxn As ADODB.Connection Dim rstData As ADODB.Recordset Set Cnxn = New ADODB.Connection strCnxn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=...;" Cnxn.Open strCnxn Set rstData = New ADODB.Recordset rstData.Open "SELECT ...", Cnxn, adOpenStatic Worksheets(CodeSheet).Range("A2").CopyFromRecordse t rstData rstData.Close Set rstData = Nothing ' *** This recordset is not working on another computer but working very well on mine****** Set rstData = New ADODB.Recordset rstData.Open "SELECT dbo_Product.[Product_ID] AS NotDisc" _ & " FROM dbo_Product" _ & " WHERE (dbo_Product.[Product_ID] = '" & Code & "' AND dbo_Product.[Discontinued]= 0)", Cnxn, adOpenKeyset rstNum = rstData.RecordCount rstData.Close Set rstData = Nothing Another similar thing that isn't working on another computer but working on mine is: I have a form (the same app) and I'm establishing the connection on the form opening and close it with the form unloading.The recordset isn't working there. The connection name is the same Cnxn. I've tried to give different names to the connections and recordsets but it doesn't help. Please, advise what can be wrong with all this stuff. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
no DDE connection?? | Excel Discussion (Misc queries) | |||
ADO Connection Help | Excel Programming | |||
Connection using ADO | Excel Programming | |||
SQL Connection | Excel Programming |