QueryDef Collection

[Alain]

Hi to all,

I would like to learn and understand the QueryDef object and collection.
I have been asked to export to Excel reports that are being custom build by
the end user. Access 2000 books I have and help file does not help me much.
The few line I read tells me that I can make modification to an already
existing query in the db but that is it.

I would like to know if there is good references or articles out there so i
can get started with this sincew I not sure on how to declare this and use
it properly in code

TIA

Alain

 

[Guest]

The QueryDefs collection contains all all QueryDef objects for a database,
so, CurrentDb.Querydefs(0).Name will return the name of the first query.
CurrentDb.Querydefs.Count will return the total number of querys in the
database.
CurrentDb.QueryDefs(0).SQL will return the SQL string for the first query in
the database.

So, for example, let's say that all the queries you want to export to Excel
have a name that starts with "qselXl". (You will need some way to identify
which queries to export). We can loop through the QueryDefs collection, find
the queries we want to report, and export them to Excel.

Dim dbf as Database
Dim qdfs as QueryDefs
Dim qdf as QueryDef

Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs
For Each qdf in qdfs
If Left(qdf.Name, 5) = "qselXl" Then
DoCmdTransferSpreadsheet acExport, , qdf.Name, "C:\SomeFolder\"
& _
qdf.Name, True
End If
Next qdf
Set qdf = Nothing
Set qdfs = Nothing
Set dbf = Nothing

Now, lets say we need to make changes to the SQL for the query based on
criteria selected by the user.
(using the same Dims as above)
strSQL = qdf.SQL
'Modify the query's SQL here
qdf.SQL = strSQL

Hope this helps. Post back if you have more questions.

 

[Alain]

Thanks,

I will study and run some test on what you write, if more questions I will
post back

Alain