Connect to Microsoft Access databases via ADO + ODBC

Related articles and resources

• KB-LJW-DB-101 : Impact of ADO cursor types on an ODBC driver in ASP

Microsoft Access drivers installed on our servers

The native ODBC Drivers and OLE DB Providers allowing you to connect to Microsoft Access databases are installed on our Classic ASP shared hosting plans, and are available in 32-bit and 64-bit:

•  {Microsoft.Jet.OLEDB.4.0}
•  {Microsoft.ACE.OLEDB.12.0}
•  {Microsoft Access Driver (*.mdb)}
•  {Microsoft Access Driver (*.mdb, *.accdb)}

What are Microsoft Access Databases?

Microsoft Access databases are relational databases stored in a .mdb or .accdb file. They rely on the legacy Jet Database Engine (Jet.OLEDB), and now on the Access Database Engine (ACE.OLEDB v. 12/14/16).

Frequently used in Classic ASP development, Access databases are above all a simple relational data source because they can be accessed without a dedicated database server. You can query them via the SQL language, sometimes with a few adaptations and specificities of the SQL dialect for Access .

Technically, Access organizes data into tables, relationships, indexes, and queries, in a way comparable to other DBMSs. Access to your Access databases is typically performed in Classic ASP via ADO (ActiveX Data Objects), using either an OLE DB provider (for example Microsoft.Jet.OLEDB.4.0 or Microsoft.ACE.OLEDB.12.0), or the ODBC Driver provided by Microsoft Microsoft Access Driver (.mdb, .accdb).

Access databases are suitable for sites with intermediate traffic and data volume, because there is no strict separation between engine, storage, and concurrent access. All these roles are encapsulated in a single file: connections, Recordset objects, SQL queries, transactions.

How to manage and administer Access databases

Because Microsoft Access databases are provided as standalone files, they are easy to use. You simply need to place a .mdb or .accdb file in a directory of your website, and your ASP code has the rights to access it. Depending on the dedicated or shared hosting plan you have, you can use an unlimited number of .mdb or .accdb databases.

Access database management is performed in most cases by FTP transfer, and through direct management from pages coded in Classic ASP. The pages intended for the public side of your website are generally meant to read data (with some write cases), while your "Administrator/Backoffice/Manager/Intranet" pages allow you to perform write and update operations on your data as the site operator.

Remote management of your databases from the Microsoft Access application installed on your computer is technically possible, but is generally reliable only from a local network. The conditions of an Access database located on a remote web server are not suited to this technique, except with either significant risks of data corruption, or blocking periods making your database inaccessible for concurrent access from the public website side, or ghost locks and unpredictable response times.

These limitations are inherent to the fact that Microsoft Access is a database engine based on a single file, and not a database server. Access to the databases is therefore possible only with very low connection concurrency, and always at the cost of locking the database. Remote access is therefore not a reliable choice from an operational point of view.

How to access MS Access databases from Classic ASP

Connecting to your Microsoft Access databases from your Classic ASP site is done through a native OLE DB Provider, or through an ODBC Driver provided for this purpose by Microsoft. The differences between OLE DB and ODBC are presented in this section.

The preferred connection method mainly depends on the bitness (32-bit or 64-bit) of your IIS Application Pool. The main selection rule is as follows:

•  {Microsoft.Jet.OLEDB.4.0} :
  Legacy OLE DB provider suitable for 32-bit connections
•  {Microsoft.ACE.OLEDB.12.0} :
  Current OLE DB provider suitable for 64-bit connections
•  {Microsoft Access Driver (*.mdb)} :
  Fallback solution if OLE DB is not suitable, available in 32-bit
•  {Microsoft Access Driver (*.mdb, *.accdb)} :
  Fallback solution if OLE DB is not suitable, available in 64-bit

The following table presents the different methods for accessing your Access databases:

Direct connection without DSN via an ODBC Driver or OLE DB Provider

When you establish a connection to your Microsoft Access database via an ODBC Driver from your ASP code, you are establishing a so-called "DSN-less" connection. In this case, you do not need to specify Provider=MSDASQL (Microsoft OLE DB provider for ODBC) in your connection string: this default OLE DB provider for ODBC Drivers is implicitly used by ADO.

One exception to note: you will probably need to add Provider=MSDASQL; DSN=MyDSN; to your connection string if you establish your connection via a DSN.

Microsoft.Jet.OLEDB.4.0

