These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

ADO Connection Strings

Below you will find example ADO connection strings that you can use when connecting to some of the most common database types.

Connecting to any data source using an ODBC DSN (Data Source Name):
You must first create the DSN, this can be done using the "ODBC Data Source Administrator" found in "Control Panel" or "Administrative Tools" on your computer.
Make sure to create a SYSTEM DSN (not a USER DSN) when creating an ASP solution.

  cn.Open "DSN=SystemDataSourceName;" & _ 
  "Uid=userid;Pwd=userpassword"

Connecting to an Access database using standard security (OLEDB):

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\foldername\databasename.mdb;" & _ 
    "User Id=admin;Password="

Connecting to an Access database using standard security (ODBC):

  cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _
    "dbq=c:\foldername\databasename.mdb;uid=admin;pwd="

Connecting to an Access database using a workgroup system database (OLEDB):

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\foldername\databasename.mdb;" & _ 
    "Jet OLEDB:System Database=c:\foldername\systemdatabasename.mdw", _
    "userid", "userpassword"

Connecting to an Access database using a workgroup system database (ODBC):

  cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _
    "dbq=c:\foldername\databasename.mdb;" & _
    "systemdb=c:\foldername\systemdatabasename.mdw;", _
    "userid", "userpassword"

Connecting to a MS SQL server using standard security (OLEDB):

  cn.Open "Provider=sqloledb;" & _ 
    "Data Source=servername;" & _
    "Initial Catalog=databasename;" & _
    "User Id=userid;Password=userpassword"

Connecting to a MS SQL server using standard security (ODBC):

  cn.Open "driver={SQL Server};" & _
    "server=servername;database=databasename;" & _
    "uid=userid;pwd=userpassword"

Connecting to an Oracle database using the current Microsoft provider (OLEDB):

  cn.Open "Provider=msdaora;" & _
      "Data Source=servername.world;" & _ 
      "User Id=userid;Password=userpassword"

Connecting to an Oracle database using the current Oracle provider (OLEDB):

  cn.Open "Provider=OraOLEDB.Oracle;" & _
      "Data Source=servername.world;" & _ 
      "User Id=userid;Password=userpassword"

Connecting to an Oracle database using the current Microsoft driver (ODBC):

  cn.Open "driver={Microsoft ODBC for Oracle};" & _
    "server=servername.world;" & _
    "uid=userid;pwd=userpassword"

Connecting to an Oracle database using the Oracle ODBC driver (ODBC):

  cn.Open "driver={Oracle ODBC Driver};" & _
    "dbq=databasename;" & _
    "uid=userid;pwd=userpassword"

Connecting to a Sybase ASE database using the OLE DB provider (OLEDB):

  cn.Open "Provider=Sybase.ASEOLEDBProvider;" & _
  "Srvr=servername,5000;" & _
  "Catalog=databasename;" & _
  "User Id=userid;Password=userpassword"

Connecting to a Sybase database using the Sybase System 12 driver (ODBC):

  cn.Open "driver={SYBASE ASE ODBC Driver};" & _
    "srvr=servername;" & _
    "uid=userid;pwd=userpassword"

Connecting to a Sybase database using the Sybase System 11 driver (ODBC):

  cn.Open "driver={SYBASE SYSTEM 11};" & _
    "srvr=servername;" & _
    "uid=userid;pwd=userpassword"

Connecting to a MySQL server (OLEDB):

  cn.Open "Provider=MySQLProv;" & _
      "Data Source=MYSQLDB;" & _
      "User Id=userid;Password=userpassword" 

Connecting to an Excel workbook (OLEDB):

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=c:\foldername\workbookname.xls;" & _
      "Extended Properties=""Excel 8.0;HDR=Yes"""

Connecting to an Excel workbook (ODBC):

  cn.Open "driver={Microsoft Excel Driver (*.xls)};" & _
    "driverid=790;dbq=c:\foldername\workbookname.xls;" & _
    "defaultdir=c:\foldername"

Connecting to a dBase database (ODBC):

  cn.Open "driver={Microsoft dBase Driver (*.dbf)};" & _
    "driverid=277;dbq=c:\foldername"
  ' specify the filename when you open the recordset
  rs.Open "select * from tablename.dbf", cn, , , adCmdText

Connecting to a Paradox database (ODBC):

  cn.Open "driver={Microsoft Paradox Driver (*.db )};" & _
    "driverid=538;fil=Paradox 5.X;" & _
    "defaultdir=c:\foldername\;" & _
    "dbq=c:\foldername\;collatingsequence=ASCII"

Connecting to a text file (OLEDB):

  cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=c:\foldername\;" & _ 
    "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
    rs.Open "select * from filename.csv", cn, adOpenStatic, adLockReadOnly, adCmdText

Connecting to a text file (ODBC):

  cn.Open "driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "dbq=c:\foldername\;" & _
    "extensions=asc,csv,tab,txt,log,*."
  rs.Open "select * from filename.csv", cn, adOpenStatic, adLockReadOnly, adCmdText

The format of the text file is determined by using a schema information file. The schema information file, which is always named Schema.ini and always kept in the same folder as the text data source, provides information about the general format of the file, the column name and data type information, and a number of other data characteristics.
A Schema.ini file is always required for accessing fixed-width data.
You should use a Schema.ini file when your text table contains DateTime, Currency, or Decimal data, or any time you want more control over the handling of the data in the table.

Example Schema.ini file for a file named filename.txt containing tab delimited data with column names in the first row:

[filename.txt]
Format=TabDelimited
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI

Example Schema.ini file for a file named filename.txt containing semicolon delimited data with column names in the first row:

[filename.txt]
Format=Delimited(;)
ColNameHeader=True
MaxScanRows=0
CharacterSet=ANSI

Example Schema.ini file for a file named filename.txt containing fixed width data without column names in the first row:

[filename.txt]
Format=FixedLength
ColNameHeader=False
Col1=FieldName1 Char Width 30
Col2=FieldName2 Date Width 15
Col3=FieldName3 Integer Width 15
Col4=FieldName4 Float Width 20
CharacterSet=ANSI

More information about ADO, DAO, RDO, SQL, Oracle and .NET is available from Carl Prothman.net.

Document last updated 2005-10-08 18:48:08