For anyone stuck with Access 97, I was not able to get the other answers to work. Using a combination of Oliver's and DaveParillo's excellent answers and making some modifications, I was able to get the scripts working with our Access 97 databases. It's also a bit more user-friendly since it asks which folder to place the files.
AccessExport.vbs:
' Converts all modules, classes, forms and macros from an Access file (.mdb) <input file> to
' text and saves the results in separate files to <path>. Requires Microsoft Access.
Option Explicit
Const acQuery = 1
Const acForm = 2
Const acModule = 5
Const acMacro = 4
Const acReport = 3
Const acCmdCompactDatabase = 4
Const TemporaryFolder = 2
Dim strMDBFileName : strMDBFileName = SelectDatabaseFile
Dim strExportPath : strExportPath = SelectExportFolder
CreateExportFolders(strExportPath)
Dim objProgressWindow
Dim strOverallProgress
CreateProgressWindow objProgressWindow
Dim strTempMDBFileName
CopyToTempDatabase strMDBFileName, strTempMDBFileName, strOverallProgress
Dim objAccess
Dim objDatabase
OpenAccessDatabase objAccess, objDatabase, strTempMDBFileName, strOverallProgress
ExportQueries objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportForms objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportReports objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportMacros objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
ExportModules objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress
objAccess.CloseCurrentDatabase
objAccess.Quit
DeleteTempDatabase strTempMDBFileName, strOverallProgress
objProgressWindow.Quit
MsgBox "Successfully exported database."
Private Function SelectDatabaseFile()
MsgBox "Please select the Access database to export."
Dim objFileOpen : Set objFileOpen = CreateObject("SAFRCFileDlg.FileOpen")
If objFileOpen.OpenFileOpenDlg Then
SelectDatabaseFile = objFileOpen.FileName
Else
WScript.Quit()
End If
End Function
Private Function SelectExportFolder()
Dim objShell : Set objShell = CreateObject("Shell.Application")
SelectExportFolder = objShell.BrowseForFolder(0, "Select folder to export the database to:", 0, "").self.path & "\"
End Function
Private Sub CreateExportFolders(strExportPath)
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
MsgBox "Existing folders from a previous Access export under " & strExportPath & " will be deleted!"
If objFileSystem.FolderExists(strExportPath & "Queries\") Then
objFileSystem.DeleteFolder strExportPath & "Queries", true
End If
objFileSystem.CreateFolder(strExportPath & "Queries\")
If objFileSystem.FolderExists(strExportPath & "Forms\") Then
objFileSystem.DeleteFolder strExportPath & "Forms", true
End If
objFileSystem.CreateFolder(strExportPath & "Forms\")
If objFileSystem.FolderExists(strExportPath & "Reports\") Then
objFileSystem.DeleteFolder strExportPath & "Reports", true
End If
objFileSystem.CreateFolder(strExportPath & "Reports\")
If objFileSystem.FolderExists(strExportPath & "Macros\") Then
objFileSystem.DeleteFolder strExportPath & "Macros", true
End If
objFileSystem.CreateFolder(strExportPath & "Macros\")
If objFileSystem.FolderExists(strExportPath & "Modules\") Then
objFileSystem.DeleteFolder strExportPath & "Modules", true
End If
objFileSystem.CreateFolder(strExportPath & "Modules\")
End Sub
Private Sub CreateProgressWindow(objProgressWindow)
Set objProgressWindow = CreateObject ("InternetExplorer.Application")
objProgressWindow.Navigate "about:blank"
objProgressWindow.ToolBar = 0
objProgressWindow.StatusBar = 0
objProgressWindow.Width = 320
objProgressWindow.Height = 240
objProgressWindow.Visible = 1
objProgressWindow.Document.Title = "Access export in progress"
End Sub
Private Sub CopyToTempDatabase(strMDBFileName, strTempMDBFileName, strOverallProgress)
strOverallProgress = strOverallProgress & "Copying to temporary database...<br/>"
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
strTempMDBFileName = objFileSystem.GetSpecialFolder(TemporaryFolder) & "\" & objFileSystem.GetBaseName(strMDBFileName) & "_temp.mdb"
objFileSystem.CopyFile strMDBFileName, strTempMDBFileName
End Sub
Private Sub OpenAccessDatabase(objAccess, objDatabase, strTempMDBFileName, strOverallProgress)
strOverallProgress = strOverallProgress & "Compacting temporary database...<br/>"
Set objAccess = CreateObject("Access.Application")
objAccess.Visible = false
CompactAccessDatabase objAccess, strTempMDBFileName
strOverallProgress = strOverallProgress & "Opening temporary database...<br/>"
objAccess.OpenCurrentDatabase strTempMDBFileName
Set objDatabase = objAccess.CurrentDb
End Sub
' Sometimes the Compact Database command errors out, and it's not serious if the database isn't compacted first.
Private Sub CompactAccessDatabase(objAccess, strTempMDBFileName)
On Error Resume Next
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objAccess.DbEngine.CompactDatabase strTempMDBFileName, strTempMDBFileName & "_"
objFileSystem.CopyFile strTempMDBFileName & "_", strTempMDBFileName
objFileSystem.DeleteFile strTempMDBFileName & "_"
End Sub
Private Sub ExportQueries(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Queries (Step 1 of 5)...<br/>"
Dim counter
For counter = 0 To objDatabase.QueryDefs.Count - 1
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & objDatabase.QueryDefs.Count
objAccess.SaveAsText acQuery, objDatabase.QueryDefs(counter).Name, strExportPath & "Queries\" & Clean(objDatabase.QueryDefs(counter).Name) & ".sql"
Next
End Sub
Private Sub ExportForms(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Forms (Step 2 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Forms")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acForm, objDocument.Name, strExportPath & "Forms\" & Clean(objDocument.Name) & ".form"
objAccess.DoCmd.Close acForm, objDocument.Name
Next
End Sub
Private Sub ExportReports(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Reports (Step 3 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Reports")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acReport, objDocument.Name, strExportPath & "Reports\" & Clean(objDocument.Name) & ".report"
Next
End Sub
Private Sub ExportMacros(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Macros (Step 4 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Scripts")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acMacro, objDocument.Name, strExportPath & "Macros\" & Clean(objDocument.Name) & ".macro"
Next
End Sub
Private Sub ExportModules(objAccess, objDatabase, objProgressWindow, strExportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Exporting Modules (Step 5 of 5)...<br/>"
Dim counter : counter = 1
Dim objContainer : Set objContainer = objDatabase.Containers("Modules")
Dim objDocument
For Each objDocument In objContainer.Documents
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter & " of " & objContainer.Documents.Count
counter = counter + 1
objAccess.SaveAsText acModule, objDocument.Name, strExportPath & "Modules\" & Clean(objDocument.Name) & ".module"
Next
End Sub
Private Sub DeleteTempDatabase(strTempMDBFileName, strOverallProgress)
On Error Resume Next
strOverallProgress = strOverallProgress & "Deleting temporary database...<br/>"
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objFileSystem.DeleteFile strTempMDBFileName, true
End Sub
' Windows doesn't like certain characters, so we have to filter those out of the name when exporting
Private Function Clean(strInput)
Dim objRegexp : Set objRegexp = New RegExp
objRegexp.IgnoreCase = True
objRegexp.Global = True
objRegexp.Pattern = "[\\/:*?""<>|]"
Dim strOutput
If objRegexp.Test(strInput) Then
strOutput = objRegexp.Replace(strInput, "")
MsgBox strInput & " is being exported as " & strOutput
Else
strOutput = strInput
End If
Clean = strOutput
End Function
And for importing files into the database, should you need to recreate the database from scratch or you wish to modify files outside of Access for some reason.
AccessImport.vbs:
' Imports all of the queries, forms, reports, macros, and modules from text
' files to an Access file (.mdb). Requires Microsoft Access.
Option Explicit
const acQuery = 1
const acForm = 2
const acModule = 5
const acMacro = 4
const acReport = 3
const acCmdCompileAndSaveAllModules = &H7E
Dim strMDBFilename : strMDBFilename = SelectDatabaseFile
CreateBackup strMDBFilename
Dim strImportPath : strImportPath = SelectImportFolder
Dim objAccess
Dim objDatabase
OpenAccessDatabase objAccess, objDatabase, strMDBFilename
Dim objProgressWindow
Dim strOverallProgress
CreateProgressWindow objProgressWindow
ImportQueries objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportForms objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportReports objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportMacros objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
ImportModules objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress
objAccess.CloseCurrentDatabase
objAccess.Quit
objProgressWindow.Quit
MsgBox "Successfully imported objects into the database."
Private Function SelectDatabaseFile()
MsgBox "Please select the Access database to import the objects from. ALL EXISTING OBJECTS WITH THE SAME NAME WILL BE OVERWRITTEN!"
Dim objFileOpen : Set objFileOpen = CreateObject( "SAFRCFileDlg.FileOpen" )
If objFileOpen.OpenFileOpenDlg Then
SelectDatabaseFile = objFileOpen.FileName
Else
WScript.Quit()
End If
End Function
Private Function SelectImportFolder()
Dim objShell : Set objShell = WScript.CreateObject("Shell.Application")
SelectImportFolder = objShell.BrowseForFolder(0, "Select folder to import the database objects from:", 0, "").self.path & "\"
End Function
Private Sub CreateBackup(strMDBFilename)
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
objFileSystem.CopyFile strMDBFilename, strMDBFilename & ".bak"
End Sub
Private Sub OpenAccessDatabase(objAccess, objDatabase, strMDBFileName)
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase strMDBFilename
objAccess.Visible = false
Set objDatabase = objAccess.CurrentDb
End Sub
Private Sub CreateProgressWindow(ByRef objProgressWindow)
Set objProgressWindow = CreateObject ("InternetExplorer.Application")
objProgressWindow.Navigate "about:blank"
objProgressWindow.ToolBar = 0
objProgressWindow.StatusBar = 0
objProgressWindow.Width = 320
objProgressWindow.Height = 240
objProgressWindow.Visible = 1
objProgressWindow.Document.Title = "Access import in progress"
End Sub
Private Sub ImportQueries(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = "Importing Queries (Step 1 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Queries\")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strQueryName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strQueryName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acQuery, strQueryName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportForms(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Forms (Step 2 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Forms\")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strFormName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strFormName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acForm, strFormName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportReports(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Reports (Step 3 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Reports\")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strReportName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strReportName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acReport, strReportName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportMacros(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Macros (Step 4 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Macros\")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strMacroName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strMacroName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acMacro, strMacroName, file.Path
counter = counter + 1
Next
End Sub
Private Sub ImportModules(objAccess, objDatabase, objProgressWindow, strImportPath, strOverallProgress)
strOverallProgress = strOverallProgress & "Importing Modules (Step 5 of 5)...<br/>"
Dim counter : counter = 0
Dim folder : Set folder = objFileSystem.GetFolder(strImportPath & "Modules\")
Dim objFileSystem : Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Dim file
Dim strModuleName
For Each file in folder.Files
objProgressWindow.Document.Body.InnerHTML = strOverallProgress & counter + 1 & " of " & folder.Files.Count
strModuleName = objFileSystem.GetBaseName(file.Name)
objAccess.LoadFromText acModule, strModuleName, file.Path
counter = counter + 1
Next
' We need to compile the database whenever any module code changes.
If Not objAccess.IsCompiled Then
objAccess.RunCommand acCmdCompileAndSaveAllModules
End If
End Sub
