Microsoft KB Archive/245438

= ACC2000: How to Bulk Convert All Databases in a Folder to Access 2000 =

Article ID: 245438

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q245438



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

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how to create the ConvertMDBs function that you can use to convert all the databases in a specific folder to Access 2000 format. It also shows you how to create the ProcessTree function that you can use to convert all Access databases in a folder and in all of the subsequent subfolders.

IMPORTANT: Most Microsoft Access databases will convert to Microsoft Access 2000 with no difficulty. In some rare cases, however, new features can conflict with existing objects and code in the converted database. Before you use the method describe in this article, Microsoft recommends that you read The Microsoft Access 2000 Conversion White Paper to familiarize yourself with issues that you may encounter when you convert your databases to Access 2000 file format. To download The Microsoft Access 2000 Conversion White Paper, please see the following article in the Microsoft Knowledge Base:

237313 ACC2000: Conversion White Paper Available in Download Center

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.



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 </li> 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.</li> Start Access 2000, and then create a new, blank database.</li> Click Modules under Objects, and then click New.</li>  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 </li>  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 </li>  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 </li>  In the Immediate window, type the following line, and then press ENTER: ?ProcessTree("c:\test\") </li></ol>

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.

Keywords: kbinfo KB245438

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.