Microsoft.Jet.OLEDB.4.0 is the OLE DB provider that allows ADO to access the Jet Database Engine , the legacy Microsoft Access engine. It works exclusively with .mdb files and only in 32-bit. It was long favored in Classic ASP for sites running in an IIS Application Pool configured in 32-bit. On a 64-bit Pool, it must be replaced by Microsoft.ACE.OLEDB.12.0.

Jet is still installed on most web hosting servers to ensure compatibility for the very many ASP websites running on 32-bit IIS Application Pools. Its operation is stable on legacy applications. Note, however, that it is no longer maintained and imposes significant constraints in terms of concurrency and reliability. Migration to 64-bit should be preferred.

The following example shows you how to establish a connection to an Access database via the JET OLE DB Provider in 32-bit:

<%
'Establish a connection to the Access database via the JET OLE DB 4 Provider
'Available for 32-bit Application Pools
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=base.mdb;"
%>

If you want to perform operations that require exclusive access to your Access database, add the parameter Mode=Share Exclusive; to your connection string. Make sure not to block your database for an extended period with this method, so that the public pages of your site can connect to the database.

Microsoft.ACE.OLEDB.12.0

Microsoft.ACE.OLEDB.12.0 is the OLE DB provider exposing the Access Database Engine  (ACE), the official successor to Jet. It supports the .accdb and .mdb formats and works in 64-bit (and/or in 32-bit if installed). It is intended to replace Jet for programmatic access to Access via ADO from your Classic ASP pages.

ACE is not simply a renamed version of Jet: it is a different and more recent engine, with more data types and better Unicode compatibility. However, it does not turn Access into a server database: the same structural limitations remain.

The following example shows you how to establish a connection to an Access database via the ACE OLE DB Provider in 64-bit:

<%
'Establish a connection to the Access database via the ACE OLE DB Provider 12 (or 14, or 16)
'Available for 64-bit Application Pools
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.ACE.OLEDB.12.0; DATA SOURCE=base.mdb;"
%>

If your database is password-protected, specify it in your connection string with the parameter Jet OLEDB:Database Password=yourPassword;. In some cases, this method requires you to use a password that does not contain special characters and is no longer than 14 characters. Finally, some databases encrypted with Access 2010 - 2013 may not work; in that case, you must choose Access 2007-type encryption.

Microsoft Access Driver (*.mdb, *.accdb)

The Microsoft Access Driver (.mdb, .accdb) is an ODBC driver historically designed to allow access to Access databases via the ODBC protocol. However, when it is used with ADO, the following layers are stacked: ADO > ODBC > Jet/ACE. Using ODBC in this context is technically inferior in the sense that it adds an unnecessary abstraction layer that slightly degrades performance. From your Classic ASP code, an OLE DB Provider (Jet/ACE) should be preferred because it is more direct and better integrated.

The following example shows you how to establish a connection to an Access database via the ODBC Driver in 32-bit:

<%
'Establish a connection to the Access database via the ODBC driver.
'Available for 32-bit Application Pools.
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=base.mdb; Uid=Admin; Pwd=;"
%>

The following example shows you how to establish a connection to an Access database via the ODBC Driver in 64-bit:

<%
'Establish a connection to the Access database via the ODBC driver.
'Available for 64-bit Application Pools.
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq=base.accdb; Uid=Admin; Pwd=;"
%>

If you want to perform operations that require exclusive access to your Access database, add the parameter Exclusive=1; to your connection string. Make sure not to block your database for an extended period with this method, so that the public pages of your site can connect to the database.

Connection via a DSN

Connecting to a Microsoft Access database via DSN is possible by using the ODBC Driver, but not the native OLE DB Provider.

If you have a shared hosting plan provided by Le Juste Web, you can connect to an Access database via a DSN by proceeding as follows:

1. Log in to Plesk
2. Go to the “Dashboard” tab, then click “ODBC Data Sources”
3. Click the “Add ODBC DSN” button
4. Enter the authentication information for your Access database as well as the parameters related to this ODBC connection.
5. In your ASP code, you can now connect to this ODBC DSN using one of the following methods:
   ASP CLASSIC

   <%
   'Establish a connection to the Access database via a DSN
   'Available for 32-bit and 64-bit Application Pools
   Dim objConn
   Set objConn = Server.CreateObject("ADODB.Connection")
   objConn.CursorLocation = 3 'adUseClient (cf. "adovbs.inc")
   
   'Method 1
   objConn.ConnectionString = "DSN=DSNname;Uid=Username;Pwd=Password"
   objConn.Open
   
   ''Method 2 (alternative)
   'objConn.Open "DSNname", "Username", "Password"
   %>

   
   

