Access Basic/Access 97

J

John Wallace

I have an Access 97 database containing tables and
queries. In a module I can open a recordset and read in
Table data thus:

Set rst=CurrentDb.OpenRecordset("Table_Name")

but when I replace Table_Name with Query_Name it will not
work. How do I do this?
 
J

John Vinson

I have an Access 97 database containing tables and
queries. In a module I can open a recordset and read in
Table data thus:

Set rst=CurrentDb.OpenRecordset("Table_Name")

but when I replace Table_Name with Query_Name it will not
work. How do I do this?
Click to expand...

You need to open the Querydef object:

Dim qd As DAO.Querydef
Dim rst As DAO.Recordset
Set qd = CurrentDb.Querydefs("Query_Name")
Set rst = qd.OpenRecordset

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
D

Dirk Goldgar

John Vinson said:
You need to open the Querydef object:

Dim qd As DAO.Querydef
Dim rst As DAO.Recordset
Set qd = CurrentDb.Querydefs("Query_Name")
Set rst = qd.OpenRecordset
Click to expand...

I don't follow you, John. It's certainly valid to open a recordset on a
stored query without using a QueryDef object, like this:

Set rst=CurrentDb.OpenRecordset("Query_Name")

.... so long as the query doesn't have any parameters.

So the question is, what does "it will not work" mean? Maybe this is a
query that contains references to form controls, so that John Wallace
gets the "Too few parameters" message. If that's the case, then a
QueryDef object *is* required, but it's also necessary to resolve the
parameters before opening the recordset:

Dim qd As DAO.Querydef
Dim rst As DAO.Recordset
Dim prm As DAO.Parameter

Set qd = CurrentDb.Querydefs("Query_Name")

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

Set rst = qd.OpenRecordset
 
G

George Nicholson

What kind of query is it? Does it return records? You should be able to
use the command as you state if the query is a Select, Union or MakeTable
query, but not if it is a Delete, Append or Update (i.e., Action queries).
 
T

Tony Toews

Roger Carlson said:
It is preferable for a number of reasons to create a database variable
instead of using CurrentDb directly.
Click to expand...

Why? There are a few reasons but IIRC they seem fairly esoteric to
me. I've been happily using CurrentDB for years.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
T

Tony Toews

John Wallace said:
I have an Access 97 database containing tables and
queries. In a module I can open a recordset and read in
Table data thus:

Set rst=CurrentDb.OpenRecordset("Table_Name")

but when I replace Table_Name with Query_Name it will not
work. How do I do this?
Click to expand...

What message are you getting?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Top