Database format and compatibility

The purpose of this help topic is to explain the database compatibility issues that might prevent you from working with your database, especially a Microsoft Access database, in Surpac, and to prevent information or link to information that can help you resolve the issue.

.ddb file

To connect to a database in Surpac, you open a .ddb file. The .ddb file is a data source that specifies the name and format of the database and specifies what names to use for fields in Surpac and how those fields map to fields in the database.

Formats

Surpac allows you to connect to several different database formats but the two most commonly-used formats are Microsoft Access databases (either .mdb or .accdb) and SQL Server databases connected using ODBC.

Note: You can use ODBC to connect to many other database formats.

Microsoft Access - .mdb format

Microsoft is phasing out support for the Jet driver, the driver used to connect to .mdb files. This means that it is becoming common for Surpac users to find that they can no longer use a database that connects to an .mdb file.

If you have Microsoft Office 32-bit (any version), and Surpac 32-bit, you can use an .mdb database. Otherwise, if you want to use Microsoft Access, you must use an .accdb file.

Note: Surpac 32-bit provides the Jet driver, which is one of the installation components of Surpac, called OLEDB.

Microsoft Access - .accdb format

You can use Surpac with an .accdb database. When you install Microsoft Access, the necessary database drivers are installed.

Notes:

Microsoft Office 2013 cannot open .mdb files, so if you want to convert an .mdb file to .accdb you must use Microsoft Access 2010.
For Surpac to connect to the .accdb file, the .ddb file must be set to TYPE "access".

A limitations to be aware of is:

If you are running Surpac 64-bit, you must use Office 64-bit. You cannot use Office 32-bit.

Tip: If you are in the situation where you have a 64-bit computer, but you have Office 32-bit (not Office 64-bit), you could install both Surpac 64-bit (for work with block models and other work requiring high memory use) and Surpac 32-bit for database work.

SQL Server

Regardless of whether you have Surpac 32-bit or 64-bit you can connect to a SQL Server database using ODBC.

The help topic Migrating a database from Access to SQL Server explains how you can migrate data to SQL Server and set up a connection to it.

Mapping

Some database problems occur because the .ddb file is incorrectly mapped to the tables and fields in the database. You can try using lower case table names or field names, and using the DB MAPPER function to make sure all mandatory fields are mapped. Sometimes the data type for a field that is stated in the .ddb file is different from the data type specified in the database.