Re: Connecting to Query
From: Bill (Bill_at_discussions.microsoft.com)
Date: 11/03/04
- Next message: PJ: "Scroll bar colour"
- Previous message: Brad Pears: "SQL insert question..."
- Next in thread: Dirk Goldgar: "Re: Connecting to Query"
- Reply: Dirk Goldgar: "Re: Connecting to Query"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 3 Nov 2004 06:40:02 -0800
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
"Dirk Goldgar" wrote:
> "Bill" <Bill@discussions.microsoft.com> wrote in message
> news:20D0BB91-0573-4C7C-B489-4D8AFF275755@microsoft.com
> > 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
>
> 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 Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>
- Next message: PJ: "Scroll bar colour"
- Previous message: Brad Pears: "SQL insert question..."
- Next in thread: Dirk Goldgar: "Re: Connecting to Query"
- Reply: Dirk Goldgar: "Re: Connecting to Query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
