Access Basic/Access 97

[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?

 

[Roger Carlson]

It is preferable for a number of reasons to create a database variable
instead of using CurrentDb directly. Therefore, try this:

Dim db = DAO.Database
Dim rst = DAO.Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Query_Name", dbOpenDynaset)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[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?

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

 

[Dirk Goldgar]

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

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

 

[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).

 

[Tony Toews]

It is preferable for a number of reasons to create a database variable
instead of using CurrentDb directly.

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

 

[Tony Toews]

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?

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

 

[John Vinson]

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:

Quite right. Brainfade...

Note to self: don't post when I'm that tired!

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