|
Export All VBA Code
By D. C. Conlin
Save your VBA code separately from the MDB file so that it's not lost if the MDB file becomes corrupted.
Microsoft Access database developers often want to keep their VBA code backed up separately from their MDB files, so that when a disaster befalls the database file, the lost or corrupted MDB file doesn't mean that the hard work invested in developing the code for that database application is gone forever, too. However, when exporting VBA code from an MDB file, two issues commonly come up:
1.) How do you separate the code modules from the Form and Report objects?
2.) Professional Microsoft Access database development shouldn't depend on the SaveAsText subroutine to export modules, because SaveAsText is an undocumented and unsupported subroutine.
The first issue is solved by creating a Recordset that selects the names of the Form and Report objects from the MSysObjects system table, then iterating through each of these records to check whether the Form or Report has a module and, if so, using the SaveAsText or OutputTo subroutine to save the code to an individual file. The example that follows will show both methods.
The second issue concerns the advice given by Microsoft to developers, which is to not use undocumented and unsupported features because they may not work as the user intends, and even when they do, these unsupported features may not work in future versions of the product. Essentially, Microsoft is saying, "Don't use unsupported features," because you could be left high and dry. The problem with this advice is that it also applies to documented, supported features in the current versions of Microsoft's applications, because Microsoft can remove support at any time for these features.
Using documented, supported features does not guarantee that Microsoft won't later disable them. A significant (read: very handy) feature that no longer works in most recent versions of Access is described in the following Microsoft Knowledge Base article: "List of supported data sources for importing, exporting, and linking in an Access 2002 database (up until 18 Oct. '05)": (Note that these were also available in Microsoft Office Access 2003 until Service Pack 2 was installed.)
http://support.microsoft.com/default.aspx?id=283294
"You cannot change, add, or delete data in tables that are linked to an Excel workbook in Office Access 2003 or in Access 2002:"
http://support.microsoft.com/kb/904953
In case you hadn't heard, here is the reason this documented and supported feature was removed:
http://news.com.com/Patent+ruling+costs+Microsoft+8.9+million/2100-1007_3-5 735432.html
To back up all of your VBA code modules to individual files, including the Form and Report modules, copy and paste the following two procedures in a standard module, then save and compile the code:
Public Sub backupModules()
On Error GoTo ErrHandler
Dim sPath As String
sPath = "C:\Backup"
MsgBox "All VBA code exported = " & exportModules(sPath)
Exit Sub
ErrHandler:
MsgBox "Error in backupModules( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Sub
Public Function exportModules(sDestPath As String) As Boolean
On Error GoTo ErrHandler
Dim recSet As DAO.Recordset
Dim frm As Form
Dim rpt As Report
Dim sqlStmt As String
Dim sObjName As String
Dim idx As Long
Dim fOpenedRecSet As Boolean
'--------------------------------------------------------------
' Ensure that there's a backslash at the end of the path.
'--------------------------------------------------------------
If (Mid$(sDestPath, Len(sDestPath), 1) <> "\") Then
sDestPath = sDestPath & "\"
End If
'--------------------------------------------------------------
' Export standard modules and classes.
'--------------------------------------------------------------
sqlStmt = "SELECT [Name] " & _
"FROM MSysObjects " & _
"WHERE ([Type] = -32761);"
Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True
If (Not (recSet.BOF And recSet.EOF)) Then
recSet.MoveLast
recSet.MoveFirst
For idx = 1 To recSet.RecordCount
SaveAsText acModule, recSet.Fields(0).Value, _
sDestPath & recSet.Fields(0).Value & ".bas"
If (Not (recSet.EOF)) Then
recSet.MoveNext
End If
Next idx
End If
'--------------------------------------------------------------
' Export form modules.
'--------------------------------------------------------------
sqlStmt = "SELECT [Name] " & _
"FROM MSysObjects " & _
"WHERE ([Type] = -32768);"
Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True
If (Not (recSet.BOF And recSet.EOF)) Then
recSet.MoveLast
recSet.MoveFirst
For idx = 1 To recSet.RecordCount
sObjName = recSet.Fields(0).Value
DoCmd.OpenForm sObjName, acDesign
Set frm = Forms(sObjName)
If (frm.HasModule) Then
DoCmd.OutputTo acOutputModule, "Form_" & _
sObjName, acFormatTXT, sDestPath & _
sObjName & ".bas"
End If
DoCmd.Close acForm, sObjName
If (Not (recSet.EOF)) Then
recSet.MoveNext
End If
Next idx
End If
'--------------------------------------------------------------
' Export report modules.
'--------------------------------------------------------------
sqlStmt = "SELECT [Name] " & _
"FROM MSysObjects " & _
"WHERE ([Type] = -32764);"
Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True
If (Not (recSet.BOF And recSet.EOF)) Then
recSet.MoveLast
recSet.MoveFirst
For idx = 1 To recSet.RecordCount
sObjName = recSet.Fields(0).Value
DoCmd.OpenReport sObjName, acDesign
Set rpt = Reports(sObjName)
If (rpt.HasModule) Then
DoCmd.OutputTo acOutputModule, "Report_" & _
sObjName, acFormatTXT, sDestPath & _
sObjName & ".bas"
End If
DoCmd.Close acReport, sObjName
If (Not (recSet.EOF)) Then
recSet.MoveNext
End If
Next idx
End If
exportModules = True ' Success.
CleanUp:
If (fOpenedRecSet) Then
recSet.Close
fOpenedRecSet = False
End If
Set frm = Nothing
Set rpt = Nothing
Set recSet = Nothing
Exit Function
ErrHandler:
MsgBox "Error in exportModules( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
exportModules = False ' Failed.
GoTo CleanUp
End Function ' exportModules( )
You can read about your favorite expert contributors.
Copyright © 2007 Q-Built Solutions. All rights reserved.
 Top
Visitors since 30 Jan. '07:
|
