Why runtime error with this line... Set MyTable = MyDB.OpenRecordset("tblOrders", DB_OPEN_TABLE)

[MLH]

I copied the following code right out of MS Access 2.0
HELP under Index Property Setting example...

Sub Button0_Click ()
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("tblOrders", DB_OPEN_TABLE)
MyTable.Index = "PrimaryKey"
MyTable.Seek "=", 10050
MyTable.Close

End Sub

When I run it, I get an Invalid Operation error on the
Set MyTable line. If tblOrders is a valid attached table,
why does running that line produce an error? I do not
get the same error if substituting a local table (one that
is not attached). BTW, the attachment is from a back
end table to a front end table - both residing on the
same stand-alone workstation.

[Allen Browne]

Firstly, make sure you have a reference to the DAO library.
From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library

Secondly, the constants have changed name. Try dbOpenTable instead of
DB_OPEN_TABLE.

Most importantly, you cannot open a linked table as dbOpenTable. You need to
use dbOpenDynaset. When you do that, the next line will fail, because you
cannot set the Index property of a dynaset type recordset. The code
therefore needs to be rewritten so that it opens a SQL statement, ordered by
the desired field, or limited the desired record. This kind of thing:

strSQL = "SELECT OrderID FROM tblOrders WHERE OrderID = 10050;"
Set MyTable = MyDB.OpenRecordset(strSql)
If not MyTable.EOF Then
MsgBox "Order 10050 was found."
End If
MyTable.Close

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.net> wrote in message
news:f9********************************@4ax.com...

I copied the following code right out of MS Access 2.0
HELP under Index Property Setting example...

Sub Button0_Click ()
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("tblOrders", DB_OPEN_TABLE)
MyTable.Index = "PrimaryKey"
MyTable.Seek "=", 10050
MyTable.Close

End Sub

When I run it, I get an Invalid Operation error on the
Set MyTable line. If tblOrders is a valid attached table,
why does running that line produce an error? I do not
get the same error if substituting a local table (one that
is not attached). BTW, the attachment is from a back
end table to a front end table - both residing on the
same stand-alone workstation.

[MLH]

Thanks for the heads up on the situation, Allen. Good advice.
xxxxxxxxxxxxxxxxxxxxxx
On Mon, 18 Oct 2004 22:44:51 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Firstly, make sure you have a reference to the DAO library.
From the code window, choose References on the Tools menu.
Check the box beside:
Microsoft DAO 3.6 Library

Secondly, the constants have changed name. Try dbOpenTable instead of
DB_OPEN_TABLE.

Most importantly, you cannot open a linked table as dbOpenTable. You need to
use dbOpenDynaset. When you do that, the next line will fail, because you
cannot set the Index property of a dynaset type recordset. The code
therefore needs to be rewritten so that it opens a SQL statement, ordered by
the desired field, or limited the desired record. This kind of thing:

strSQL = "SELECT OrderID FROM tblOrders WHERE OrderID = 10050;"
Set MyTable = MyDB.OpenRecordset(strSql)
If not MyTable.EOF Then
MsgBox "Order 10050 was found."
End If
MyTable.Close