Maintain Database Connections

Local System Databases

Local System Databases are primarily used to hold tables of data created by Transfer Data from SAP.

An unlimited number of Local System Databases can be created to hold data used by MDA Workbench. These are Microsoft Access databases, which means that data transferred to them may also be queried, reported, and otherwise analyzed using standard Microsoft Office functionality.

Only one Local System Database may be Active. The last chosen Local System Database is automatically made Active when MDA Workbench is started.

The connection to a Local System Database is managed by MDA Workbench; it is not necessary to maintain the connection details using the Data Sources - Database button on the External Data ribbon tab.

What are Local System Databases used for?

Transfer Data from SAP to a Local System Database.
Transfer Data from Load-Ready Files to a Local System Database.
Browse data in Local System Database using a Database Browser window.
Export data from Local System Database: to Excel or to tab-delimited text files.
Use the data in a Local System Database to Validate Sample Data.
Use the data in a Local System Database to Validate Load-Ready Files.

Manage Local System Database from the External Data ribbon:

To choose an existing Local System Database, click System Database - Choose button.

To create a new Local System Database, click System Database - New button.

The full path and file name of the active Local System Database is shown as a tool tip when hovering the mouse pointer over the System Database - Choose button on the External Data ribbon.

Manage Local System Database from the application Status Bar

The main application window includes a Status Bar that displays the file name of the active Local System Database.

Hovering over the displayed file name displays the full path and file name of the active Local System Database as a tool tip.
Double clicking the displayed file name enables Choose a Local System Database.

The chosen database immediately becomes the active Local System Database.

Right-clicking the displayed file name displays a context sensitive menu with options to Choose and Create a Local System Database.

A chosen database immediately becomes the active Local System Database.
A created database immediately becomes the active Local System Database.

External Databases

Connections for databases other than Local System Databases may be created and maintained using the Data Sources - Database button on the External Data ribbon tab.

Such databases would typically be Staging Databases, and connection details are maintained so that the database is available as an option for browsing when using a Database Browser window.

Click the Save button on the toolbar after creating or maintaining a database connection. Otherwise the changes will not be saved.

The objective in creating or maintaining the database connection is is create the correct connection string. The easiest way to accomplish this when connecting to a SQL Server is to set the Configuration Mode to Auto and then maintain the other properties. As each property is maintained, MDA Workbench adjusts the connection string accordingly.

When the properties are maintained, the Test icon can be click to confirm that the database connection works.

The following properties are available:

Property	Usage
Name	This is the name that will be displayed in the drop down list when using a Database Browser window.
Description
Configuration Mode	Choose Auto and let MDA Workbench create the Connection String.
Database Platform	Unknown Microsoft Access 2007 SQL Server 2008 (use this option for all versions of Microsoft SQL Server)
Security Mode	Unknown Windows User Name and Password
Connection String	A database connection string.
Provider	Unknown SQL OLEDB Microsoft Jet OLDDB 4.0
Server	The server name or host name of the database server.
Database	The name of the database.
User Name	When using Security Mode of User Name and Password (e.g. using SQL Server Authentication) then this is the User Name that will be supplied to the database server.
Password	When using Security Mode of User Name and Password (e.g. using SQL Server Authentication) then this is the Password that will be supplied to the database server.
Connection Timeout	The time, in seconds, that the program will wait when establishing a connection to the database.

Example settings for a SQL Server database named SAP_STAGING_DATABASE, using Windows Authentication.

Property	Example Value
Name	SQL01
Description	SQL01 Staging Database
Configuration Mode	Choose Auto and let MDA Workbench create the Connection String.
Database Platform	SQL Server 2008
Security Mode	Windows
Connection String	Do not maintain this property when using Configuration Mode of Auto. MDA Workbench will create the connection string as follows as other properties are maintained: Provider=SQLOLEDB;Data Source=SQL01;Initial Catalog=SAP_STAGING_DATABASE;Integrated Security=SSPI;Connect Timeout=10;
Provider	SQL OLEDB
Server	SQL01
Database	SAP_STAGING_DATABASE
User Name
Password
Connection Timeout	10