In our shared hosting plans, you can at any time adjust the bitness of the Application Pool in Plesk in the « Hosting & DNS > Dedicated IIS Application Pool for Website » tab on the « Enable 32-bit applications » option.

Tips and special cases with Microsoft Access

Microsoft Access is a simple system that offers quick and low-cost implementation, making it a natural and suitable choice in many cases for Classic ASP sites. However, because it is a serverless file-based database system, certain structural limitations important for use on your website must be taken into account when using it.

Concurrency management is rudimentary, performance can drop during simultaneous access, and the risk of file corruption increases under heavy loads or numerous queries. The SQL dialect used by Jet/ACE differs from strict SQL standard, which can make writing advanced queries more complicated and reduce database portability.

If you encounter any difficulty, do not hesitate to contact us.

Database corruption & Backups

The risks of data corruption in an Access database are real. Access databases rely on a single file (.mdb / .accdb) accessed directly by the Jet or ACE engine. This architecture exposes the database to corruption in the event of an interrupted write operation, such as an abrupt IIS server shutdown, an application crash, a network outage, or poorly managed concurrent locking, or when they are used on sites that receive sometimes substantial traffic, or a very large number of queries over short periods. The more frequent and simultaneous the writes are, the greater the risk. Corruption of your database may be partial (some tables or indexes unreadable), or total, making the database unusable.

Even a "well-designed" database can become corrupted: certain factors should alert you to imminent corruption problems:

•  Multiple concurrent write accesses
•  Large databases or databases close to their 2 GB limit
•  No regular compacting
•  ASP code that does not properly close connections and recordsets

Regular backups are essential. They are indeed the only reliable way to recover data after severe corruption of your database. Keep several historical backups over a retention period (for example 1 week or 1 month) long enough to allow you to identify the corruption problem and restore the dataset that suits you.

The repair tools built into the Microsoft Access application can sometimes restore a database, but without any guarantee of data integrity. The backups you make are not only useful for data corruption, but also for human errors that may occur when you code or work on your database (e.g. accidental deletion, incorrect update). Keep in mind that "making backups from time to time" or only before a risky operation is insufficient.

Follow the following backup best practices:

•  Back up automatically and regularly (daily at minimum)
•  Back up offline (when the database is closed + with exclusive access)
•  Keep several versions (rotation)
•  Periodically test the viability of your backed-up databases

Default cursor

ADO never hands control to the default cursor of the ODBC Driver, and specifies at least 0=adOpenForwardOnly. This concretely means that even if you do not specify the rs.CursorType property, the default cursor of the Microsoft Access ODBC Driver does not apply.

Please refer to our article KB-LJW-DB-101 on this topic.

Maximum size of 2 GB

A Microsoft Access database theoretically has a maximum size of 2 GB per file (.mdb or .accdb). This limit includes all data, but also indexes, system objects, temporary tables, and internal unused space.

In practice, an Access database becomes unstable well before 2 GB. Performance deteriorates sharply from 1 ~ 1.2 GB, especially when frequent writes are involved. The realistic operational limit is therefore closer to ~1 GB. Operations such as complex queries, mass updates, or index creation require temporary space, which can cause errors even if the final size of your database remains below 2 GB.

A few points frequently misunderstood about Access databases:

•  Deleting data does not reduce the file size without subsequent compacting.
•  OLE Object / Attachment type fields consume space very quickly. Store this binary data in files on disk instead.
•  Temporary tables and sorting queries can fail due to lack of internal space, without the disk being full.
•  The Jet/ACE engine does not distribute data across multiple files. You only have a single file per Access database.

This architectural limitation inherent to Microsoft Access databases quickly makes them unsuitable for large or continuously growing databases. Some of the strategies most frequently used to work around this size problem are for example:

•  Split data across several databases (manual partitioning). This quickly has the drawback of requiring you to manually manage several database files, which increases the risk of confusion.
•  Regularly archive old data into other "cold" storage databases
•  Migrate to a real database server (for example MariaDB or SQL Server) when the size becomes structurally critical

Compacting and maintenance

Over time, the size of your Access database grows. With each data modification, empty space is left in the database and is not purged. Compacting a Microsoft Access database is a maintenance operation that fully rewrites the database file (.mdb or .accdb). It consists of removing the space left by deleted or modified records, and reorganizing data pages and rebuilding certain indexes.

