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