MS Access databases are fully supported. The only feature that is missing in comparison to MS SQL, is the Column Extended Properties. This means that only the Global resource captions&descriptions mechanism can be used as described in Database captions and descriptions.

 

The OLE DB Data Provider is recommended for connecting to MS Access database files. ODBC data provider is not recommended because it does not properly propagate the AllowDBNull value for the columns.

To connect to a database that uses Microsoft Office Access 2003 or earlier, use an OLE DB connection manager, and then for Provider, select Microsoft Jet 4.0 OLE DB Provider.

To connect to a database that uses Microsoft Office Access 2007, use an OLE DB connection manager, and then for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

To connect to data sources that use Access 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider.

For more information see OLE DB Connection Manager 

Access 2007 multivalued fields are not supported. Before generating the Update, Insert and Delete statements in the SmartDataSource configuration wizard, make sure the Select statement does not include multivalued fields.

 

Foreign key meta information

The foreign key meta information is extracted from the system table MSysRelationships. Access system tables are not accessible by default. In order smartDBforms.NET to be able to access them, you have to give read rights on this table to the user which you specified in the connection string. This has to be done in the Access program.

• Open the database
• Go to Tools/Options
• In the Show group select the System objects check box. This will make the MSysRelationships table visible.
• Now go to Tools/Security/User and Group Permissions...
• Select the MSysObjects table and check the box that says "Read Data"

 

In order to edit the table relations you can open the Relationships tool in MS Access by selecting the Tools/Relationships... menu. You can find more information here - Create, edit or delete a relationship.

Only relations which have "Enforce Referential Integrity" enabled are taken into consideration.

Using relative path to the Access file

The connection strings for MS Access databases by default  contain the absolute path to the database file. Using relative path to the Access file is possible only in runtime. In order the SmartDataSource control to work in design time the absolute path should be used.

In order to reference the database file with relative path it should be placed in the /App_Data special folder. In the connection string use the |DataDirectory| to reference the App_Data folder. For example: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|nwind2000.mdb".