Parameter Query

[Guy]

Hi,

Is there a way of using parameters to select which field to extract using
DAO?

eg:

Dim strSQl as String
Dim qryA as QueryDef

strSQL = "PARAMETERS pOne Text, pTwo Text; " _
& "SELECT

.pOne as Data FROM Table " _
& "WHERE

.[Index] = pTwo;"

Set qryA = CurrentDb.CreateQueryDef("", strSQL)
.....

qryA!pOne = "Field a"
qryA!pTwo = "Row b"

etc...

 

[Cindy M -WordMVP-]

Hi Guy,

Is there a way of using parameters to select which field to extract using
DAO?

Where should the parameter information be coming from? User input (as when
you view a query in the UI, and a little box appears where you can type
something)?

I think you'd need to do this indirectly, using an Inbox for example to get
the user input, then making this part of the SQL string.

sOne = InputBox("Enter the information about...")

sSQL = ... & "WHERE

.[Index]=" & CLng(sOne)

eg:

Dim strSQl as String
Dim qryA as QueryDef

strSQL = "PARAMETERS pOne Text, pTwo Text; " _
& "SELECT

.pOne as Data FROM Table " _
& "WHERE

.[Index] = pTwo;"

Set qryA = CurrentDb.CreateQueryDef("", strSQL)
.....

qryA!pOne = "Field a"
qryA!pTwo = "Row b"

etc...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Guy C]

Where should the parameter information be coming from? User input (as when
you view a query in the UI, and a little box appears where you can type
something)?

I think you'd need to do this indirectly, using an Inbox for example to get
the user input, then making this part of the SQL string.

sOne = InputBox("Enter the information about...")

sSQL = ... & "WHERE

.[Index]=" & CLng(sOne)

The parameter infomation is extracted from another program. I was trying to
avoid having to create a new SQL query every time I looped through (c.
100,000 times). It works, but is very slow.

 

[Cindy M -WordMVP-]

Hi Guy,

The parameter infomation is extracted from another program. I was trying to
avoid having to create a new SQL query every time I looped through (c.
100,000 times)

I'd try asking this in an Access group, and maybe also in an ADO group. I
recall (but only vaguely) that ADO might have something like this.

In any case, it might be faster to
1. NOT loop the CreateQueryDef, but just setting the SQL property, once
it's been created. A lot of your overhead is probably in the CreateQueryDef

2. or even take a really good look at ADO, because it might be faster to
load a recordset to the "client", disconnect and work with that, rather than
running DAO for this particular task

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail