Setting up an OLE DB Connection String

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).

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

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 connect to a IBM AS/400 database?Provider=IBMDA400;"Data source=myAS400;User Id=myUsername;Password=myPassword

How do I connect to an Excel Spreadsheet as a database?Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel
Files;Initial Catalog=c:\spreadsheet.xls

Other questions

How can I improve the
performance (like speed up query data retrieval)?Avoid returning too many fields. Performance suffers as a larger number of
fields are returned. For example using "SELECT * FROM TableName" when TableName
has 40 fields, and you really need to modify only 2 or 3 fields.

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