Compacting is not an optional rescue operation, but rather a practice that you must integrate into the lifecycle of your ASP application. Note, however, that compacting will not correct poor architecture and does not improve concurrency management, as the latter is inherent to the engine itself. The reasons that should lead you to systematically compact your database regularly are the following:

•  Reduction of the file size
•  Improved performance (queries, indexes)
•  Reduced risk of corruption
•  Restoration of internal consistency after many write operations of the INSERT, UPDATE, DELETE type

In practice, the Jet or ACE engine creates a new internal copy of the database, copies only the valid data into it, then replaces the original file. Disk space is thus recovered and the logical structure is re-optimized. The compacting operation opens the database in exclusive access mode, which makes the database inaccessible during this operation. On large databases, this can block it for several minutes, especially if no compacting has been performed recently. Therefore, always schedule this compacting outside periods of use.

We recommend compacting your Microsoft Access database:

•  For applications with few changes: every month
•  For applications with frequent writes: every week
•  For very active applications: more frequently and after heavy processing

You can perform this compacting manually or automatically via a script:

•  Via Microsoft Access: Via the "Database Tools > Compact and Repair" menu
•  Via a scheduled ASP or VBScript script: Use the Jet or ACE engine to compact the database

The following source code examples (VBScript or ASP) show you how to compact your Access database regularly:

