ACC2000: How to Export All Modules in a Database to Text Files (209869)

MORE INFORMATION

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

To export all the modules in the sample database Northwind.mdb to text files, follow these steps:

1. Start Microsoft Access, and then open the sample database Northwind.mdb.
2. Create a module, and then type the following lines in the Declarations section:

   Option Explicit
   Public MyMsg as String, MyTitle as String ' Global variables.
   					

3. Type or paste the following procedure:

   Function OutPutModules(strMyloc As Variant)
   '******************************************************************
   ' This function will export ALL modules in the current dbase as
   ' text files to the user supplied location or to the default
   ' location. This function will except an optional argument as the
   ' location to export to. The Optional argument should consist of a
   ' path to the location where you want to store the text files. If
   ' you don't supply the optional argument, the default location of
   ' c:\ will be used.
   '******************************************************************
   On Error GoTo OutPutModules_Error
   
   Dim strMyMsg As String
   Dim strMyTitle As String
   Dim DB As DAO.Database
   Dim strModuleName As String
   Dim intI As Integer
   Dim mdl As Module
   Dim strMyExt As String
   Dim strDisplayError As String
   Dim strNewMsg As String
   Dim strNewLoc As String
   
   Set DB = CurrentDb()
   ' If user enters the backslash in the location, for example, 'A:\',
   ' parse out the '\' backslash, assign the drive letter and the colon
   ' ONLY to variable 'Myloc'.
   If Right(strMyloc, 1) = "\" Then strMyloc = Left(strMyloc, _
      Len(strMyloc) - 1)
   strMyExt = ".txt"  ' Set extension for file names.
   ' Loop through module names.
   For intI = 0 To DB.Containers("Modules").Documents.Count - 1
      ' Set string variable(strModuleName) to module names.
      strModuleName = DB.Containers("Modules").Documents(intI).Name
      ' Print out ALL modules in designated format to designated
      ' drive/folder.
      strNewLoc = strMyloc & "\" & strModuleName & strMyExt
      DoCmd.OutputTo acOutputModule, strModuleName, acFormatTXT, _
         strNewLoc, 0
   Next intI
   ' Display message with # of objects exported.
   strNewMsg = intI & " Module Objects Exported" & Chr(13) & Chr(10)
   strNewMsg = strNewMsg & "to " & strMyloc & "."
   MsgBox strNewMsg, vbOK, "Export Objects"
   
   Exit_OutPutModules:
      Exit Function
   
   OutPutModules_Error:
      Set mdl = Modules(strModuleName)
      strMyTitle = "Error in Procedure: OutPutTo ; Module: " & mdl
      strMyMsg = "Error Number: " & Err.Number & Chr(13) & Chr(10)
      strMyMsg = strMyMsg & "Error Description :" & Err.Description & _
         Chr(13) & Chr(10)
      MsgBox strMyMsg, vbExclamation, strMyTitle
      Resume Exit_OutPutModules
   End Function
   
   					

4. To test this function, type the following line in the Immediate window, and then press ENTER:

   ?OutPutModules("C:\")

   Note that you receive the following response:

   3 Modules Exported to C:\