Opening Microsoft Access .MDB databases with Delphi

Copyright © 2000 Ernesto De Spirito

ADO

If you have Delphi 5 Enterprise or Delphi 5 Professional with ADO Express, then you can use an ADOTable and in the ConnectionString property of the associated ADOConnection you can use the property editor to build the right connection string. For example:

Provider=Microsoft.Jet.OLEDB.4.0;
User ID=Admin;
Password=Password;
Data Source=D:\Path\dbname.mdb;
Mode=ReadWrite;
Extended Properties="";
Persist Security Info=False;
Jet OLEDB:System database="";
Jet OLEDB:Registry Path="";
Jet OLEDB:Database Password="";
Jet OLEDB:Engine Type=5;
Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=True;
Jet OLEDB:SFP=False

NOTE: It all goes in the same line.

This would open the database D:\Path\dbname.mdb using the ADO driver for Access databases (Microsoft.Jet.OLEDB.4.0). The username would be Admin without password (the default when you create an Access database). If you have set a password for the database, you should provide it in the Jet OLEDB:Database Password property. If you have set up security, then you should indicate your workgroups .MDW or .MDA file in the Jet OLEDB:System database property.
 

BDE

If like most of us you don't have ADO, you can use the BDE which provides a native driver (MSACCESS). In a Database component, set the following properties:

DatabaseName = any_name (or alias_name)
DriverName   = MSACCESS
LoginPrompt  = False
Params       = PATH=d:\path
               DATABASE NAME=d:\path\filename.mdb
               TRACE MODE=0
               LANGDRIVER=Access General
               USER NAME=Admin
               PASSWORD=your_password
               OPEN/MODE=READ/WRITE
               SQLPASSTHRU MODE=NOT SHARED

Value of the DatabaseName property of a Database object is the one you should use in the DatabaseName property of Table and Query components that represent tables and queries on this database (this is the way to "link" them to the Database object).

BDE+ODBC

In the case of Access databases, the BDE provides a native driver, but there are many database formats for which there insn't a BDE driver, although maybe you can find an ODBC driver. ODBC offers what I would call "compatibility access" (for applications with bare minimum database requirements, or applications that only need to perform import/export operations), but sometimes it's all we have...

Here is an example of how to use an ODBC driver with the BDE to open an Access database:
 

• Create a DSN (Data Source Name) for your database (using the ODBC Data Sources applet in Windows's Control Panel).

  1. Click the "System DSN" or the "User DSN" tab

  2. Click the "Add..." button

  3. Select the "Microsoft Access Driver (*.mdb)" and press ENTER. The "ODBC Microsoft Access Setup" dialog will appear.

  4. Provide a name in the Data Source Name textbox (no spaces and no special characters).

  5. Click the "Select..." button to specify your .MDB database.

  6. If you have set up a security scheme you have to select the "Database" radio button in the "System Database" frame and then click the "System database..." button to specify your .MDW or .MDA workgroups file.

  7. Click the "Advanced..." button if you want to set a default user name and password. This is for low-security access because anybody with access to your machine can see the DSN properties. If you need higher security, you should provide and username and password when you open the database (see below).

  8. Finally click the "OK" button to save your DSN.

• In Delphi set the properties of the TDatabase component:

  1. Set DatabaseName to the name you gave to the DSN.

  2. If you want the user to supply a login name and password, then leave LoginPrompt in True.

  3. If you don't like the standard login dialog (or if you want to provide the user name and password by code) you can set LoginPrompt to False and use you own dialog box (or use a user name and password hard-coded in your application) and set the Params property with the login data:

     USER NAME=your_username
     PASSWORD=your_password

• Associate your TTable or TQuery components to the TDatabase component mentioned above by simply providing the same DSN name in their corresponding DatabaseName property.