export SQL from Queries

[nik]

Hi,

Is there a way to export the SQL from all of the Queries inside an
Access Database into individual text files. I can obviously open each
one up by hand in Design mode and copy/paste into a txt file. I also
see there there is an export menu when I right click on the individual
Queries file, but I get an error doing that and both of these methods
will take a long time to access each Query individually, so I am
hoping there is some sort of simple export all option that will allow
me to export the SQL from all of the Queries objects into individual
text files.

Thanks,
Nik

 

[Dirk Goldgar]

in message

Hi,

Is there a way to export the SQL from all of the Queries inside an
Access Database into individual text files. I can obviously open each
one up by hand in Design mode and copy/paste into a txt file. I also
see there there is an export menu when I right click on the individual
Queries file, but I get an error doing that and both of these methods
will take a long time to access each Query individually, so I am
hoping there is some sort of simple export all option that will allow
me to export the SQL from all of the Queries objects into individual
text files.

Something along these lines should work:

'----- start of code -----
Sub ExportQueries()

' Export the SQL of each query to an individual text file.

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strFileName As String
Dim lngQueryCount As Long
Dim intFileNo As Integer

Set db = CurrentDb

SysCmd acSysCmdSetStatus, "Exporting queries ..."
DoCmd.Hourglass True

For Each qdf In db.QueryDefs
With qdf
If Left$(.Name, 3) <> "~sq" Then
lngQueryCount = lngQueryCount + 1
intFileNo = FreeFile()
strFileName = CurrentProject.path & "\" & .Name & ".sql"
Open strFileName For Output As #intFileNo
Print #intFileNo, .SQL
Close #intFileNo
End If
End With
Next qdf

Exit_Point:
On Error Resume Next
DoCmd.Hourglass False
SysCmd acSysCmdClearStatus
If intFileNo <> 0 Then Close intFileNo
Set qdf = Nothing
Set db = Nothing
MsgBox "Exported " & lngQueryCount & " queries."
Exit Sub

Err_Handler:
DoCmd.Hourglass False
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub

'----- end of code -----

 

[a a r o n . k e m p f]

Select Text From SysComments should work just great for you.

you could probably do it via BCP in a single dos command.

-Aaron