Linked tables issue

  • Thread starter Thread starter Rita
  • Start date Start date
R

Rita

I have a recordset:

set rst = dbs.OpenRecordset(sql)

sql = "select key from query1"

where query1 is a query based on tables in the database.

Now I exported all tables to a back-end - and then
suddenly the OpenRecordset(sql) fails with error code
3061 - as described in my post of earlier today.

If I select from one of the linked tables directly, it
works - but selecting from a query based on linked tables
makes it fail. The reason for the intermediate query is to
force some joins to happen the right way.

Any solution to this? (besides creating a local table with
the data of query1)
 
Hi Rita

It is impossible to tell you exactly what the problem is unless you post the
SQL of Query1. However, here is a way to find out what it is in the SQL
that is unresolved and therefore seen as a "parameter":

Create new, empty query and type the following into the SQL view window:
Select key from Query1

Then run the query. It should prompt for a parameter. This is the
unresolved item.
 
Hi Graham,

That I already did. The exact same SQL statement works
well when used in the SQL window.

After further investigation the reason is:

In order to limit data I have a where-clause in Query1:
WHERE (((vdoc.vdocID)=[Forms]![ScanVdoc]![vdoc]));

It turns out that taking this value from an open form does
not work with the OpenRecordset method. Now I modify the
Query1-sql through code to be e.g.
WHERE (((vdoc.vdocID)=48));
And that works well with the OpenRecordset method.

The linked tables have nothing to do with this - I just
made too many changes and lost track of what I did...

Rita
-----Original Message-----
Hi Rita

It is impossible to tell you exactly what the problem is unless you post the
SQL of Query1. However, here is a way to find out what it is in the SQL
that is unresolved and therefore seen as a "parameter":

Create new, empty query and type the following into the SQL view window:
Select key from Query1

Then run the query. It should prompt for a parameter. This is the
unresolved item.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



I have a recordset:

set rst = dbs.OpenRecordset(sql)

sql = "select key from query1"

where query1 is a query based on tables in the database.

Now I exported all tables to a back-end - and then
suddenly the OpenRecordset(sql) fails with error code
3061 - as described in my post of earlier today.

If I select from one of the linked tables directly, it
works - but selecting from a query based on linked tables
makes it fail. The reason for the intermediate query is to
force some joins to happen the right way.

Any solution to this? (besides creating a local table with
the data of query1)
Click to expand...


.
Click to expand...
 
Hi Rita

Ah... the reason is that when you open Query1 from the database window,
Access first tries to resolve any parameters and then prompts for any
unresolved ones, *before* passing the SQL to Jet. When you open the same
query as a recordset from VBA, the SQL is passed to Jet directly. The trick
is that *you* must do the work that Access does for you from the DB window.

Where the parameters are entities that can be resolved by Access without
prompting, such as controls on an open form, You can use the Eval function,
in which VBA asks Access to evaluate a string expression.

The following general-purpose code will open a query as a recordset where
the query has resolvable parameters:

Dim db as Database
Dim qdf as QueryDef
Dim prm as DAO.Parameter
Dim rs as DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Rita said:
Hi Graham,

That I already did. The exact same SQL statement works
well when used in the SQL window.

After further investigation the reason is:

In order to limit data I have a where-clause in Query1:
WHERE (((vdoc.vdocID)=[Forms]![ScanVdoc]![vdoc]));

It turns out that taking this value from an open form does
not work with the OpenRecordset method. Now I modify the
Query1-sql through code to be e.g.
WHERE (((vdoc.vdocID)=48));
And that works well with the OpenRecordset method.

The linked tables have nothing to do with this - I just
made too many changes and lost track of what I did...

Rita
-----Original Message-----
Hi Rita

It is impossible to tell you exactly what the problem is unless you post the
SQL of Query1. However, here is a way to find out what it is in the SQL
that is unresolved and therefore seen as a "parameter":

Create new, empty query and type the following into the SQL view window:
Select key from Query1

Then run the query. It should prompt for a parameter. This is the
unresolved item.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand



I have a recordset:

set rst = dbs.OpenRecordset(sql)

sql = "select key from query1"

where query1 is a query based on tables in the database.

Now I exported all tables to a back-end - and then
suddenly the OpenRecordset(sql) fails with error code
3061 - as described in my post of earlier today.

If I select from one of the linked tables directly, it
works - but selecting from a query based on linked tables
makes it fail. The reason for the intermediate query is to
force some joins to happen the right way.

Any solution to this? (besides creating a local table with
the data of query1)
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top