ADO connection strings

 2009-06-04    Import & Export    6    121

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2015-07-07 08:10:18 (GMT)

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References… and selecting the Microsoft ActiveX Data Objects x.x Object Library.

Amy | 2015-06-25 00:42:08 (GMT)

I am trying to export data from an active Excel worksheet to an Access database. I am getting run-time error '-2147467259 (80004005)'; Automation error Unspecified Error when it hits the code to open the ADODB connection. What am I doing wrong??

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, wb As Workbook

' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=p:\Amenities\Amenities Tracker.mdb"

' cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
' "Data Source=p:\Amenities\Amenities Tracker.mdb;"
' open a recordset

deepak | 2012-05-31 10:16:37 (GMT)

I think this is one of the most vital information for me. And i am glad reading your article. But want to remark on few general things, The web site style is great, the articles is really nice : D. Good job, cheers

Carlos | 2011-09-15 13:40:19 (GMT)

Excelent work.

OPE | 2009-09-17 13:32:37 (GMT)

You just have to use a different connection. If you use the link right above your post and go to Carl Prothman.net you will find examples on how to connect to other database types.

Joop de Bont | 2009-09-17 12:41:17 (GMT)

Thanx for this info it helped me pushing data to Access from Excel, but what I like to known how its done to a DB2 (IBM) Database.

Best regards

Joop