Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 304318 - Last Review: June 23, 2005 - Revision: 5.0
How to programmatically convert multiple Access databases in Access 2002
This article was previously published under Q304318
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
This article shows you two methods that you can use to programmatically convert Microsoft Access databases. The first method uses the
ConvertAccessProject method to convert databases to the format that you specify. The second method uses the
Shell function to run Msaccess.exe with the
/convert switch. This second method is embedded in a comment block in the sample code. When you use the second method, the databases will be converted to Access 2000 file format regardless of the
Default File Format that is specified in the
Options dialog box on the
Tools menu.
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.
- Open a new or an existing database.
- On the Insert menu, click Module. Type the following line in the Declarations section if it is not already there:
- 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
- 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")
For more information about the ConvertAccessProject method, in the Visual Basic Editor, click
Microsoft Visual Basic Help on the
Help menu, type
convertaccessproject in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
For additional information about database conversion, click the following article number to view the article in the Microsoft Knowledge Base:
319400Â
(http://kbalertz.com/Feedback.aspx?kbNumber=319400/
)
Conversion white paper is available in the download center
APPLIES TO
- Microsoft Access 2002 Standard Edition
| kbhowto kbconversion kbdta KB304318 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate