db.OpenRecordset with linked tables

I am running code that updates tables using the following:

Set rs = db.OpenRecordset("tblVendorNames1", DB_OPEN_TABLE)

I have what to me is an interesting problem.  When I try to use this code with linked tables I get run time error 3219 Invalid Operation.  When ever I try to use this with tables located in the front end it works fine.  I would like to keep all my tables located in the back end.  How can I get this to work with linked tables?
HockeyAsked:
Who is Participating?
 
ildcCommented:
Hi hockey,

I donnut know which version of access your using, but if it's 97 or later, it never hearts to declare your variables as :

dim db as dao.database
dim rs as dao.recordset

regards
0
 
ildcCommented:
Set rs = db.OpenRecordset("tblVendorNames1", DB_OPEN_TABLE)
should be:
Set rs = db.OpenRecordset("tblVendorNames1", DBOPENTABLE)

But I advice your to use dbopendynaset if you want to change data, and dbopenforwardonly if you want to loop through data, and dbsnapshot if you want to see data

Regards
0
 
HockeyAuthor Commented:
That unfortunately, still did not do the trick.  Even using dynaset.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kenspencerCommented:
Hi,

So,
        Set rs = db.OpenRecordSet("tblVendorNames1", dbOpenDynaset)

only works with your local tables and not your linked ones?

Ken
0
 
Daniel StanleyDatabase engineerCommented:
i ran into a similar problem, i couldn't find a way around it; i was only looking for one value so i used DLookup instead.

good luck,
daniels@asix.com
0
 
peroveCommented:
this is correct..that you get the error I'mean.

The db is set to the currentdb and not the MDB where the table resides.

Two way I can think of to fix it.

One use the correct setting when you are setting the db variable:

method 1
Function opendb()
Dim rs As Recordset
Dim db As Database
Set db =     Set Db = DBEngine.Workspaces(0).OpenDatabase("c:\linkedtablemdb.mdb")
Set rs = db.OpenRecordset("linkedtablemdb", dbOpenTable)
End Function

method 2
Make a query that select everything from the tblVendorNames1, save this query as tblVendorNames1Q

Then you can directly do a:
Set rs = db.OpenRecordset("tblVendorNames1Q", DB_OPEN_TABLE)


perove







0
 
peroveCommented:
Sorry... method 1 should be:

Function opendb()
Dim rs As Recordset
Dim db As Database
Set Db = DBEngine.Workspaces(0).OpenDatabase("c:\linkedtablemdb.mdb")
Set rs = db.OpenRecordset("linkedtablemdb", dbOpenTable)
End Function


slip of the finger

perove
0
 
HockeyAuthor Commented:
Ok guys thanks I will start to trying your suggestions.  

0
 
HockeyAuthor Commented:
Metod 1 worked thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.