ERLANDSEN DATA CONSULTING

Excel & VBA Tips

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"

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

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

Use "Excel 8.0" for workbooks created by Excel 97 or later.
Use "Excel 5.0" for workbooks created by Excel 5 or 95.
Use "HDR=Yes" if the data source contains a header row in the cell range or named range.
If "HDR=No" the returned recordset will include the first row.

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

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.

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

User comments:

Paresh from India wrote (2006-09-27 07:56:15 CET):

Excellent...
Hye All I was just in search of such a marvellous site, which can helpful in any critical conditions... thanks a lot...

Keep developing...
:)

Ole P. from Norway wrote (2006-08-16 22:42:17 CET):

Re: What Driver Should I Use for Foxpro 2.6 Database
Take a look at the link above to Carl Prothman.

Lester wrote (2006-08-16 18:58:34 CET):

What Driver Should I Use for Foxpro 2.6 Database
What Driver Should I use for foxpro 2.6 database?

Greg from Boca Raton, FL wrote (2006-04-06 21:35:03 CET):

Connect to Oracle in Access through Excel
I have an Access database that runs a query from an Oracle server. I want to be able to run this query from Excel and pull in the data. The Oracle Connect pop-up screen comes up and asks for a username and password.
Is there a way to send the username and password without having to type it in?
Thank you

Ole P. from Norway wrote (2005-10-20 11:19:58 CET):

Re: Index file request when importing dbf file
I have never seen this error message before.
You can try this optional approach to opening dBase files.
It it doen't work, you'll have to search the Google Groups to see if you find another solution, since this seems to be a rather common problem.

You will have to create a data source name (DSN) for your dBase files first (e.g. named "dBaseFilesDSN")

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim strConn As String, strTable As String
strConn = "Provider=MSDASQL.1;Persist Security Info=False;DSN=dBaseFilesDSN;UID=;" & _
"SourceDB=C:\FolderName;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

Set cn = New ADODB.Connection
cn.Open strConn
Set rs = New ADODB.Recordset
strTable = "dbfFileNameWithoutTheFileExtension"
rs.Open "select * from " & strTable, cn, adOpenDynamic, adLockReadOnly, adCmdText

Flora wrote (2005-10-20 08:04:29 CET):

Index file request when importing dbf file
Hi, I've made an ADO connection to a dbf file which I would need to import to excel and using it, but unfortunately when I try to select data from the dbf file, it keeps on giving me the error message "Index not found" but the .cdx file had already exist, is there anyway to fix this problem.
Thanks

Gilson Jr. from Piracicaba, Brazil wrote (2005-10-11 19:08:11 CET):

Good information
Congratulations ! Very good !

Ole P. from Norway wrote (2005-06-13 16:29:55 CET):

Re: Very Good Site
Try, it will probably work.

Charles from England Wiltshire wrote (2005-06-13 14:25:58 CET):

Re: Very Good Site
If I use ADOX Can I run procdures that are stored in Access from Excel, I know you can do a lot with it but not sure about running code.
Charles

Ole P. from Norway wrote (2005-06-13 10:17:57 CET):

Re: Very Good Site
You will probably have to modify the VBA code you use in Access before it will run properly in Excel.