ACC: How to Export All Modules in a Database to Text Files

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article shows you how to export all modules in a database to text files.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Open the sample database Northwind.mdb.



2. Create a module and type the following lines in the Declarations section:




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

3. Type 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 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
         ' In Microsoft Access 7.0 change acOutputModule to acModule
      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:
         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 Debug window, and then press ENTER:

   
   ?OutPutModules("C:\My Documents") 

   Note that you receive the following message:




3 Modules Exported to C:\My Documents

Additional query words: textfile textfiles exporting

Keywords          : PgmHowto 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 6, 1999