Connecting with MS Access ODBC

The Scriptcase provides two drivers for connecting to the Access database: ODBC and ADO. For more information on how to enable the MS Access ADO driver see our documentation.

In Scriptcase, it is also possible to convert tables from Access database files (.mdb or .accdb) to the databases: MySQL, PostgreSQL, SQLite and SQL Server. For more information, see our documentation for Database Convert.

Requirements

Before proceeding with this documentation, check your PHP architecture. For the drivers to be enabled correctly, files must be downloaded according to the architecture used.

• In your Scriptcase, there is the file info.php, access it by the URL: http://127.0.0.1:8092/scriptcase/info.php, where you will find information regarding architecture in the phpinfo().

• x86 Architecture = 32 Bits
• x64 Architecture = 64 Bits

WARNING: The PHP architecture of Scriptcase and MS Access must be equivalent.

In the automatic installation of Scriptcase, the COM extension is already enabled in PHP, requiring only the following items below to make the connection with Access:

• The MS Access database (.mdb or .accdb) must be stored on the same server/machine as Scriptcase with one or more tables created.
• Access Database Engine installed.
• System DSN ODBC created in the Windows ODBC Manager.

Necessary files:

x64

• Access Database Engine 2010: Click here

x86

• Access Database Engine 2010: Click here

Installing the Microsoft Access Database Engine 2010 Driver

• First we must download Driver Access Database Engine 2010 according to your PHP architecture:

After download, run the file to install the engine:

1 - Click “Next” to continue the installation.

2 - Accept the license terms and agree to continue.

3 - Set the path of the Access Engine installation. You can also use the default path entered by the installer.

4 - Installation complete, click “OK” to finish.

Create an ODBC System DSN

See below for all the tutorials for creating an ODBC data source to connect to Scriptcase.

NOTE: The ODBC must be created on the same server where ScriptCase is installed.

1 - Access the ODBC Data Manager and select according to architecture. We will select the x64 version:

2 -When you log into ODBC Data Source Manager, select the DSN tab and click Add to create your connection to your Access database.

3 - After this, you need to select the controller to connect to Access. Select driver: Microsoft Access Driver (*mdb, *accdb).

4 - Now you need to define the name of the DSN and select the Access database file.

Data Source Name

Here you will define the name of the DSN to be entered in the Scriptcase connection.

Description

In this item, you can create a description for this DSN by distinguishing it from another one created.

Database

In this section, you must select the database file by clicking the Select… button, but you can also create new databases, repair or compress existing files.

5 - After clicking Select, you must choose the path to the Access file.

6 - Now just click “OK” to confirm the creation of DSN, so it will appear in the list of DSN available.

Create a connection in Scriptcase

In this documentation we will show the connection through the ODBC driver by System DSN and in File path to the Access database.

1 - Access any project from your Scriptcase.

2 - Click on the New Connection icon to create a connection.

or access the Database > New Connection menu tab.

After that, a new page will appear with all database connections.

• Select connection “MS Access”.

• Select DBMS driver: MS Access ODBC(Recommended)

In this access connection controller, we can connect in two ways: using the ODBC DSN and entering the database path in “Server/Host (Name or IP)”.

Connect to Scriptcase using the system DSN:

In this type of connection, we must inform the DSN of the system created in step Creating an ODBC data source to make the connection.

Connection name

Define the name of your new connection in Scriptcase.

DBMS driver

Select the access connection controller.

Server/Host (Name or IP)

Enter the system DSN created in Windows ODBC.

Username

Inform the user to connect to the Access database. This option should only be populated if a user is configured as required when connecting to the Access database.

Password

Enter the password to connect to the Access database. This option should be completed only if a password is set as needed when connecting to the Access database.

Connect to Scriptcase using the file path:

In this type of connection, we must enter the full path to the Access database file to make the connection.

Connection name

Define the name of your new connection in Scriptcase.

DBMS driver

Select the access connection controller.

Server/Host (Name or IP)

Enter the full path to the Access database file.

Username

Inform the user to connect to the Access database. This option should only be populated if a user is configured as required when connecting to the Access database.

Password

Enter the password to connect to the Access database. This option should be completed only if a password is set as needed when connecting to the Access database.

Filter

We can configure the initial filtering of the information that the database will bring and display.

Show

In this option, we will define if the connection will show the Tables, Views, System Tables or Procedures databases.

Searches

In this option we can configure the display of specific tables by user. If the elements are empty, the user informed in authentication will see all the data available.

• Tables: We will list the tables that may or may not be displayed.

• Owner: We will inform you of the name of the user who has access to the necessary tables or to the complete database.

• Show: We will define if the reported elements will be displayed or not in this connection.

Advanced

You can configure more advanced settings for the connection.

Decimal separator

This option allows you to define the decimal separator of numerical values. You can choose between dot (.) and comma (,).

Persistent Connection

Enabling this option will close connections when script execution completes.

Use the schema before the table name

This option causes Scriptcase to use the database schema before the table names in its actions. For example:

dbo.Account

• dbo: It is the name of the scheme used.

• Account: This is the name of the table that is used soon after.

Doubts or Connection Problems?

Contact our Support Team in case of connection problems or questions regarding this database.