MSAccess XP and ADO. Newbe to ADO.

[Philip]

Hello all.
Having problems with ADO in MSAccess XP. New to it. Have created a Access
DB, Not split (code and BD in same file). Trying to access one of the
tables in the DB with the following commands. Can you tell me where I'm
going wrong? It compiles OK, but errors on the Open line (last line shown)
Error is...
Quote
Run-time error 3709
The connection cannot be used to perfrom this operation. It is either
closed of invalid in this context.
Unquote

The relevent section of code is...
Dim MyRS As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Pref_Cust_ID, Order_No FROM Order WHERE
(Order.Pref_Cust_ID = '" & Me!Pref_Cust_ID & "') ORDER BY Order.Order_No
DESC;"
MyRS.Open strSQL , , adOpenForwardOnly, adLockReadOnly, adCmdText

Have tryed veriety of options in this line, but it always errors. SQL
statement is not problem as I have also tryed simplifying it. No diference.

I have the following references.
VB for Apps
MS Access 11.0 Object Library
OLE Automation
MS ADO 2.7 Library
MS ADO Recordset 2.7 Library
MS ADO Ext. 2.7 for DDL and Security
MS Jet and Replication Objects 2.6 Library

Can anyone see what I'm doing wrong?

Thanks.

 

[Randy Harris]

Hello all.
Having problems with ADO in MSAccess XP. New to it. Have created a Access
DB, Not split (code and BD in same file). Trying to access one of the
tables in the DB with the following commands. Can you tell me where I'm
going wrong? It compiles OK, but errors on the Open line (last line shown)
Error is...
Quote
Run-time error 3709
The connection cannot be used to perfrom this operation. It is either
closed of invalid in this context.
Unquote

It looks as though Access doesn't like the connection. Perhaps

MyRS.Open strSQL , CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

I find that this syntax works for me.

Randy

 

[Van T. Dinh]

You have not specified the Connection to be used for the Recordset as per
Randy's advice.

In addition, "Order" is a *Reserved Word* in JET SQL and I think you will
ave problems with this.

Try enclosing any reference to the Table Order in square brackets like:

[Order]

 

[Philip]

Thanks to both of you.
Both needed to be done. resulting working code looks like this...

Dim MyRS As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Pref_Cust_ID, Order_No FROM [Order] WHERE
([Order].Pref_Cust_ID = '" & Me!Pref_Cust_ID & "') ORDER BY [Order].Order_No
DESC;"
MyRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
If MyRS.EOF Then
Me!Order_No = Me!Pref_Cust_ID & "0001"
Else
Me!Order_No = Me!Pref_Cust_ID & Str(Val(Right(MyRS!Order_No, 4) + 1))
End If
MyRS.Close: Set MyRS = Nothing

Thanks heaps.
Philip Middleton.

You have not specified the Connection to be used for the Recordset as per
Randy's advice.

In addition, "Order" is a *Reserved Word* in JET SQL and I think you will
ave problems with this.

Try enclosing any reference to the Table Order in square brackets like:

[Order]

--
HTH
Van T. Dinh
MVP (Access)

Hello all.
Having problems with ADO in MSAccess XP. New to it. Have created a Access
DB, Not split (code and BD in same file). Trying to access one of the
tables in the DB with the following commands. Can you tell me where I'm
going wrong? It compiles OK, but errors on the Open line (last line shown)
Error is...
Quote
Run-time error 3709
The connection cannot be used to perfrom this operation. It is either
closed of invalid in this context.
Unquote

The relevent section of code is...
Dim MyRS As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT Pref_Cust_ID, Order_No FROM Order WHERE
(Order.Pref_Cust_ID = '" & Me!Pref_Cust_ID & "') ORDER BY Order.Order_No
DESC;"
MyRS.Open strSQL , , adOpenForwardOnly, adLockReadOnly, adCmdText

Have tryed veriety of options in this line, but it always errors. SQL
statement is not problem as I have also tryed simplifying it. No diference.

I have the following references.
VB for Apps
MS Access 11.0 Object Library
OLE Automation
MS ADO 2.7 Library
MS ADO Recordset 2.7 Library
MS ADO Ext. 2.7 for DDL and Security
MS Jet and Replication Objects 2.6 Library

Can anyone see what I'm doing wrong?

Thanks.