OLE DB Connection String Setup

1. To build a database connection string, click on the Build... button on the right of the connection string field. This will bring up the Windows Build dialog (which may look different to that shown depending on your Windows version).

2. Choose Build… again to bring up the Data Link Properties.

3. Select the OLE DB Provider for ODBC Drivers entry. Then click Next >>

If the Provider you want is not listed, please contact one of the OLE DB vendors.

Select your data source name from the drop down box. If you need to setup a new ODBC source, see setting up an ODBC driver. If you needed to set this up for a specific user, it would be done here. Then click Test Connection to ensure everything is fine.

Note: MySQL users. Ensure you set the Username, Password and Initial Catalog in the System DSN, NOT HERE, otherwise you will get "Catastrophic Failure" messages. This is a known bug in the MySQL driver.

Other databases

How do I connect to a MS Access 2000 database?
Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\MyDatabase.mdb;Persist Security Info=False

How do I connect to a password protected MS Access 2000 database?
Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=XXXXXX;DataSource=C:\MyDatabase.mdb;Persist Security Info=False

What provider should I use for MS Access
For MS Access 97 use Microsoft.Jet.OLEDB.3.51
For MS Access 2000 use Microsoft.Jet.OLEDB.4.0

How do I connect to a dBase database?
Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\MyDatabase.mdb;Extended Properties="dBase 5.0;"

How do I connect to a Paradox database?
If you have an OLE DB driver for Paradox, use DataPipe's Build option to create a connection string as per normal. If not, first setup an ODBC data source using the Paradox driver (System DSN under Control Panel, Administrative Tools) then use DataPipe's Build option to point an OLE DB connection at it e.g.
Provider=MSDASQL.1;Persist Security Info=False;Data Source=MyParadoxODBC

How do I use multiword table / field names (spaces in Table or Field name)?
Enclose multiword names in backquotes:
SELECT `Last Name`, `First Name` FROM `Address Book`

How do I use constant fields in a SQL query?
SELECT ''2002'', 'First Name', Salary FROM Employees