Exporting all QueryDefs
contained within a Microsoft Access Database file (MDB) to single files is straightforward, once you know how.
The following VBA function does just this:
Public Sub ExportAllQueriesToFile() Dim qdf As QueryDef Dim strFileName As String For Each qdf In CurrentDb.QueryDefs If Left(qdf.Name, 1) <> "~" Then strFileName = qdf.Name & ".SQL" Set fs = CreateObject("Scripting.FileSystemObject") Set destinationFile = _ fs.CreateTextFile("c:\" & strFileName, True) destinationFile.WriteLine "-----------" destinationFile.WriteLine qdf.Name destinationFile.WriteLine "-----------" destinationFile.WriteLine "" destinationFile.WriteLine qdf.sql destinationFile.Close End If Next qdf MsgBox "Done" End Sub
Simply create a new VBA module inside the MDB file where you want to export the queries from and execute the function.
Optionally, adjust the destination folder path.
Exporting other objects like Reports or Forms
To export other objects, use the function ExportDatabaseObjects
described in this forum posting.
This function exports
- Tables definitions
- Form definitions
- Report definitions
- Script definitions
- Module definitions
- Query definitions
from a given Microsoft Office Access MDB or ACCDB database file.