Maintain Database Connections

Parent Previous Next

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?




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.




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