Run-time error '3219': Invalid operation

TerenceHewett
TerenceHewett used Ask the Experts™
on
Hi experts,

I have the following code that puts an ID into a field in the table tblPSX.  

Dim db As Database
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblPSX", dbOpenTable)

    rs.MoveFirst
    'Updating the records
    Do Until rs.EOF = True
    With rs
    .Edit
    PS=!PSID
   .Update
   End With
   rs.MoveNext
   Loop
     
    Set rs = Nothing
    Set db = Nothing


The code works fine if tblPSX is not a linked table, but if the table is linked I get the following message:

Run-time error '3219': Invalid operation

When I debug, the highlighted text is "Set rs = CurrentDb.OpenRecordset("tblPSX", dbOpenTable)".

Can anyone help?

Thank you.
Terry


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
try using

Set rs = CurrentDb.OpenRecordset("tblPSX",dbopendynaset)

or

Set rs = CurrentDb.OpenRecordset("tblPSX",dbopendynaset,dbseechanges)
When it is a linked table it is not in the current Db. The easiest way around this is open a query to the table instead:
Set rs = CurrentDb.OpenRecordset("Select * From tblPSX")

BTW: You are not using "db" so you don't need:
Dim db As Database
    Set db = Nothing
Researching further, you should be able to open a recordset on a linked table. Can you open the tblPSX from the front end?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2016

Commented:
me thinks, linked table is from SQL or other db that uses ODBC

Author

Commented:
As always, a first class response and I am very grateful.

Author

Commented:
Thanks for all posts - Cap, the following worked a treat:

Set rs = CurrentDb.OpenRecordset("tblPSX",dbopendynaset)


Regards.
Terry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial