Unanswered: Error 3219 When Opening a Linked Table
I'm running Access2002 under WindowsXP and have developed an application with a form which displays information from a recordset whose record source is an SQL statement defining a union of two tables.
While this recordset is still open, the form allows each of the tables to be updated individually with a new record by creating a temporary table recordset and using the .AddNew method. This works fine when all the tables are held locally.
I have just split the database so that the two relevant tables are now held in a separate Access mdb file. Now, I find that when I attempt to add a new record that involves updating the linked tables, the attempt to open
the table recordset is rejected with error 3219 - Invalid Operation. Is this really an invalid operation, or is there some setting I need to change to
allow me to perform the table update?
Is the problem occurring when you try to open the recordset? Are you trying to open it as a table? If so then you will have to reference the database that the table actually resides in. Are you using DAO or ADO?
Thanks for your helpful comments. The problem is when I attempt to open a table-type recordset using DAO, targetting a linked table. I have since read through the Access Help information more closely and have discovered that table-type recordsets are restricted to local tables only. If I change the recordset to a dynaset-type, the recordset can be opened and the linked table can be updated without error. I chose a table-type recordset initially in order to take advantage of the faster processing of the indexed Seek method to locate specific rows, but hey-ho it's back to the FindFirst, FindNext methods of dynasets once more. Thanks anyway.
You can still open it as a table type recordset and take advantage of the performance of the Seek method. All you have to do is to open the database that the table resides in.
Typically you will use CurrentDB() to return a reference to the database (quick and easy way). But all you have to do is to create a database object and use the OpenDatabase method to refer to your other database. Soemthing like:
Dim dbTables as DAO.Database
Set dbTables = DBEngine.OpenDatabase("C:\DatabaseName.mdb")
Set rstTable = dbTables.OpenRecordset("tblTableName",dbOpenTable)
Make sure to cleanup the recordset and database. Close the recordset first then the database. If you close the database first then all the recordsets opened with that database reference will be closed.