How to programmatically convert multiple Access databases in Access 2002 (304318)

MORE INFORMATION

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.

1. Open a new or an existing database.
2. On the Insert menu, click Module. Type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

3. Type or paste the following procedure:

    
   Sub ConvertDb(oldDbPath As String, newDbPath As String)
   
   'NOTE: Uncomment the line Dim x if you are using the 
   'alternative Shell function method listed later in this code.
   
   'Dim x
   
   Dim strDBName As String
   Dim strOLDDB As String
   Dim strNewDb As String
   
   strDBName = Dir(oldDbPath & "\*.MDB")   ' Retrieve the first MDB file entry.
   
   'Loop though the files in the folder to find MDB files.
   
   Do While strDBName <> ""
   ' Ignore the current folder and the encompassing folder.
     If strDBName <> "." And strDBName <> ".." Then
           
       If Right(strDBName, 3) = "mdb" Then
          strOLDDB = oldDbPath & "\" & strDBName
          strNewDb = newDbPath & "\" & strDBName
   
          'NOTE: Comment out the following four lines if you are using the
          'Shell function method.
   
          Application.ConvertAccessProject _
          SourceFilename:=strOLDDB, _
          DestinationFilename:=strNewDb, _
          DestinationFileFormat:=acFileFormatAccess2000
   
          'NOTE: The alternative method is to use the Shell function to open
          'Access and concatenate the Convert switch instead of using the
          'ConvertAccessProject method. To use this method, uncomment the 
          'following two lines.
          
          ' x = Shell("C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE " & Chr(34) _
          '      & strOLDDB & Chr(34) & " /Convert " & Chr(34) & strNewDb & Chr(34))
   
          MsgBox "Conversion complete."
       End If
     End If
   strDBName = Dir    ' Get next MDB.
   Loop
   
   End Sub
   
   
   					

4. To test this function, create two folders in the root directory of drive C. Name the folders DatabasesToConvert and ConvertedDatabases. Put the databases that you want to convert in the DatabasesToConvert folder, type the following line in the Immediate window, and then press ENTER:

   call ConvertDb("C:\DatabasesToConvert", "C:\ConvertedDatabases")