VBA Access design (sql) view
-
I need help figuring out how to display the sql view of a query using VBA. I have an Access 2003 database that contains a little over 300 queries. Yikes! I know in Access you can open a query in Design view and then change the view to SQL view so that you can actually see the Select statement. Instead of doing that more than 300 times to cut and paste the query statement, I would like to write a quick macro to display the information. I was able to figure out how to loop through all my query objects and display the name, but I can't find a way to actually get at the sql view of the query. Below is the code I have: Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentData For Each obj In dbs.AllQueries MsgBox "sql view: " & obj.FullName Next obj When I watch the obj data, there isn't any properties for the sql view. I thought this information would be in there. Am I misunderstanding what this object contains? It seems like I'm pretty close because I'm able to see the name of all the queries I have in my DB. What crucial piece of information am I missing? Thanks, FMPol
-
I need help figuring out how to display the sql view of a query using VBA. I have an Access 2003 database that contains a little over 300 queries. Yikes! I know in Access you can open a query in Design view and then change the view to SQL view so that you can actually see the Select statement. Instead of doing that more than 300 times to cut and paste the query statement, I would like to write a quick macro to display the information. I was able to figure out how to loop through all my query objects and display the name, but I can't find a way to actually get at the sql view of the query. Below is the code I have: Dim obj As AccessObject Dim dbs As Object Set dbs = Application.CurrentData For Each obj In dbs.AllQueries MsgBox "sql view: " & obj.FullName Next obj When I watch the obj data, there isn't any properties for the sql view. I thought this information would be in there. Am I misunderstanding what this object contains? It seems like I'm pretty close because I'm able to see the name of all the queries I have in my DB. What crucial piece of information am I missing? Thanks, FMPol
You may want to try looking at this[^]. If that doesn't work for you, I got more results from a carefully worded Google search, "vba access query sql text[^]". No, seriously, I had to go through about 15 search phrases to find the right combination of keywords.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008 -
You may want to try looking at this[^]. If that doesn't work for you, I got more results from a carefully worded Google search, "vba access query sql text[^]". No, seriously, I had to go through about 15 search phrases to find the right combination of keywords.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008Thanks Dave that was exactly what I needed. I think every google search I tried had 'view' in it and that must have been my big problem. I've posted the new code below: Dim db As Database Set db = CurrentDb Dim curSQL As String Dim qrynam As String For Each qrydef In db.QueryDefs qrynam = qrydef.Name curSQL = qrydef.SQL MsgBox "sql name: " & qrynam & Chr$(13) & "sql view: " & curSQL Next