Accessing SQL Query Text in VBA
Page 1 of 2
Computing
Databases
Accessing SQL Query Text in VBA
By Mike Chapple, About.com Guide November 13, 2007 Follow me on: Free Databases Newsletter! Enter email address Discuss in my forum
Share
Sign Up
In a recent forum posting, TMCHAM wrote: "I am trying to maintain a set of queries in several databases on our network. The queries I write in my local copy are tested, the I distribute them to the others in the office. I cant just send them my database because they may have their own queries, so we wrote a routine in another application that will recognize anything with a .txt extension in a certain directory and pull it in as a new query. This works great except when it comes time to create those TXT files. I am sick of opening up each query, switching to SQL view copying and pasting into notepad and saving the file. Every export option I found on the web has to do with exporting the results of the query, but what I want to do is through VBA write a routing that will create a TXT file with the name of the query, and the SQL code. I know how to create the TXT files, but not how to access the SQL code instead of the results of the query for writing to the file. Is there ANY way to do this in VBA?" Can you help? Interested in solutions to the same problem? Visit our forum to read more. Prev Next
Comments
(1)Richard Rostsays:
Leave a Comment
November 13, 2007 at 1:44 pm
Sure. If all youre trying to do is get at the SQL for a query, just loop through all of the queries in your database until you find the one youre looking for, then access its SQL property. Dim Dim Dim Dim db As Database qr As QueryDef QueryName As String MySQL As String
QueryName = MyQueryQ or whatever your query is named Set db = CurrentDb For Each qr In db.QueryDefs If qr.Name = QueryName Then MySQL = qr.SQL End If Next MsgBox MySQL Hope this helps. Richard Rost President/CEO http://www.599cd.com Microsoft Access Tutorials (2)Chris MacDonaldsays: Richard, Your code gets the job done, but looping through all queries for just one statement is not efficient if you know the query name. I would suggest setting it up as a function in a module and calling the function. Heres my code.. Place this function in any module.. Public Function GrabSQLtxt(QName As String) As String
July 28, 2009 at 3:23 pm
http://databases.about.com/b/2007/11/13/accessing-sql-query-text-in-vba... 4/24/2013
Accessing SQL Query Text in VBA
Page 2 of 2
Dim db As Database Dim qst As QueryDef Set db = CurrentDb Set qst = db.QueryDefs(QName) GrabSQLtxt = qst.SQL MsgBox qst.SQL End Function Then call the function like this. SQLstring = GrabSQLtxt(qryShowOpenID) This is the code I use. Works every time. You will need to output the code to a textbox and copy/paste it where you need it. Fairly simple. Now, if you have a set number of queries you want to loop through, you can just call the function with each query name. If you want to loop through them all, setup an array of strings, incorporate some of the code from Richards post and VOILA!!!! You have this. Dim I,J as integer Dim qr as querydef Dim SQLarray(256) as string I=0 For each qr in currentdb.querydefs I=I+1 SQLarray(I) = GrabSQLtxt(qr.name) Next For J = 1 to I Enter statements to transfer text to a text box. Next J With the text in a textbox, its ready to go. Sorry if there are some minor spelling or syntax mistakes. If there are, they are not hard to figure out. Thanks. CJ (3)dbelleruesays: Thanks Richard and Chris. What I want to end up with is a table in the Access 2007 database with two fields: QueryName and QuerySQL. There are 415 queries in the database, so a routine similar to your suggested code should do the job, only modified to add 415 records, one for each query. Could you suggest the code for this? Im familiar with writing VB6 apps, but not VBA in Access. I appreciate your help immensely!
March 9, 2013 at 7:11 pm
Leave a Comment
Top Related Searches Query Text
Sql Query
Vba
http://databases.about.com/b/2007/11/13/accessing-sql-query-text-in-vba... 4/24/2013
