OpenRecordset on Linked Tables (1 Viewer)

[petehilljnr]

If anyone can explain:

In A2k, when I try to use a recordset on a linked table (or a query based on a linked table), it spits out a "Run-time error '3061': Too few parameters. Expected 2" error.

If I import that table fully (instead of linking), it works fine.

I'm trying to create the recordset using the following snippets of code:

Dim rst As DAO.Recordset
Set rst = CurrentDB.OpenRecordset("SELECT * FROM tblTest",dbOpenDynaset)

Does anyone know why it doesn't like linked tables? Am I calling it the wrong way (as in, is the syntax different with a linked table)?

Regards,
Pete.

 

[modest]

Go open the table manually. If a login name and password is not required then you should not be receiving any issues.

Try Compacting and Repairing the database and Compiling your code. Also, make sure your spelling is correct. Additionally, I've ran into occassion where the database needed to define a variable for CurrentDB() - for instance, using CurrentDB as you have done above, while it works 99% of the time, I've had issues where it wouldn't do something (can't remember what).

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTest", dbOpenDynaset)

Notice how when you're doing a "SELECT *" query with no WHERE clause, all you need to put is the table or query name. If this does not work, you may need to decompile and then recompile your database. If that does not work, post the two databases online (trimmed down of course) with only the tables involved in test data.

 

[petehilljnr]

Hey cheers for replying.

I think I have narrowed it down a bit further.

In the original query I had, it was referring to a value in a form as part of a filter.

e.g. in the criteria field in the QBE grid on a field, I had:

=[forms]![MyForm]![MyValue]

If you open this query (with a value in MyValue on MyForm) it works fine.
If you try to create a recordset on this query, it fails.
If I take out the "=[forms]![MyForm]![MyValue]" and explicitly define it by typing a number in the criteria, it works fine.

If you import the tables that my query was based on (as opposed to linking):
It all works fine.

I tried everything you noted in your previous post but still the same result.
I also recreated this on a fresh database (FE/BE).

Before posting a sample, any thoughts on why? I've worked around it, but just interested to see why it isn't working as I would expect it to.

Regards,
Pete.

 

[RuralGuy]

If your query has a criteria that points to a form, then whether the table is linked or local, you should get an error if the form is not open.

 

[petehilljnr]

Here's a sample:

FE.mdb linked to BE.mdb on C:\

Access 2000

Pete.

 

[willpower]

I don't think the fact the table is linked is causing the problem. The error suggests that TWO parameter values are missing. Is tblTest a table or a query? If it's a query, is there another parameter somewhere in the criteria?

willpower

 

[petehilljnr]

The only parameter is from the form, which is open with a valid integer value filled in.

It's almost like it ignores the parameter if it has to go looking for it when a linked table is involved.

 

[pbaldy]

Nothing to do with linked tables, it's the parameter in the query:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203