ACC2000: How to Bulk Convert All Databases in a Folder to Access 2000 (245438)

MORE INFORMATION

Explanation of the ConvertMDBs Function

Used alone, the ConvertMDBs function converts a folder of .mdb files to Access 2000 format. Only the target folder is required. To create the ConvertMDBs function, follow the steps in the "ConvertMDBs Function and ProcessTree Function Example" section later in this article.

At the end of the ConvertMDBs function, an alert is displayed that indicates that the files have been converted. If you want to suppress the alert, just specify the second optional parameter with a value of 1. For example, if you want to use this function alone to convert all the files in the folder C:\MyDocuments\, type the following line in the Immediate window:

?ConvertMDBs("C:\MyDocuments\")

Note that you must provide a backslash "\" at the end of the path; otherwise, you receive an error.

To suppress the "Files Converted" alert, type the line as follows:

?ConvertMDBs("C:\MyDocuments\", 1)

IMPORTANT: The ConvertMDBs function does not differentiate between versions of Access databases. If the folder contains mixed versions of Access databases, for example, some Access 97 databases and some Access 2000 databases, you receive an error message for each Access 2000 database. Also, the function will not run successfully if there are conversion errors. Therefore, you should back up your files and folders before using these functions.

Explanation of the ProcessTree Function

The ProcessTree function allows you to process Access databases in a specified folder and in all subsequent subfolders below it until all Access databases in those folders are converted. Used together with the ConvertMDBs function, it can convert all Access databases in an entire folder structure. All that the function requires is the folder to start from. To create the ProcessTree function, follow the steps in the "ConvertMDBs Function and ProcessTree Function Example" section later in this article.

NOTE: The IMPORTANT note in the "Explanation of the ConvertMDBs Function" section earlier in this article still applies. If an Access 2000 database is encountered, the database is not converted, and you will receive an error message. You must click OK for the function to continue. Therefore, if possible, move all Access 2000 databases outside the target folders. Also, if conversion errors occur, the entire process will stop.

ConvertMDBs Function and ProcessTree Function Example

To create the ConvertMDBs function and the ProcessTree function and to see how to use them together, follow these steps:

Create a new folder named Test in the root directory of drive C.
Create two folders in C:\Test named Folder1 and Folder2.
In both Folder1 and Folder2, create four folders: FolderA, FolderB, FolderC, and FolderD.

The resulting structure should look as follows:
```
   C:\Test
      \Folder1
         \FolderA
         \FolderB

      \Folder2
         \FolderA
         \FolderB
					
```
Make a copy of the Access 97 version of the sample database Northwind.mdb, and place it in the C:\Test folder.
Make several copies of Northwind.mdb in the folder and give each one a unique name, for example:
```
   Northwind1.mdb
   Northwind2.mdb
					
```
Copy the files that you created in step 5, and then paste the copies in each of the folders and subfolders that you created in steps 2 and 3.
Start Access 2000, and then create a new, blank database.
Click Modules under Objects, and then click New.

Type or paste the following code in the Declarations section of the module:

Private Type STARTUPINFO
      cb As Long
      lpReserved As String
      lpDesktop As String
      lpTitle As String
      dwX As Long
      dwY As Long
      dwXSize As Long
      dwYSize As Long
      dwXCountChars As Long
      dwYCountChars As Long
      dwFillAttribute As Long
      dwFlags As Long
      wShowWindow As Integer
      cbReserved2 As Integer
      lpReserved2 As Long
      hStdInput As Long
      hStdOutput As Long
      hStdError As Long
   End Type

   Private Type PROCESS_INFORMATION
      hProcess As Long
      hThread As Long
      dwProcessID As Long
      dwThreadID As Long
   End Type

   Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
      hHandle As Long, ByVal dwMilliseconds As Long) As Long

   Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
      lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
      lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
      ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
      ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
      lpStartupInfo As STARTUPINFO, lpProcessInformation As _
      PROCESS_INFORMATION) As Long

   Private Declare Function CloseHandle Lib "kernel32" (ByVal _
      hObject As Long) As Long

   Private Const NORMAL_PRIORITY_CLASS = &H20&
   Private Const INFINITE = -1&
      'Added for example of opening a process hidden
   Private Const STARTF_USESHOWWINDOW = &H1
   Private Const SW_HIDE = 1

'This function executes command line actions.
Public Function ExecCmd(cmdline$)
      Dim proc As PROCESS_INFORMATION
      Dim start As STARTUPINFO
      Dim ReturnValue As Integer

      ' Initialize the STARTUPINFO structure.
      start.cb = Len(start)

      'Added to set constant to hide window.
      start.dwFlags = STARTF_USESHOWWINDOW
      start.wShowWindow = SW_HIDE

      ' Start the shelled application.
      ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, _
         NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

      ' Wait for the shelled application to finish.
      Do
         ReturnValue = WaitForSingleObject(proc.hProcess, 0)
         DoEvents
         Loop Until ReturnValue <> 258

      ReturnValue = CloseHandle(proc.hProcess)
End Function

Type or paste the following code for the ConvertMDBs function:

