Multiple Recordsets

[Guest]

How do I open more than one table at a time in the same database using ODBC
and ADO? The code below results in an error when attempting to open the
second table.


Sub OpenMultTables()
'
' Create the Recordset attributes
'
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rcd_cnt1 As Integer
Dim rcd_cnt2 As Integer
'
' Initialize the Recordset attributes
'
rcd_cnt1 = 1
rcd_cnt2 = 1
'
' Create the Recordset objects
'
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
'
' Open the recordset objects
'
With rs1
.Source = "Table1"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

With rs2
.Source = "Table2"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub


Thanks for your assistance.

 

[Alex Dybenko]

code looks ok in general, what error do you get?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

I am currently getting a 3704 on the second close. I don't recall the
original message. With subsequent attempts the message changes. I can
normally close Access and then reopen and start over but now I get the 3704
repeatedly.

 

[Guest]

A previous message was:

[Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung;
'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.

which basically says that there was an illegal SQL command and that it was
anticipating a DELETE, INSERT; SELECT OR UPDATE which is not exactly the
message you expect to get under the current circumstances.

 

[Alex Dybenko]

Well, I would check that SQL and table names are correct typed...

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

A previous message was:

[Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung;
'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.

which basically says that there was an illegal SQL command and that it was
anticipating a DELETE, INSERT; SELECT OR UPDATE which is not exactly the
message you expect to get under the current circumstances.

--
Rick

code looks ok in general, what error do you get?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

That was it; there was a typo that I didn't catch. What was confusing me was
that there is no SQL in the module and yet I was getting an SQL error
message. I'm still pretty new at using VBA and am not familiar with how one
error message might mask the actual error. Thanks very much for your
assistance.

--
Rick

Well, I would check that SQL and table names are correct typed...

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

A previous message was:

[Microsoft][ODBC Microsoft Access Driver] Unzulässige SQL-Anweisung;
'DELETE', 'INSERT', 'SELECT' oder 'UPDATE' erwartet.

which basically says that there was an illegal SQL command and that it was
anticipating a DELETE, INSERT; SELECT OR UPDATE which is not exactly the
message you expect to get under the current circumstances.

--
Rick

code looks ok in general, what error do you get?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


How do I open more than one table at a time in the same database using
ODBC
and ADO? The code below results in an error when attempting to open the
second table.


Sub OpenMultTables()
'
' Create the Recordset attributes
'
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rcd_cnt1 As Integer
Dim rcd_cnt2 As Integer
'
' Initialize the Recordset attributes
'
rcd_cnt1 = 1
rcd_cnt2 = 1
'
' Create the Recordset objects
'
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
'
' Open the recordset objects
'
With rs1
.Source = "Table1"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

With rs2
.Source = "Table2"
.ActiveConnection = "DatabaseName"
.LockType = adLockOptimistic
.Open
End With

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub


Thanks for your assistance.