Converting an Access 2003 database to 2007 and higher online

Even though it is well past 2019, you still encounter many old Access databases online, such as Access 2000, 2002, and 2003. These all use the .mdb extension. Newer versions of Access use a different file extension: .accdb. This guide explains how to convert Access 2003 to 2007+ online using a simple script.

Please note this is an older post of mine, transferred from itfaq.nl, translated to English and actualized.

Understanding the Scope

Before we proceed, it is important to understand the technical scope of this conversion. The Microsoft Jet Database Engine is the engine behind Access. Essentially, what you want to achieve is updating Jet OLEDB:Engine Type=5 to Jet OLEDB:Engine Type=6, rather than just “upgrading” the software version.

To do this, you need to change the file format: Convert an Access MDB database to the ACCDB file format.

Conversion using Classic ASP

You can convert from the Jet version to the ACCDB file format using a small Classic ASP script.

Prerequisites

• The Microsoft Access Database Engine 2007 (or newer) must be installed on the IIS web server.
• Preferably, use the 2010 or 2016 Redistributable versions for better compatibility.

The Conversion Script

Save the following code as convert.asp and upload it to your website:

<%
Dim objFSO
Dim objEngine
Dim strLckFile
Dim strSrcName
Dim strDstName
Dim strBackup

' Define your paths here
strLckFile = "D:\www\path\to\database\db.ldb"
strSrcName = "D:\www\path\to\database\db.mdb"
strDstName = "D:\www\path\to\database\db.accdb"
strBackup  = "D:\www\path\to\database\db_backup.accdb"

Set objEngine = CreateObject("DAO.DBEngine.120")
Set objFSO = CreateObject("Scripting.FileSystemObject")

If Not (objFSO.FileExists(strLckFile)) Then
    If (objFSO.FileExists(strBackup)) Then
        objFSO.DeleteFile strBackup
    End If
    
    If (objFSO.FileExists(strDstName)) Then
        objFSO.DeleteFile strDstName
    End If
    
    objFSO.CopyFile strSrcName, strBackup
    
    ' dbVersion120 = 128
    objEngine.CompactDatabase strSrcName, strDstName, , 128
End If

Set objFSO = Nothing
Set objEngine = Nothing
%>

Bron: @HansUp op Stack Overflow.

Instructions

1. Modify the four variables: strLckFile, strSrcName, strDstName, and strBackup.
2. Note that strSrcName is your source file (.mdb) and strDstName is the target (.accdb).
3. Run the script by navigating to the URL in your browser.
4. Once executed, a new db.accdb file will be created. You will likely notice that the .accdb file is slightly larger than the original .mdb file.

Found this guide helpful? You can support my independent deep dives into Windows Server and DevOps by donating via PayPal. Every bit of support helps keep saotn.org fast and updated!

Access Version Overview

If you have Microsoft Access installed locally, you can also perform this task by opening the old database and choosing “Save As”. Here is a quick reference for versions and engines:

Office Access Version	Version Number	Jet / ACE Version
Access 2002	10.0	Jet 4.0 SP1
Access 2003	11.0	Jet 4.0 SP1
Access 2007	12.0	ACE 12
Access 2010	14.0	ACE 14
Access 2013	15.0	ACE 14
Access 2016	16.0	ACE 16
Access 2019	17.0	ACE 16+

Are you still using Access databases for your website? It might be time to “get real” and migrate to Microsoft SQL Server or at least MySQL for better performance and scalability.

Bonus: Compacting an Access Database Online with ASP

You can also use Classic ASP to “compact” (shrink) an Access database. This improves the performance of both the database and your ASP application.

The Compacting Script

Use the following code to maintenance your database:

<%
' This script compacts an Access database and redirects upon completion.
Dim LoginName, DatabaseName, strDoneURL
Dim strDataBaseFile, strTempFile, strBackupFile
Dim fso, dbEngine, dbVersion, SourceFile, DuplicateFile, CompactedFile

LoginName = "example.com"
DatabaseName = "database.mdb" ' or database.accdb
dbVersion = "2007" ' Use "2007" or "2003"
strDoneURL = "default.asp"

' File Paths
strDataBaseFile = "z:\Sites\www\" & LoginName & "\database\" & DatabaseName
strTempFile = "z:\Sites\www\" & LoginName & "\database\tmp_" & DatabaseName

Dim Jet_Conn_Partial
If dbVersion = "2007" Then
    Jet_Conn_Partial = "Provider=Microsoft.ACE.OLEDB.12.0;Jet OLEDB:Engine Type=6;Data source="
    Set dbEngine = CreateObject("DAO.DBEngine.120")
    strBackupFile = Replace(strDataBaseFile, "accdb", "accdbBAK")
Else
    Set dbEngine = Server.CreateObject("JRO.JetEngine")
    strBackupFile = Replace(strDataBaseFile, "mdb", "BAK")
End If

Set fso = CreateObject("Scripting.FileSystemObject")

If (fso.FileExists(strDataBaseFile)) Then
    Set SourceFile = fso.GetFile(strDataBaseFile)
    
    If (fso.FileExists(strTempFile)) Then
        fso.DeleteFile(strTempFile)
        Response.Write "Existing temp file removed!<br>"
    End If

    ' Create Backup
    SourceFile.Copy strBackupFile
    
    If dbVersion = "2007" Then
        dbEngine.CompactDatabase strDataBaseFile, strTempFile
    Else
        ' Legacy JRO compacting
        dbEngine.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDataBaseFile, _
                                 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTempFile
    End If

    SourceFile.Delete
    Set CompactedFile = fso.GetFile(strTempFile)
    CompactedFile.Copy strDataBaseFile
    CompactedFile.Delete

    Response.Write strDataBaseFile & " has been compacted."
    ' Response.Redirect strDoneURL
Else
    Response.Write "<b>" & strDataBaseFile & "</b> - File not found!"
End If

Set fso = Nothing
Set dbEngine = Nothing
%>

By keeping your databases compacted, you ensure your legacy web applications remain as responsive as possible.