•  If you are using a 64-bit IIS Application Pool:
  
  ASP CLASSIC

  <%
  'Force a numerical value of X digits, prefixed by Zeros (e.g. 5 => 05)
  Function Num2Digits(data, numdigits)
     Num2Digits = Right(String(numdigits, "0") & CStr(data), 2)
  End Function 'Num2Digits
  
  'Move/rename a file
  Sub MoveRenameFile(sSrc, sDest)
     Dim fso, f
     Set fso = Server.CreateObject("Scripting.FileSystemObject")
        fso.MoveFile sSrc, sDest
     Set fso = Nothing : fso = Empty
  End Sub 'MoveRenameFile
  
  'Define path to the source Access database
  '(UNC paths are allowed but not recommended with Access => risks of corruption)
  Dim sFilePathFrom : sFilePathFrom = Server.MapPath("/data/base.accdb")
  
  Dim sDateTimeStamp : sDateTimeStamp = "_" & Year(Now()) & Num2Digits(Month(Now()),2) & Num2Digits(Day(Now()),2) & "_" & Num2Digits(Hour(Now()),2) & Num2Digits(Minute(Now()),2) & Num2Digits(Second(Now()),2)
  
  'Define the path to the compacted Access database
  '(UNC paths are allowed but not recommended with Access => risks of corruption)
  Dim sFilePathTo : sFilePathTo = Server.MapPath("/data/compact_database" & sDateTimeStamp & ".accdb")
  
  'Define connection strings
  Dim sdbConnFrom, sdbConnTo
  sdbConnFrom = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sFilePathFrom
  sdbConnTo   = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & sFilePathTo
  
  'Enable error handling
  Err.Clear
  On Error Resume Next
  
  'Compact Access database from "Data Access Objects" (64-bits)
  Dim oDAO
  Set oDAO = Server.CreateObject("DAO.DBEngine.120")
  oDAO.CompactDatabase sdbConnFrom, sdbConnTo
  
  if (Err.Number <> 0) then
     Response.Write "Error while compacting """ & sFilePathFrom """ : " & Err.Number & " - " & Err.Description
  else
     'Rename the compacted base to Production
     MoveRenameFile sFilePathTo, sFilePathFrom
  end if
  
  'Disable error handling
  Err.Clear
  On Error Goto 0
  %>

  
  
•  If you are using a 32-bit IIS Application Pool:
  
  ASP CLASSIC

  <%
  'Force a numerical value of X digits, prefixed by Zeros (e.g. 5 => 05)
  Function Num2Digits(data, numdigits)
     Num2Digits = Right(String(numdigits, "0") & CStr(data), 2)
  End Function 'Num2Digits
  
  'Move/rename a file
  Sub MoveRenameFile(sSrc, sDest)
     Dim fso, f
     Set fso = Server.CreateObject("Scripting.FileSystemObject")
        fso.MoveFile sSrc, sDest
     Set fso = Nothing : fso = Empty
  End Sub 'MoveRenameFile
  
  'Define path to the source Access database
  '(UNC paths are allowed but not recommended with Access => risks of corruption)
  Dim sFilePathFrom : sFilePathFrom = Server.MapPath("/data/base.mdb")
  
  Dim sDateTimeStamp : sDateTimeStamp = "_" & Year(Now()) & Num2Digits(Month(Now()),2) & Num2Digits(Day(Now()),2) & "_" & Num2Digits(Hour(Now()),2) & Num2Digits(Minute(Now()),2) & Num2Digits(Second(Now()),2)
  
  'Define the path to the compacted Access database
  '(UNC paths are allowed but not recommended with Access => risks of corruption)
  Dim sFilePathTo : sFilePathTo = Server.MapPath("/data/compact_database" & sDateTimeStamp & ".mdb")
  
  'Define connection strings
  Dim sdbConnFrom, sdbConnTo
  sdbConnFrom = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sFilePathFrom
  sdbConnTo   = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & sFilePathTo
  
  'Enable error handling
  Err.Clear
  On Error Resume Next
  
  'Compact Access database from "Jet and Replication Objects" (32-bits)
  Dim oJRO
  Set oJRO = Server.CreateObject("JRO.JetEngine")
  oJRO.CompactDatabase sdbConnFrom, sdbConnTo
  
  ''OR
  ''Compact Access database from "Data Access Objects" (32-bits)
  'Dim oDAO
  'Set oDAO = Server.CreateObject("DAO.DBEngine.36")
  'oDAO.CompactDatabase sdbConnFrom, sdbConnTo
  
  if (Err.Number <> 0) then
     Response.Write "Error while compacting """ & sFilePathFrom """ : " & Err.Number & " - " & Err.Description
  else
     'Rename the compacted base to Production
     MoveRenameFile sFilePathTo, sFilePathFrom
  end if
  
  'Disable error handling
  Err.Clear
  On Error Goto 0
  %>

  
  

  The version of DAO.DBEngine to instantiate depends on the version of the ACE OLE DB Provider installed on the server. Our shared hosting plans provide version 12.

   Click to sort columns

  Engine	OLE DB Provider	DAO DBEngine
  Jet 4.0	Microsoft.Jet.OLEDB.4.0	DAO.DBEngine.36
  ACE 2007	Microsoft.ACE.OLEDB.12.0	DAO.DBEngine.120
  ACE 2010	Microsoft.ACE.OLEDB.14.0	DAO.DBEngine.140
  ACE 2016+	Microsoft.ACE.OLEDB.16.0	DAO.DBEngine.160

If a connection is still open (*.ldb or *.laccdb file still present) compacting will fail and a COM+ error will be raised. You should therefore preferably implement this source code in a loop that tests exclusive access with Set dbe = Server.CreateObject("DAO.DBEngine.36") : Set db = dbe.OpenDatabase("C:\data\base.mdb", True) then perform a 10-second pause/sleep before the next attempt, or schedule your task at a time of day with the least activity on your database. Other advanced strategies involve stopping the IIS Application Pool from VBScript, for example. However, this requires elevated access rights on the server. Contact us if you need such techniques.

Locking management (*.ldb and *.laccdb files)

.ldb and .laccdb files are locking files used by Microsoft Access to manage concurrent access to the database and are therefore inseparable from its internal operation. When a user opens a database via the Jet/ACE engine, a .ldb or .laccdb file is automatically created by Microsoft Access. The main role of this file is to manage locks (locking), identify connected users, and prevent incompatible concurrent writes. This file does not contain data as such, is created in the same folder as the database, and disappears when the last user closes the database. Most other database engines manage this concurrent access in RAM. The fact that Microsoft Access manages this in a file is de facto a bottleneck: each access to the database causes disk access, which happens to be the slowest type of memory.

Locking in Access works by 2 KB or 4 KB pages depending on the version, and not by row unlike other server-based database systems, such as MariaDB. The consequences are that a single modification can lock several records, but also that performance can quickly drop, and finally that frequent conflicts can occur.

During a crash of the Application Pool running your ASP application, this lock file may remain "orphaned". Its presence then indicates an incorrect shutdown, and prevents exclusive access. It also typically prevents you from writing/overwriting the database file via FTP, because it is considered "locked".

Access is not designed for real concurrency. This lock file is a workaround to simulate it under low-traffic conditions, but in reality constitutes a very coarse lock that is extremely sensitive to network interruptions and has almost no fault tolerance.

If you need to delete a residual .ldb or .laccdb file:

•  Never delete it while the database is open
•  Do not copy a database while a .ldb exists
•  If deletion is impossible:
  • On shared hosting: you must recycle the Application Pool from Plesk in the « Hosting & DNS > Dedicated IIS Application Pool for Website » tab, then wait several minutes for file access to be released: either it will disappear by itself, or you will have to (and finally be able to) delete it manually.
  • On a dedicated server: you can directly kill the w3wp.exe process associated with your IIS Application Pool: the file will be released and you will be able to delete it immediately. Access to the database will also be possible, allowing you to overwrite it via FTP if needed.

Very frequent residual and orphaned locking files are signs that the IIS Application Pool has crashed. It is then time to look into your source code or the database schema to understand why. Migration to a more robust database system such as MariaDB should also be considered if you encounter this kind of problem too frequently.

Migration to MariaDB

It may happen that you reach the limits of Microsoft Access in one way or another. At that point, the question arises of migrating to a more robust database system. The solution that might seem the most obvious at first glance would be Microsoft SQL Server. However, this product is extremely different from Access, and genuinely much more complex to master, in addition to having a significant license cost. We provide it only on our dedicated servers for these reasons. A transition to MariaDB is more suitable in most cases.

Migrating from Microsoft Access to MariaDB becomes relevant as soon as your application reveals the following symptoms: reliable non-blocking concurrent writes, sluggish scaling, the need for reliable backups and restores, availability requirements, or the desire to move beyond Access's structural limits (size, locking, risk of corruption).

MariaDB provides a real server engine, robust transactions, and efficient access and backup features, while remaining simple to use when you have experience with Microsoft Access. Concurrency management and hundreds of simultaneous connections or queries are simply incomparable.

The common bias, however, is to believe that a simple engine change will be enough. In reality, this is not just "an export/import", but rather a controlled reworking of the data, queries, and operating process. Migrating a database engine involves a change in the execution model (local file versus server). It therefore impacts the schema, queries, and ADO code. However, once the data has been imported and your queries adjusted, a change of ODBC Driver is very often the final required step.

We master the methodology to follow to perform such a migration smoothly and without disrupting production on your ASP site. It mainly consists of evaluating tables, relationships, indexes, saved queries, validation rules, calculated fields, macros, and finally Jet/ACE-specific SQL queries that will need to be partially rewritten to comply with the SQL standard.

When designing the target schema on MariaDB (types, primary keys, constraints, indexes), divergences with Access must be handled, such as AutoNumber, which becomes AUTO_INCREMENT, Yes/No, which becomes TINYINT(1) or BOOLEAN, or Date/Hour, which becomes DATETIME/TIMESTAMP.

Attachment/OLE fields are already discouraged with Microsoft Access, as in any DBMS. This migration is an opportunity to rethink them, very often by storing binary data in a file on disk. It is also an opportunity to correct common anti-patterns under Access such as missing keys, overly permissive types, or multi-value data; migrating identically would only transfer schema problems.

During such migrations, we are particularly attentive to the SQL-specific syntax and application logic on the ASP side or in saved queries. Jet/ACE and MariaDB do not have the same dialect (e.g. date functions, concatenation, IIf, Nz, LIKE, TOP, parameters, null handling). For each critical query, its equivalent should be written in standard SQL compatible with MariaDB, then the results should be validated on a fixed dataset.

On the Classic ASP side, the Access OLE DB connection strings will need to be replaced by the MariaDB ODBC Driver. Attention should be paid to the change in transactional semantics, especially ADO cursors: what "seemed to work" with Access (implicit locking) must become explicit with rs.CursorType to make your ASP code more reliable.

Finally, the data transfer phase is approached in two stages: extraction from Access to an intermediate format (CSV or TSV), then import into MariaDB with strict control of UTF-8 encodings, date formats, and null values. Integrity checks must be carried out, such as row counts, checksums, row sampling, and constraint verification. If a hard switch from Access to MariaDB is not feasible, plan a dual-write or synchronization phase.

We also take care to plan rollback procedures, by keeping the old Access-compatible queries in the ASP code, and we perform the operational switch in a planned and structured manner.

Finally, remember that whatever the database system, none of them can eliminate problems coming from the ASP code itself or from the schema (non-indexed queries, lack of parameters, risky business logic): all of this falls within the role of the software architect that you are!
(and that we are too!)

Classic ASP hosting

Classic ASP hosting and migration to Windows Server 2025 / 2022 / 2019

Classic ASP development & maintenance

Classic ASP maintenance, support and development for websites and business applications

IIS Audit & configuration tweaking

Microsoft® IIS configuration audit and performance tuning