Erlandsen Data Consulting

ADO connection strings

2009-06-04 ADO 8 951

Below you will find example ADO connection strings that you can use when connecting to some of the most common database types.
OLEDB is by design supposed to be the fastest communications interface to use, and is usually faster than ODBC.

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 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 read only and standard security (OLEDB):

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

Connecting to a MS SQL server using integrated sequrity (OLEDB):

cn.Open "Provider=sqloledb;" & _ 
    "Data Source=servername;" & _
    "Initial Catalog=databasename;" & _
    "Integrated Security=SSPI"

Connecting to a MS SQL server using read only integrated sequrity (OLEDB):

cn.Open "Provider=sqloledb;" & _ 
    "Data Source=servername;" & _
    "Initial Catalog=databasename;" & _
    "ApplicationIntent=ReadOnly;" & _
    "Integrated Security=SSPI"

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

cn.Open "Driver={ODBC Driver 17 for SQL Server};" & _
    "Server=servername;" & _
    "Database=databasename;" & _
    "Uid=userid;Pwd=userpassword"

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

cn.Open "Driver={ODBC Driver 17 for SQL Server};" & _
    "Server=servername;" & _
    "Database=databasename;" & _
    "ApplicationIntent=ReadOnly;" & _
    "Uid=userid;Pwd=userpassword"

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

cn.Open "Driver={ODBC Driver 17 for SQL Server};" & _
    "Server=servername;" & _
    "Database=databasename;" & _
    "Trusted_Connection=yes"

Connecting to a MS SQL server using read only integrated security (ODBC):

cn.Open "Driver={ODBC Driver 17 for SQL Server};" & _
    "Server=servername;" & _
    "Database=databasename;" & _
    "ApplicationIntent=ReadOnly;" & _
    "Trusted_Connection=yes"

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

You also have to define the dbq databasename in the tnsnames.ora file.

Connecting to a MySQL server (OLEDB):

cn.Open "Provider=MySQLProv;" & _
    "Data Source=MYSQLDB;" & _
    "User Id=userid;Password=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 an Excel workbook (OLEDB):

' use "HDR=Yes" if the data source contains a header row
' if "HDR=No" the returned recordset will not include the first row

' Excel 2007 or later (xlsx-files, macros disabled):
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=c:\foldername\workbookname.xlsx;" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES"""

' this will treat the data in the workbook as text
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=c:\foldername\workbookname.xlsx;" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;"""

' Excel 2007 or later (xlsm-files, macros enabled):
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=c:\foldername\workbookname.xlsm;" & _
    "Extended Properties=""Excel 12.0 Macro;HDR=YES"""

' Excel 2007 or later (xlsb-files, binary format):
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=c:\foldername\workbookname.xlsb;" & _
    "Extended Properties=""Excel 12.0;HDR=YES"""

' Excel 97 to 2003 (xls-files):
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\foldername\workbookname.xls;" & _
    "Extended Properties=""Excel 8.0;HDR=Yes"""

' Excel 5 to 95 (xls-files):
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\foldername\workbookname.xls;" & _
    "Extended Properties=""Excel 5.0;HDR=Yes"""

' specify the worksheet name when opening the recordset, 
' add brackets around the sheet name followed by the $-symbol:
rs.Open "select * from [MySheetName$] where [ColumnFieldName] > 100", _
    cn, adOpenForwardOnly, adLockReadOnly, adCmdText

Connecting to an Excel workbook (ODBC):

' Excel 2007 or later:
cn.Open "driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
    "driverid=1046;dbq=c:\foldername\workbookname.xls;" & _
    "defaultdir=c:\foldername"

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

' specify the worksheet name when opening the recordset, 
' add brackets around the sheet name followed by the $-symbol:
rs.Open "select * from [MySheetName$] where [ColumnFieldName] > 100", _
    cn, adOpenForwardOnly, adLockReadOnly, adCmdText

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, adOpenForwardOnly, adLockReadOnly, adCmdText

You will need the Borland Database Engine (BDE) to update dBase files.

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"

The extra space character after *.db in the driver name is necessary.
You will need the Borland Database Engine (BDE) to update Paradox ISAM files.

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 or from ConnectionStrings.com.