Documenting queries: Extracting SQL syntax

MrTibbs

Registered User.
Local time
Today, 06:54
Joined
Oct 10, 2001
Messages
101
Access '97
I have hundreds of queries that I would like to export the actual query (not the resulting records) into a simple text file.

How may I programatically extract the SQL code?

What I would end up with

query1_example
SELECT tbl_temp_crosstab.*
FROM tbl_temp_crosstab
WHERE (((tbl_temp_crosstab.Total) Is Not Null))
ORDER BY tbl_temp_crosstab.[Process Group], tbl_temp_crosstab.[Process Order], tbl_temp_crosstab.Type;

query2 example
...

I would also consider export to Excel or similar.

I am sure this is possible but have no clue how to achieve it. Is there a good tool / utiltiy that could help?

I want to migrate from Access '97 into an SQL server [the database is aproaching 1Gb] so I want the SQL syntax exported for reference.
:confused:
 
This is not my work but i have used it and it works.
Of course you need to change "C:\qryDef" to the full path of where you want to create your txt file.If you have 100's of query it will take a couple of mins to run the sub


Sub OutputQry()
Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
Application.SaveAsText acQuery, qdf.Name, "C:\qryDef" & qdf.Name & ".txt"
Next qdf

End Sub


Regards
Bjackson
 
I should mention ,that sub will create a txt file for every querydef in the db including combo boxes,list boxes etc.If you just want the stored querys you will need to export them into an empty db and then run the sub
Regards
Bjackson
 
Works .. but not quite what I am looking for

The suggested code exports the query as a text file but not in SQL view.

I am looking for the code as SQL (as seen in design / view as SQL) rather than the output given by the code above.

Any further suggestions?

e.g.
Wanted:
PARAMETERS LowDate DateTime, HighDate DateTime;
SELECT tblPGPPerformance.ID, tblPGPPerformance.ProductGroup, tblPGPPerformance.[Product Description], tblPGPPerformance.[Pack Size], tblPGPPerformance.[Process MfgPro], tblPGPPerformance.PreferredRoute, tblPGPPerformance.OutputPerShift, tblPGPPerformance.ManningDirect, tblPGPPerformance.ManningIndirect, tblPGPPerformance.BillingRate, tblPGPPerformance.StartDate, tblPGPPerformance.EndDate, tblPGPPerformance.TheoreticalMaxOutPerHour, tblPGPPerformance.SubProductGroup, tblPGPPerformance.SubProcess, tblPGPPerformance.[Fixed Indirect Hours], tblPGPPerformance.RoutingPercentage, IIf([tblpgpvariations].[startdate] Is Null,[lowdate]-(Weekday([lowdate],2)-1),[tblpgpvariations].[startdate]) AS VariationStartDate, IIf([tblpgpvariations].[enddate] Is Null,[highdate]+(7-(Weekday([highdate],2))),[tblpgpvariations].[enddate]) AS VariationEndDate, Weekday([lowdate],2) AS LowDayOfWeek, IIf([tblpgpvariations].[PercentOfRouting] Is Null,1,[tblpgpvariations].[PercentOfRouting]) AS RoutingVariationPercent, tblPGPVariations.RoutingType, tblPGPVariations.RoutingReason
FROM tblPGPPerformance LEFT JOIN tblPGPVariations ON (tblPGPPerformance.[Process MfgPro] = tblPGPVariations.[Process MfgPro]) AND (tblPGPPerformance.ProductGroup = tblPGPVariations.ProductGroup);

rather than

Code suggested writes as:
Operation = 1
Option = 0
Begin InputTables
Name ="tblPGPVariations"
Name ="tblPGPPerformance"
End
Begin OutputColumns
Expression ="tblPGPPerformance.ID"
Expression ="tblPGPPerformance.ProductGroup"
Expression ="tblPGPPerformance.[Product Description]"
Expression ="tblPGPPerformance.[Pack Size]"
Expression ="tblPGPPerformance.[Process MfgPro]"
Expression ="tblPGPPerformance.PreferredRoute"
Expression ="tblPGPPerformance.OutputPerShift"
Expression ="tblPGPPerformance.ManningDirect"
Expression ="tblPGPPerformance.ManningIndirect"
Expression ="tblPGPPerformance.BillingRate"
Expression ="tblPGPPerformance.StartDate"
Expression ="tblPGPPerformance.EndDate"
Expression ="tblPGPPerformance.TheoreticalMaxOutPerHour"
Expression ="tblPGPPerformance.SubProductGroup"
Expression ="tblPGPPerformance.SubProcess"
Expression ="tblPGPPerformance.[Fixed Indirect Hours]"
Expression ="tblPGPPerformance.RoutingPercentage"
Alias ="VariationStartDate"
Expression ="IIf([tblpgpvariations].[startdate] Is Null,[lowdate]-(Weekday([lowdate],2)-1),[t"
"blpgpvariations].[startdate])"
Alias ="VariationEndDate"
Expression ="IIf([tblpgpvariations].[enddate] Is Null,[highdate]+(7-(Weekday([highdate],2))),"
"[tblpgpvariations].[enddate])"
Alias ="LowDayOfWeek"
Expression ="Weekday([lowdate],2)"
Alias ="RoutingVariationPercent"
Expression ="IIf([tblpgpvariations].[PercentOfRouting] Is Null,1,[tblpgpvariations].[PercentO"
"fRouting])"
Expression ="tblPGPVariations.RoutingType"
Expression ="tblPGPVariations.RoutingReason"
End
Begin Parameters
Name ="LowDate"
Flag = 8
Name ="HighDate"
Flag = 8
End
Begin Joins
LeftTable ="tblPGPPerformance"
RightTable ="tblPGPVariations"
Expression ="tblPGPPerformance.ProductGroup = tblPGPVariations.ProductGroup"
Flag = 2
LeftTable ="tblPGPPerformance"
RightTable ="tblPGPVariations"
Expression ="tblPGPPerformance.[Process MfgPro] = tblPGPVariations.[Process MfgPro]"
Flag = 2
End
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbByte "RecordsetType" ="0"
dbBoolean "OrderByOn" ="0"
dbText "Description" ="Link tblPGPPerformance and tblPGPVariations. Shows all standards available with"
"in the date range"
Begin
Begin
dbText "Name" ="tblPGPPerformance.RoutingPercentage"
dbInteger "ColumnWidth" ="1980"
dbBoolean "ColumnHidden" ="0"
End
Begin
dbText "Name" ="RoutingVariationPercent"
dbInteger "ColumnWidth" ="2490"
dbBoolean "ColumnHidden" ="0"
End
End
Begin
State = 2
Left = -6
Top = -25
Right = 802
Bottom = 487
Left = -1
Top = -1
Right = 797
Bottom = 144
Left = 0
Top = 0
ColumnsShown = 539
Begin
Left = 172
Top = 6
Right = 311
Bottom = 113
Top = 4
Name ="tblPGPVariations"
End
Begin
Left = 38
Top = 6
Right = 134
Bottom = 113
Top = 0
Name ="tblPGPPerformance"
End
End
 
Ensure the text file exists and:

Code:
Public Function ExportQueries() As Boolean
    On Error GoTo Err_ExportQueries
    Const strFileName As String = "C:\Test.txt"
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = CurrentDb
    Open strFileName For Output As #1
        For Each qdf In db.QueryDefs
            Print #1, qdf.Name & vbCrLf & qdf.SQL
        Next
    Close #1
    ExportQueries = True
Exit_ExportQueries:
    Set qdf = Nothing
    Set db = Nothing
    Exit Function
Err_ExportQueries:
    ExportQueries = False
    Resume Exit_ExportQueries
End Function
 
Well You could print out all the query def sqls to the debug window
then copy and paste to 1 txt file,or instead of debug print you could add the sql strings to a table then create as many txt files as you require.Not sure how you would get the parameters to print out

Function PrintOutMysql()

Dim dbs As Database
Dim qdfLoop As QueryDef
Set dbs = CurrentDb()

With dbs

For Each qdfLoop In .QueryDefs
Debug.Print qdfLoop.Name
Debug.Print qdfLoop.SQL
Next qdfLoop


End With

End Function
 
SJ's code .. export SQL worked beautifully

SJ

The function EXportQueries to export SQL statements worked well and very quickly.

The file was created by the code and is overwritten if the file already exists .. exactly the behaviour I wanted.

BJackson,
Your code also worked but I have so many queries it overflowed the debug window.

Thank you both.

Tony
:D
 
Glad you got it working

The original code i posted is handy if you want to store all your querydef as a txt back up in case your db crashes,you can then import them back into any new db

Regards
Bjackson
 
The built in documenter would also have done this.
 
built-in documenter vs SQL extract ...

The documenter takes several hours to run on my database where the query and SQL code extractions ran in less than 5 seconds each.

My background includes various Unix's so a small tool that does the job required, quickly, is much appreciated.

I wanted the bare SQL where the documenter offers all kinds of extras (security settings etc.). Different tools for different needs I guess.

Thank you for all the pointers


Tony
 
Public Function ExportQueries() As Boolean
On Error GoTo Err_ExportQueries
Const strFileName As String = "C:\Test.txt"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Open strFileName For Output As #1
For Each qdf In db.QueryDefs
Print #1, qdf.Name & vbCrLf & qdf.SQL
Next
Close #1
ExportQueries = True
Exit_ExportQueries:
Set qdf = Nothing
Set db = Nothing
Exit Function
Err_ExportQueries:
ExportQueries = False
Resume Exit_ExportQueries
End Function

Very cool. Thks
kh
 

Users who are viewing this thread

Back
Top Bottom