Export Access RunSQL Queries to Excel

[jcbirch]

Hi,

I have an Access VB module that creates an Excel report. Part of the Access VB module calls a macro that runs a series of SQL statements based upon what the user specified. Different users have different business rules, thus there are multiple macros, and I select the appropriate one before running that particular report. What I want to do is include the SQL statements used in the macro as its own tab in the resulting report, as a record of what business rules were used. Is there a way to do this? I don't even know where to start with any code on that so can't post anything.

If you could help, I'd appreciate it!!! Thanks!

 

[sxschech]

Something like this:

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryQuery")
    Debug.Print qd.SQL

Put the name of the query or variable name for the query you want to display the sql statement for in "qryQuery". Leave off quotes if you are using a variable.

Change Debug.Pring qd.SQL to a variable or the statement you would use to pass the SQL statement to your excel file.

X=qd.sql

 

[jcbirch]

Will this work if the queries are nestled as "RunSQL" statements within different macros? They are not "queries" per se. I'm not sure how to code for a Macro definition, or Macro contents.