Running PreSaved Queries in a Module 1

[Fubear]

I am writing a fairly complex database that has to perform many queries in an operation, and the design of these queries may change slightly each time the operation is run (monthly), these queries are mainly append and delete queries.

I have a number of access queries saved, and I want to call them from a Module sub. the DoCmd.runSQL only lets me enter straight SQL, which is fine, but i dont want to trawl through pages of SQL code to makea few small changes to about a dozen query calls.

If I saved a query as "Query1" how would I call this from within a VB Module. Ideally I want to avoid using recordsets as im my experiance they are VERY slow in comparison to access queries, especially when i am going to be processing over 36,000 records each time.

Also can I call a parameter query, for example I could have a "FOR count = 1 to 5" loop, run the query each time with count being entered into the query as a parameter.

---
Bottom line, I want to click a button on a form to have access run off and perform these queries in sequence, including a couple of parameter queries based on a for loop, without having to hard code the SQL in VB.

 

[oharab]

Try this:

Dim db as DAO.database
Dim qdf as DAO.querydef
Fim x as integer

set db=currentdb

db.execute "query1"
db.execute "query2"
db.execute "query3"

for x=1 to 5
set qdf=db.querydefs("query4&quot
qdf.parameters(0).value=x
qdf.execute
next x

db.execute "query5"
.
.
.

HTH

Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[Fubear]

thanks a lot - my help file is missing the entries for the DAO objects, so i would never had found those commands.

 

[Fubear]

On the same topic... is it possible to pass a field name in as a parameter?

EG in my code i could do...
for x=1 to 5
set qdf=db.querydefs("query4&quot
if x=1 then qdf.parameters(0).value="[Field1]"
if x=2 then qdf.parameters(0).value="[Field2]"
if x=3 then qdf.parameters(0).value="[Field3]"
qdf.execute
next x

So a differant field is checked in my query for each loop (that is the purpose of the loop in the first place).

 

[oharab]

'Fraid things aren't as simple as that!
You will need to create the SQL string dynamically:

Dim strSQL as string, strFieldName as string

for x=1 to 5
select case x
case 1
strFieldname="Arrests"
case 2
strFieldname="Robberies"
case 3
strFieldname="Burglaries"
case 4
strFieldname="Incidents"
case 5
strFieldname="Murders"
end select 'a select statement is much quicker than lots of if statements

strsql="SELECT ID, theDate, " & strFilename & " FROM tblCrime;"
db.execute strsql

next x

Hope that gets you going a bit further!

B ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[Fubear]

Thats what i currently have - it will have to do.

Thanks again foryour help