Connecting to Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use a series of combo boxes to create a query, but when I try to connect to
the query to extract data, I cannot.
I open the query (tstqryTest) from the Main Access Window after selecting an
item in the final combo box (cmb3), I see the data displayed exactly as it
should.
At the cmb3_AfterUpdate event, I try to open the query (tstqryTest) as a
recordset with the line:
rstTest.Open "tstqryTest", cn, adOpenKeyset, adLockOptimistic
An error message returns:
Unexpected error: -2147217900, Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
This approach works fine when I substitute a table for the query. What am I
doing wrong? How can I extract data from the query (tstqryTest)?

Thanks. Bill Schaupp
 
Bill said:
I use a series of combo boxes to create a query, but when I try to
connect to the query to extract data, I cannot.
I open the query (tstqryTest) from the Main Access Window after
selecting an item in the final combo box (cmb3), I see the data
displayed exactly as it should.
At the cmb3_AfterUpdate event, I try to open the query (tstqryTest)
as a recordset with the line:
rstTest.Open "tstqryTest", cn, adOpenKeyset, adLockOptimistic
An error message returns:
Unexpected error: -2147217900, Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
This approach works fine when I substitute a table for the query.
What am I doing wrong? How can I extract data from the query
(tstqryTest)?

Thanks. Bill Schaupp
Click to expand...

You say that you "use a series of combo boxes to create a query". I'm
not sure what you mean by that, exactly, but Is it possible that, at the
moment when you try to open your recordset on it, the query has not yet
been created? You'll get that message if you give a name for the Source
argument that isn't recognized as the name of a table, query, sproc, or
SQL statement.
 
Dirk, Thanks for the reply.

As for the combo boxes, I first use a combo box to select a territory from
lookup table of territories. Second, with the selected territory as a filter,
I use a combo box to select a customer from a query based primarily on a link
table of territories and customers that shows only customers in the selected
territory. Third, with the selected customer within the territory as a
further filter, I use a combo box to select a product from a query based
primarily on a link table of territories, customers and products that shows
only products for the selected customer in the selected territory.

Next, I use a querry (tstqryTest) based on a link table of territories,
customers, products and product details to show the details of the selected
product. I can click open the query after making my selection in the third
combo box, and see the recordset as I expect.

I believe the query has been created because it opens at the AfterUpdate
event with the command:
DoCmd.OpenQuery "tstqryTest", acViewNormal, acReadOnly

After I run the command
rstTest.Open "tstqryTest", cn, adOpenKeyset, adLockOptimistic
and get the error message, I can still open the "tstqryTest" query from the
main Access window showing the properly selected data.

I'm not sure what you mean by source argument. Is that the "tstqryTest"
item in the rstTest.Open... command above or something else? "tstqryTest"
appears in the Query list of the main Access window, and opens when I click
it.

Thanks again for your help.

Bill Schaupp
 
Bill said:
Dirk, Thanks for the reply.

As for the combo boxes, I first use a combo box to select a territory
from lookup table of territories. Second, with the selected territory
as a filter, I use a combo box to select a customer from a query
based primarily on a link table of territories and customers that
shows only customers in the selected territory. Third, with the
selected customer within the territory as a further filter, I use a
combo box to select a product from a query based primarily on a link
table of territories, customers and products that shows only products
for the selected customer in the selected territory.

Next, I use a querry (tstqryTest) based on a link table of
territories, customers, products and product details to show the
details of the selected product. I can click open the query after
making my selection in the third combo box, and see the recordset as
I expect.

I believe the query has been created because it opens at the
AfterUpdate event with the command:
DoCmd.OpenQuery "tstqryTest", acViewNormal, acReadOnly

After I run the command
rstTest.Open "tstqryTest", cn, adOpenKeyset, adLockOptimistic
and get the error message, I can still open the "tstqryTest" query
from the main Access window showing the properly selected data.

I'm not sure what you mean by source argument. Is that the
"tstqryTest" item in the rstTest.Open... command above or something
else? "tstqryTest" appears in the Query list of the main Access
window, and opens when I click it.

Thanks again for your help.

Bill Schaupp
Click to expand...

I think I understand what's going on, Bill. You aren't actually
creating the query on the fly, are you? You have previously created a
query that uses references to the combo boxes on the form as criteria;
isn't that right? That means that, as far as ADO is concerned, those
references are parameters, and you have to supply values for those
parameters before you can open a recordset on them. When you open a
query via the Access user interface or DoCmd.OpenQuery, Access fills in
those parameters for you, but when you open a recordset on the query,
ADO has no idea what those are and concludes that your query is not
valid SQL.

I'm more familiar with the DAO approach to this problem than I am with
ADO, but I believe you'll have to use a Command object to resolve the
parameters, and then open a recordset from the Command object, like
this:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rstTest As ADODB.Recordset

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = cn
cmd.CommandText = "tstqryTest"
cmd.CommandType = adCmdTable
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstTest = cmd.Execute

With rstTest

' ... do stuff with rstTest ...

.Close

End With

Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

Note, by the way, that there is no need to use DoCmd.OpenQuery to open
your query before opening your recordset on the query. I mention this
because it wasn't clear to me from what you posted whether you thought
you had to do that or not.
 
(sending again, as first post hasn't appeared)

Bill said:
Dirk, Thanks for the reply.

As for the combo boxes, I first use a combo box to select a territory
from lookup table of territories. Second, with the selected territory
as a filter, I use a combo box to select a customer from a query
based primarily on a link table of territories and customers that
shows only customers in the selected territory. Third, with the
selected customer within the territory as a further filter, I use a
combo box to select a product from a query based primarily on a link
table of territories, customers and products that shows only products
for the selected customer in the selected territory.

Next, I use a querry (tstqryTest) based on a link table of
territories, customers, products and product details to show the
details of the selected product. I can click open the query after
making my selection in the third combo box, and see the recordset as
I expect.

I believe the query has been created because it opens at the
AfterUpdate event with the command:
DoCmd.OpenQuery "tstqryTest", acViewNormal, acReadOnly

After I run the command
rstTest.Open "tstqryTest", cn, adOpenKeyset, adLockOptimistic
and get the error message, I can still open the "tstqryTest" query
from the main Access window showing the properly selected data.

I'm not sure what you mean by source argument. Is that the
"tstqryTest" item in the rstTest.Open... command above or something
else? "tstqryTest" appears in the Query list of the main Access
window, and opens when I click it.

Thanks again for your help.

Bill Schaupp
Click to expand...

I think I understand what's going on, Bill. You aren't actually
creating the query on the fly, are you? You have previously created a
query that uses references to the combo boxes on the form as criteria;
isn't that right? That means that, as far as ADO is concerned, those
references are parameters, and you have to supply values for those
parameters before you can open a recordset on them. When you open a
query via the Access user interface or DoCmd.OpenQuery, Access fills in
those parameters for you, but when you open a recordset on the query,
ADO has no idea what those are and concludes that your query is not
valid SQL.

I'm more familiar with the DAO approach to this problem than I am with
ADO, but I believe you'll have to use a Command object to resolve the
parameters, and then open a recordset from the Command object, like
this:

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rstTest As ADODB.Recordset

Set cn = CurrentProject.Connection
Set cmd = New ADODB.Command

Set cmd.ActiveConnection = cn
cmd.CommandText = "tstqryTest"
cmd.CommandType = adCmdTable
For Each prm In cmd.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rstTest = cmd.Execute

With rstTest

' ... do stuff with rstTest ...

.Close

End With

Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

Note, by the way, that there is no need to use DoCmd.OpenQuery to open
your query before opening your recordset on the query. I mention this
because it wasn't clear to me from what you posted whether you thought
you had to do that or not.
 
Dirk, Thanks again for the help. I had a problem logging on to this site
yesterday, but managed to get through today.

I will incorporate your recommendations, and let you know if they work. I
am new to the inner workings of Access, but I think with your help I'm
beginning to get the hang of it.

Bill Schaupp
 
Dirk, Thank you very much. It worked as it should (after a few debugs).
This brings me to a whole new level.

Thanks again.

Bill Schaupp
 
Dirk,

I wonder if you have a reference recommendation that includes some good
practice examples of the ADO Command, Parameters, etc. you explained below.
Your code works perfectly, but I still would like to understand a little more
detail on how it all fits together. I am working my way through "Microsoft
Access 2003 Inside Out", which mentions basically that there are such things
as ADO Commands, etc., but does not seem to go into detail or show any
examples.

What is the best reference/tutorial for ADO? Thanks again.

Bill Schaupp
 
Bill said:
Dirk,

I wonder if you have a reference recommendation that includes some
good practice examples of the ADO Command, Parameters, etc. you
explained below. Your code works perfectly, but I still would like to
understand a little more detail on how it all fits together. I am
working my way through "Microsoft Access 2003 Inside Out", which
mentions basically that there are such things as ADO Commands, etc.,
but does not seem to go into detail or show any examples.

What is the best reference/tutorial for ADO? Thanks again.
Click to expand...

Alas, Bill, I don't have any such reference. I mainly use DAO, since my
work is mostly in MDB files, so I have only limited experience with ADO.
I just read the help file and look at the examples that are provided.

Looking around at the books in my office now, I see that the _Access
2002 Developers Handbook_, by Litwin, Getz, and Gunderloy (Sybex), has a
discussion and sample code to evaluate the Parameters collection of a
Command object. This book is a terrific reference for any Access
developer, but it covers much, much more than ADO -- it's huge, two
volumes' worth. The Viescas book you've got is another good resource,
but is not so focused on providing developer solutions.

I don't actually know what books are good ADO references. I suggest
that you post a separate message to the
microsoft.public.access.modulesdaovba, or to .modulesdaovba.ado (which
would seem to be most appropriate, but that's a low-traffice group),
asking for advice on the matter.
 
Back
Top