Function ConvertMDBs(strSourcePath As String, _
Optional strSuppressAlert As Boolean)
    
    Dim dbCurrent As String
    Dim acc
    
    'Finds the first occurrence of an MDB in the
    'path passed to the procedure.
    dbCurrent = Dir(strSourcePath & "\*.mdb")
    
    'Initialize the FileSystemObject.
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    'Count the files in the folder.
    Set f = fs.GetFolder(strSourcePath)
    
    'Check to see if the folder is empty.
    'If so, exit the function.
    If f.Files.Count = 0 Then Exit Function
    
    'Create in the current folder a subfolder
    'to place the converted files.
    fs.CreateFolder (strSourcePath & "temp-2k")
    
    'Loop through all occurrences of MDB's. Convert
    'each and temporarily place it in the 2k folder,
    'so Access will not try to convert them again.
    'Add -2k to the name of each converted file.
    Do Until dbCurrent = ""
    
        acc = ExecCmd(SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE " _
        & Chr(34) & strSourcePath & "\" & dbCurrent & Chr(34) _
         & " /Convert " & Chr(34) & strSourcePath & "temp-2k\" & _
        Left(dbCurrent, Len(dbCurrent) - 4) & "-2k.mdb" & Chr(34))
        
        dbCurrent = Dir
    Loop
    
    'When done, move the converted files back into the
    'target folder.
    fs.movefile strSourcePath & "temp-2k\*.*", strSourcePath
    
    'After moving all the files, delete the ConvFiles-2k folder
    fs.deletefolder strSourcePath & "temp-2k"
    
    'Check to see if an alert is specified.
    If strSuppressAlert = False Then
        MsgBox "Finished Converting " & Chr(10) _
        & "  files for the folder " & Chr(10) _
        & Chr(10) & "  " & strSourcePath
    End If
End Function

In the same module, type or paste the following code for the ProcessTree function:

Function ProcessTree(strStartFolder As String)
Dim hMod As Integer
Dim fs As Object

Dim pathArray() As String
Dim intDepthCount As Integer
Dim strCurrentFolder As String
Dim strCurrentPath As String
Dim strCurrentLine As String

'Take out leading or trailing spaces.
strStartFolder = Trim(strStartFolder)

'Cannot just specify a backslash.
If strStartFolder = "\" Then
    MsgBox "Please specify a drive letter or unc path."
    Exit Function
End If

'Create a FileSystemObject.
Set fs = CreateObject("Scripting.FileSystemObject")

'Make sure the folder exists.
If Not fs.folderexists(strStartFolder) Then
    MsgBox "The folder " & strStartFolder & " was not found."
    Exit Function
End If

'Root folders need a backslash added to run the Tree command effectively.
If Len(strStartFolder) < 3 And Right(strStartFolder, 1) <> "\" Then
    strStartFolder = strStartFolder & "\"
End If

'Other folders should not have the backslash.
If Len(strStartFolder) > 3 And Right(strStartFolder, 1) = "\" Then
    strStartFolder = Left(strStartFolder, Len(strStartFolder) - 1)
End If

'Run the Tree command at the command prompt. Put the results into 'Treelog.txt.
TaskId = ExecCmd("COMMAND.COM /C Tree " & strStartFolder & " /a > c:\TreeLog.txt")

'Set f to the file Treelog.txt
Set f = fs.GetFile("c:\TreeLog.txt")

'Open Treelog.txt as a text stream
Set ts = f.OpenAsTextStream(1, -2)

'If a root folder with the backslash, take it out to ensure
'proper concatenation later.
If Right(strStartFolder, 1) = "\" Then
    strStartFolder = Left(strStartFolder, Len(strStartFolder) - 1)
End If

'Move to the first valid line.
ts.skipline
ts.skipline

'Start the Array at 0 and add the start folder.
ReDim pathArray(0)
pathArray(0) = strStartFolder

Do While ts.AtEndOfStream <> True
    strCurrentLine = ts.readline
    
    'If there are not subfolders, just process the files
    'in the current folder.
    If strCurrentLine = "No subfolders exist" Then
        ConvertMDBs strStartFolder & "\"
        Exit Do
    
    Else
    
    'Find the three hyphens appearing before each path.
    'From that determine how far to the right each item is.
    'inDepthCount determines where the directory is in the structure.
    intDepthCount = ((InStr(1, strCurrentLine, "---") + 2) / 4) - 1
    
    'Pull only what is right of the extended characters.
    strCurrentFolder = Right(strCurrentLine, Len(strCurrentLine) - _
    InStr(1, strCurrentLine, "---") - 2)
    
    'See if the current folder is the start folder.
    If UBound(pathArray) = 0 Then
        'If so, just set the current path to the
        'first entry in pathArray.
        strCurrentPath = pathArray(0)
        'Just resize the array.
        ReDim Preserve pathArray(intDepthCount + 1)
        
    Else
    
        'Resize the array and add the current folder name.
        ReDim Preserve pathArray(intDepthCount + 1)
        pathArray(intDepthCount + 1) = strCurrentFolder
        
        'From the contents of the array, build the path.
        For i = 0 To UBound(pathArray)
            strCurrentPath = strCurrentPath & "\" & pathArray(i)
        Next i
        
        'Get rid of the extra slash at the beginning of the array.
        strCurrentPath = Right(strCurrentPath, Len(strCurrentPath) - 1)
    End If
    
    'Add a backslash to the end of the path.
    strCurrentPath = strCurrentPath & "\"

    'Convert the databases in the current folder.
    ConvertMDBs strCurrentPath, True
    
    'Clear the current path.
    strCurrentPath = ""
    End If

Loop

'****CleanUp****
'Close the text stream.
ts.Close

'Delete TreeLog.txt.
Kill "c:\TreeLog.txt"

'Show message indicating the files are converted.
MsgBox "Files Converted"

End Function

In the Immediate window, type the following line, and then press ENTER:
```
?ProcessTree("c:\test\")
					
```

While the procedure is running, you may notice some flashing on the screen and your hard disk working. Do not interrupt the process until the activity has stopped and you see the message box indicating that all the files have been converted.