none
Excel vba to get sql statement RRS feed

  • Question

  • Hi and thanks for reading what you may think is a question that's been answered already in many places. However, I don't want to run an SQL query or return a recordset to Excel. What I'm after is how to fetch the SQL statement itself.

    I have inherited a data warehouse built predominantly on Access 2010. It contains many Access databases and each of these contains many tables and queries. To help me get a handle on the size of the task to rebuild the data warehouse using SQL Server I have built a tool in Excel to list the structure of the entire data warehouse e.g. folders and files and if a file is a database it lists the TablesSchema and ColumnsSchema. It would be the icing on the cake if I could also list the SQL Statement text of each query.

    Monday, September 1, 2014 8:03 PM

Answers

  • Hi,

    According to your description, you want to fetch all the SQL statements in a database and list them in Excel workbook.

    If you just want to get all the SQL statements, you can loop to get every QueryDef object in current database and use QueryDef.SQL Property to get the SQL statement that defines the query. QueryDef object is a stored definition of a query in a Microsoft Access database.

    For Each obj In CurrentDb.querydefs
       Debug.Print obj.Sql
    Next obj


    If you want to get all the SQL statement of the queries in the database, you can loop the AllQueries Collection to get the name of each AccessObject object and use the query name to fetch the SQL statement.

    Set db = app.CurrentDb
    Set dbs = app.CurrentData 'app means Access application
    
    For Each obj In dbs.AllQueries
       Set QD = db.QueryDefs(obj.name)
       Debug.Print QD.Sql
    Next obj

    I write a sample to open an Access database to fetch all SQL statement and then list them in the active worksheet from Excel for your reference.

    Sub GetQuerySQL()
    
    Dim app As Access.Application
    Set app = New Access.Application
    
    app.OpenCurrentDatabase "C:\Users\Database1.accdb"
    
    Set db = app.CurrentDb
    
    i = 1
    For Each obj In db.querydefs
       ActiveSheet.Range("A" & i).Value = obj.Sql
       i = i + 1
    Next obj
    
    app.CurrentDb.Close
    app.Quit
    Set app = Nothing
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by onimoD XLT Tuesday, September 2, 2014 10:39 AM
    Tuesday, September 2, 2014 7:51 AM
    Moderator

All replies

  • For some reason I think you are taking a detour exporting your access database to an sql server.

    Have you ever seen THIS?

    Tuesday, September 2, 2014 7:27 AM
  • Hi,

    According to your description, you want to fetch all the SQL statements in a database and list them in Excel workbook.

    If you just want to get all the SQL statements, you can loop to get every QueryDef object in current database and use QueryDef.SQL Property to get the SQL statement that defines the query. QueryDef object is a stored definition of a query in a Microsoft Access database.

    For Each obj In CurrentDb.querydefs
       Debug.Print obj.Sql
    Next obj


    If you want to get all the SQL statement of the queries in the database, you can loop the AllQueries Collection to get the name of each AccessObject object and use the query name to fetch the SQL statement.

    Set db = app.CurrentDb
    Set dbs = app.CurrentData 'app means Access application
    
    For Each obj In dbs.AllQueries
       Set QD = db.QueryDefs(obj.name)
       Debug.Print QD.Sql
    Next obj

    I write a sample to open an Access database to fetch all SQL statement and then list them in the active worksheet from Excel for your reference.

    Sub GetQuerySQL()
    
    Dim app As Access.Application
    Set app = New Access.Application
    
    app.OpenCurrentDatabase "C:\Users\Database1.accdb"
    
    Set db = app.CurrentDb
    
    i = 1
    For Each obj In db.querydefs
       ActiveSheet.Range("A" & i).Value = obj.Sql
       i = i + 1
    Next obj
    
    app.CurrentDb.Close
    app.Quit
    Set app = Nothing
    
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by onimoD XLT Tuesday, September 2, 2014 10:39 AM
    Tuesday, September 2, 2014 7:51 AM
    Moderator
  • For some reason I think you are taking a detour exporting your access database to an sql server.

    Have you ever seen THIS?

    Hi Michal, thanks for responding. Yes I have tried this method and, for whatever reason, found it fraught with problems. To transfer the data and queries, as is, this migration will need scripting. This structural interrogation exercise is to enable a vendor to quote on this.
    Tuesday, September 2, 2014 10:37 AM
  • Thanks Luna, that's just what I need.

    I was struggling to do this via an ADODB connection but I see opening the Access db simplifies matters.

    Tuesday, September 2, 2014 10:39 AM
  • SQL to VBA.

    http://allenbrowne.com/ser-71.html


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, September 6, 2014 12:38 PM