Connect to Microsoft Access Database (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Database Connectivity (FireDAC)

This topic describes how to connect to Microsoft Access database files.

Supported Versions

The FireDAC native driver supports the Microsoft Access 95, 97, 2000, 2003, 2007, and 2010 databases.

Windows Client Software

FireDAC requires one of the Microsoft Access x86 or x64 ODBC drivers to be installed on the workstation:

  • "Microsoft Access Driver (*.mdb)" x86 ODBC driver version 3 or later (often referred to as the Microsoft JET ODBC Driver) for 95-2003 databases. See details. You can also use one of the alternative drivers provided for non-English languages.
  • "Microsoft Access Driver (*.mdb, *.accdb)" x86 and x64 ODBC driver version 12 or later for 95-2010 databases. See details.

Note: To install an x86 or x64 Microsoft Access runtime on a workstation with an already installed runtime with a different architecture, specify "/passive" in the runtime installer command line. Because Delphi IDE is an x86 application, you should install the x86 Access version on your development workstation.

If the Microsoft Access ODBC driver has not been installed properly, an exception is raised when you try to connect:

[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

If you try to open a DB created with a newer Access version using an older Access driver, an exception is raised:

[FireDAC][Phys][ODBC][Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.

macOS and iOS client software

FireDAC does not support Microsoft Access database connection on Linux, macOS, and iOS.

Driver Linkage

To link the driver:

Connection Definition Parameters

To connect to a Microsoft Access database, most applications require that you specify DriverID and Database (see Defining Connection (FireDAC) for details).

Note: FireDAC supports the password protected databases, but the password length must be equal to or less than 14 characters. With a longer password, an application raises the "Not a valid password" exception.

DriverID=MSAcc

Parameter Description Example value
Database

The path to the MDB file. A path may include path variables.

c:\mydb.mdb
SystemDB

The path to the system database file. A path may include path variables.

c:\mysystem.mdb
ReadOnly Specify True to open a database in read-only mode. False is the default value. True
StringFormat

Defines how to represent String values:

  • Choose -- represent as ftString / ftMemo on non-Unicode Delphi (D2007 and lower) and ftWideString / ftWideMemo on Unicode Delphi (D2009 and later) (default).
  • Unicode -- always represent as ftWideString / ftWideMemo.
  • ANSI -- always represent as ftString / ftMemo.
 Unicode
ODBCAdvanced Allows you to specify any other additional ODBC connection parameter value. The default value is "ExtendedAnsiSQL=1". IMPLICITCOMMITSYNC=NO

Use Cases

  • Open the Microsoft Access database.
DriverID=MSAcc
Database=c:\mydata.mdb
  • Open the Microsoft Access database using the system database.
DriverID=MSAcc
Database=c:\mydata.mdb
SystemDB=c:\system.mdb
User_Name=usr
Password=pwd
  • Open the Microsoft Access password protected database. Note that the password length must be equal to or less than 14 characters.
DriverID=MSAcc
Database=c:\mydata.mdb
Password=pwd

Note: to drop/create, encrypt, compact, and repair the database use the TADMSAccessUtility component.

See Also

Samples

Retrieved from "https://docwiki.embarcadero.com/RADStudio/Athens/e/index.php?title=Connect_to_Microsoft_Access_Database_(FireDAC)&oldid=270596"