ADO Connection

[Alex]

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").CopyFromRecordset 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

 

[AA2e72E]

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?

 

[Alex]

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]

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.

 

[AA2e72E]

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

 

[Alex]

Thank you very much. I'll try to implement it.

 

[Tim Williams]

Define "not working". Does it error: if so what is the error number and description ?

Tim

 

[Alex]

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

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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

 

[Tim Williams]

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

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

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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

 

[Alex]

The exact message is "ODBC driver doesn't support the requested properties."

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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

 

[Alex]

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

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

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

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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

 

[Tim Williams]

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

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

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

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

:

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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

 

[Alex]

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.

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

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

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


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

:

Define "not working". Does it error: if so what is the error number and description ?

Tim

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").CopyFromRecordset 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