Creating a query in code

[paulquinlan100]

Hi

Im trying to programmatically create a query so that it can be exported
to excel using docmd.outputto. I can do this easily enough with a saved
query, but as the user has a variety of options so i dont want to have
to have to set up all the possible queries beforehand.

Should i be using QueryDef for this?? Ive tried and it appears as
though the code doesnt even run, it wont even let me break into the
procedure and any msgbox's put in dont appear.

Any ideas?
Thanks a lot for any help.
Paul

[Allen Browne]

How about creating a query for this task, and then assigning its SQL
property for export?

Example: say you create a query named qry4Export.
Then your code will do this:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).qry4Export.SQL = strSql
DoCmd.OutputTo ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa************@hotmail.comwrote in message
news:11**********************@b68g2000cwa.googlegr oups.com...

Hi

Im trying to programmatically create a query so that it can be exported
to excel using docmd.outputto. I can do this easily enough with a saved
query, but as the user has a variety of options so i dont want to have
to have to set up all the possible queries beforehand.

Should i be using QueryDef for this?? Ive tried and it appears as
though the code doesnt even run, it wont even let me break into the
procedure and any msgbox's put in dont appear.

Any ideas?
Thanks a lot for any help.
Paul

[paulquinlan100]

Im still having problems...

For some reason the procedure doesnt even seem to run when i use
dbEngine

I've also tried using: Dim db As DAO.Database and the procedure wont
run, however if i rem out this line the procedure runs (but obviously
still doesnt do what i want it to!).

Ive tested it by putting a message box as the first line of the
procedure, and the box will not appear if either of the above
statements are included anywhere in the procedure.

Thanks again for any ideas
Paul

Allen Browne wrote:

How about creating a query for this task, and then assigning its SQL
property for export?

Example: say you create a query named qry4Export.
Then your code will do this:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).qry4Export.SQL = strSql
DoCmd.OutputTo ...
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa************@hotmail.comwrote in message
news:11**********************@b68g2000cwa.googlegr oups.com...

Hi

Im trying to programmatically create a query so that it can be exported
to excel using docmd.outputto. I can do this easily enough with a saved
query, but as the user has a variety of options so i dont want to have
to have to set up all the possible queries beforehand.

Should i be using QueryDef for this?? Ive tried and it appears as
though the code doesnt even run, it wont even let me break into the
procedure and any msgbox's put in dont appear.

Any ideas?
Thanks a lot for any help.
Paul

[Allen Browne]

If Access complains about an unknown data type on this line:
Dim db As DAO.Database
you need to set your references.

Choose References on the Tools menu (in the code window), and check the box
beside:
Microsoft DAO 3.6.

More info about references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<pa************@hotmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Im still having problems...

For some reason the procedure doesnt even seem to run when i use
dbEngine

I've also tried using: Dim db As DAO.Database and the procedure wont
run, however if i rem out this line the procedure runs (but obviously
still doesnt do what i want it to!).

Ive tested it by putting a message box as the first line of the
procedure, and the box will not appear if either of the above
statements are included anywhere in the procedure.

Thanks again for any ideas
Paul

Allen Browne wrote:

>How about creating a query for this task, and then assigning its SQL
property for export?

Example: say you create a query named qry4Export.
Then your code will do this:
Dim strSql As String
strSql = "SELECT ...
dbEngine(0)(0).qry4Export.SQL = strSql
DoCmd.OutputTo ...

<pa************@hotmail.comwrote in message
news:11**********************@b68g2000cwa.googleg roups.com...

>
Im trying to programmatically create a query so that it can be exported
to excel using docmd.outputto. I can do this easily enough with a saved
query, but as the user has a variety of options so i dont want to have
to have to set up all the possible queries beforehand.

Should i be using QueryDef for this?? Ive tried and it appears as
though the code doesnt even run, it wont even let me break into the
procedure and any msgbox's put in dont appear.

Any ideas?
Thanks a lot for any help.
Paul