June 20, 2008 at 7:05 am
Does anyone know if there's a way to script the text of a Microsoft Access query and get in a text file?
Thank you
June 20, 2008 at 7:20 am
If you were to use either DAO or ADO with the VBA scripting options, you can tap into the Querydefs collection, which would have SQL code behind the Access queries.
I find DAO is better at dealing with the native Access objects, so I'd probably start there. depending on what version of Access you're in, DAO may not be one of the default references, so you may need to start there and add it back in.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 23, 2008 at 7:13 am
If I understood correctly, for a single query you could open it in design view in Access, click 'View', then 'SQL View'.
Copy the text and paste into notepad, then save as a text file.
(I am using 2003, no doubt the option is still available in Access 2007 via the command ribbon..)
Paul..
June 26, 2008 at 12:51 pm
Quick and dirty way:
OPEN "C:\Querytext.txt" FOR OUTPUT AS #1
PRINT #1, CurrentDb.QueryDefs("MyQueryName").SQL
CLOSE #1
June 27, 2008 at 7:54 am
Hi,
Expanding a little on the above suggestions. This will create 1 file containing the SQL of all the queries in the database it is used in.
Works in 2000 and 2003.
HTH
K.
Public Sub GetQuery()
Dim Db As DAO.Database
Dim QryDef As DAO.QueryDef
Dim nFile As Integer
Set Db = CurrentDb
nFile = 1
For Each QryDef In Db.QueryDefs
Open "C:\Querytext.txt" For Append Shared As #nFile
Print #nFile, QryDef.SQL
Close #nFile
nFile = nFile + 1
Next QryDef
Db.Close
Set Db = Nothing
End Sub
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply