Click here to Skip to main content
15,867,594 members
Articles / Desktop Programming / MFC

ADO Connection Strings

Rate me:
Please Sign up or sign in to vote.
4.92/5 (150 votes)
23 Jul 2002CPOL8 min read 3.3M   703   211
A list of ODBC DSN Connection Strings.

Table of Contents

Overview

Generally, one of the first steps when you are trying to work with databases is open it. You can find several types of those, and each have a different mode of connection. When you try to connect with your database sometimes, you don't know the correct connection string that you must use. It is for that reason I wrote this article. I wanted to compile the connection strings to the majority of known databases...

ODBC DSN Less Connection

ODBC Driver for dBASE

SQL
strConnection = _T("Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;"
                   "Dbq=c:\\DatabasePath;");

Note: You must specify the filename in the SQL statement... For example:

SQL
CString strQuery = _T("Select Name, Address From Clients.dbf");

ODBC Driver for Excel

SQL
strConnection = _T("Driver={Microsoft Excel Driver (*.xls)};DriverId=790;"
    bq=C:\\DatabasePath\\DBSpreadSheet.xls;DefaultDir=c:\\databasepath;");

ODBC Driver for Text

SQL
strConnection = _T("Driver={Microsoft Text Driver (*.txt; *.csv)};"
        "Dbq=C:\\DatabasePath\\;Extensions=asc,csv,tab,txt;");

If you are using tab delimited files, you must create the schema.ini file, and you must inform the Format=TabDelimited option in your connection string.

Note: You must specify the filename in the SQL statement... For example:

SQL
CString strQuery = _T("Select Name, Address From Clients.csv");

Visual FoxPro

If you are using a database container, the connection string is the following:

SQL
strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    ourceType=DBC;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

If you are working without a database container, you must change the SourceType parameter by DBF as in the following connection string:

SQL
strConnection = _T("Driver={Microsoft Visual Foxpro Driver};UID=;"
    "SourceType=DBF;SourceDB=C:\\DatabasePath\\MyDatabase.dbc;Exclusive=No");

ODBC Driver for Access

SQL
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\dbaccess.mdb;Uid=;Pwd=;");

If you are using a Workgroup (System database): you need to inform the SystemDB Path, the User Name and its password. For that, you have two solutions: inform the user and password in the connection string or in the moment of the open operation. For example:

SQL
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;"
        "Uid=Carlos Antollini;Pwd=carlos");

or may be:

SQL
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=C:\\VC Projects\\ADO\\Samples\\AdoTest\\dbTestSecurity.mdb;"
        "SystemDB=C:\\Program Files\\Microsoft Office\\Office\\SYSTEM.mdw;");
if(pDB.Open(strConnection, "DatabaseUser", "DatabasePass"))
{
    DoSomething();
    pDB.Close();
}

If you want to open in Exclusive mode:

SQL
strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\dbaccess.mdb;Exclusive=1;");

ODBC Driver for SQL Server

For Standard security:

SQL
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
        "Trusted_Connection=no;"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

For Trusted Connection security (Microsoft Windows NT integrated security):

SQL
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
     "Database=myDatabaseName;Uid=;Pwd=;");

Also, you can use the parameter Trusted_Connection that indicates that you are using the Microsoft Windows NT Authentication Mode to authorize user access to the SQL Server database. For example:

SQL
strConnection = _T("Driver={SQL Server};Server=MyServerName;"
    "Database=MyDatabaseName;Trusted_Connection=yes;");

If the SQL Server is running on the same computer, you can replace the name of the server by the word (local) like in the following sample:

SQL
strConnection = _T("Driver={SQL Server};Server=(local);"
        "Database=MyDatabaseName;Uid=MyUserName;Pwd=MyPassword;");

If you want to connect with a remote SQL Server, you must inform the address, the port, and the Network Library to use:

The Address parameter must be an IP address and must include the port. The Network parameter can be one of the following:

  • dbnmpntw Win32 Named Pipes
  • dbmssocn Win32 Winsock TCP/IP
  • dbmsspxn Win32 SPX/IPX
  • dbmsvinn Win32 Banyan Vines
  • dbmsrpcn Win32 Multi-Protocol (Windows RPC)

For more information, see Q238949.

SQL
strConnection = _T("Driver={SQL Server};Server=130.120.110.001;"
     "Address=130.120.110.001,1052;Network=dbmssocn;Database=MyDatabaseName;"
     "Uid=myUsername;Pwd=myPassword;");

ODBC Driver for Oracle

For the current Oracle ODBC driver from Microsoft:

SQL
strConnect = _T("Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;"
        "Uid=MyUsername;Pwd=MyPassword;");

For the older Oracle ODBC driver from Microsoft:

SQL
strConnect = _T("Driver={Microsoft ODBC Driver for Oracle};"
     "ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;");

ODBC Driver for MySQL

If you want to connect to a local database, you can use a connection string like the following:

SQL
strConnect = _T("Driver={MySQL ODBC 3.51 Driver};Server=localhost;"
     "Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;");

If you want to connect with a remote database, you need to specify the name of the server or its IP in the Server parameter. If the Port is distinct to 3306 (default port), you must specify it.

SQL
strConnect = _T("Driver={mySQL ODBC 3.51 Driver};Server=MyRemoteHost;"
     "Port=3306;Option=4;Database=MyDatabase;Uid=MyUsername;Pwd=MyPassword;");

The parameter Option can be one or more of the following values:

  • 1 - The client can't handle that MyODBC returns the real width of a column.
  • 2 - The client can't handle that MySQL returns the true value of affected rows. If this flag is set, then MySQL returns 'found rows' instead. One must have MySQL 3.21.14 or newer to get this to work.
  • 4 - Make a debug log in c:\myodbc.log. This is the same as putting MYSQL_DEBUG=d:t:O,c::\myodbc.log in AUTOEXEC.BAT.
  • 8 - Don't set any packet limit for results and parameters.
  • 16 - Don't prompt for questions even if driver would like to prompt.
  • 32 - Enable or disable the dynamic cursor support. This is not allowed in MyODBC 2.50.
  • 64 - Ignore use of database name in 'database.table.column'.
  • 128 - Force use of ODBC manager cursors (experimental).
  • 256 - Disable the use of extended fetch (experimental).
  • 512 - Pad CHAR fields to full column length.
  • 1024 - SQLDescribeCol() will return fully qualified column names.
  • 2048 - Use the compressed server/client protocol.
  • 4096 - Tell server to ignore space after function name and before '(' (needed by PowerBuilder). This will make all function names keywords!
  • 8192 - Connect with named pipes to a MySQLd server running on NT.
  • 16384 - Change LONGLONG columns to INT columns (some applications can't handle LONGLONG).
  • 32768 - Return 'user' as Table_qualifier and Table_owner from SQLTables (experimental).
  • 65536 - Read parameters from the client and ODBC groups from my.cnf.
  • 131072 - Add some extra safety checks (should not be needed but...).

If you want to have multiple options, you should add the above flags! For example: 16 + 1024 = 1030 and use Option= 1030;.

For more information, go to MyODBC Reference Manual.

ODBC Driver for AS400

SQL
strConnect = _T("Driver={Client Access ODBC Driver (32-bit)};System=myAS400;"
      "Uid=myUsername;Pwd=myPassword;");

ODBC Driver for SyBase

SQL
strConnect = _T("Driver={Sybase System 10};Srvr=MyServerName;Uid=MyUsername;"
       "Pwd=myPassword;");

ODBC Driver for Sybase SQL AnyWhere

SQL
strConnect = _T("ODBC;Driver=Sybase SQL Anywhere 5.0;"
    "DefaultDir=C:\\DatabasePath\;Dbf=C:\\SqlAnyWhere50\\MyDatabase.db;"
    "Uid=MyUsername;Pwd=MyPassword;Dsn=\"\";");

DSN Connection

ODBC DSN

SQL
strConnect = _T("DSN=MyDSN;Uid=MyUsername;Pwd=MyPassword;");

OLE DB Provider

OLE DB Provider for SQL Server

For Standard security:

SQL
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "User Id=MyUsername;Password=MyPassword;");

For Trusted Connection security (Microsoft Windows NT integrated security):

SQL
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
        "Initial Catalog=MyDatabaseName;"
        "Integrated Security=SSPI;");

If you want to connect to a "Named Instance" (SQL Server 2000), you must to specify Data Source=Servere Name\Instance Name like in the following example:

SQL
strConnect = _T("Provider=sqloledb;Data Source=MyServerName\MyInstanceName;"
    "Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;");

If you want to connect with a SQL Server running on the same computer, you must specify the keyword (local) in the Data Source like in the following example:

SQL
strConnect = _T("Provider=sqloledb;Data Source=(local);"
        "Initial Catalog=myDatabaseName;"
        "User ID=myUsername;Password=myPassword;");

To connect to SQL Server running on a remote computer (via an IP address):

SQL
strConnect = _T("Provider=sqloledb;Network Library=DBMSSOCN;"
        "Data Source=130.120.110.001,1433;"
        "Initial Catalog=MyDatabaseName;User ID=MyUsername;"
        "Password=MyPassword;");

OLE DB Provider for MySQL (By Todd Smith)

SQL
strConnection = _T("Provider=MySQLProv;Data Source=test");

Where test is the name of MySQL database. Also, you can replace the name of the database by the following connection string: server=localhost;DB=test.

OLE DB Provider for AS400

SQL
strConnect = _T("Provider=IBMDA400;Data source=myAS400;User Id=myUsername;"
     "Password=myPassword;");

For more information, see: Using the OLE DB Provider for AS/400 and VSAM.

OLE DB Provider for Active Directory

SQL
strConnect = _T("Provider=ADSDSOObject;User Id=myUsername;Password=myPassword;");

For more information, see: Microsoft OLE DB Provider for Microsoft Active Directory Service.

OLE DB Provider for DB2

If you are using a TCP/IP connection:

SQL
strConnect = _T("Provider=DB2OLEDB;Network Transport Library=TCPIP;"
        "Network Address=130.120.110.001;"
        "Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;"
        "Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;");

If you are using APPC connection:

SQL
strConnect = _T("Provider=DB2OLEDB;APPC Local LU Alias=MyLocalLUAlias;"
        "APPC Remote LU Alias=MyRemoteLUAlias;Initial Catalog=MyCatalog;"
        "Package Collection=MyPackageCollection;Default Schema=MySchema;"
        "User ID=MyUsername;Password=MyPassword;");

For more information, see: Using the OLE DB Provider for DB2.

OLE DB Provider for Microsoft Jet

  • Connecting to an Access file using the JET OLE DB Provider:

    Using Standard security:

    SQL
    strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
            "Data Source=C:\\DatabasePath\\MmDatabase.mdb;"
            "User Id=admin;Password=;");

    If you are using a Workgroup (System database):

    SQL
    strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
            "Data Source=C:\\DataBasePath\\mydb.mdb;"
            "Jet OLEDB:System Database=MySystem.mdw;");
    pRs.Open(strConnect, "MyUsername", "MyPassword");
  • Connecting to an Excel Spreadsheet using the JET OLE DB Provider:
    SQL
    strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;"
            "Data Source=C:\\DatabasePath\\DBSpreadSheet.xls;"
            "Extended Properties=\"\"Excel 8.0;HDR=Yes;\"\";");

    Note: If "HDR=Yes", the provider will not include the first row of the selection into the recordset. If "HDR=No", the provider will include the first row of the cell range (or named ranged) into the recordset.

    For more information, see: Q278973.

  • Connecting to a Text file using the JET OLE DB Provider:
    SQL
    strConnect = 
            _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DatabasePath\\;"
            "Extended Properties=\"\"text;"
            "HDR=Yes;FMT=Delimited;\"\";");

    Note: You must specify the filename in the SQL statement... For example:

    SQL
    CString strQuery = _T("Select Name, Address From Clients.txt");

    For more information, see: Q262537.

  • Connecting to an Outlook 2000 personal mail box using the JET OLE DB Provider: (By J. Cardinal)
    SQL
    strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Outlook 9.0;"
          "MAPILEVEL=;DATABASE=C:\\Temp\\;")

    Replace c:\temp with any temporary folder. It will create a schema file in that folder when you open it which shows all the fields available. Blank MAPILEVEL indicates top level of folders).

  • Connecting to an Exchange mail box through JET: (By J. Cardinal)
    SQL
    strConnect = _T("Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;"
          "MAPILEVEL=Mailbox - Pat Smith|;DATABASE=C:\\Temp\\;")

    You must replace c:\temp with any temporary folder.

    Replace Pat Smith with the name of the mail box and you must keep vertical pipe character | to indicate top level of folders. Place sub folder after vertical pipe if accessing specific folder.

    Note: You can enter queries against the mail store just like a database... For example:

    SQL
    CString strQuery = _T("SQL "SELECT Contacts.* FROM Contacts;");

    For more information, see: The Jet 4.0 Exchange/Outlook IISAM.

OLE DB Provider for ODBC Databases

If you want to connect with a Microsoft Access database:

SQL
strConnect = _T("Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)};"
        "Dbq=c:\\DatabasePath\\MyDatabase.mdb;Uid=MyUsername;Pwd=MyPassword;");

If you want to connect with a SQL Server database:

SQL
strConnect = _T("Provider=MSDASQL;Driver={SQL Server};Server=MyServerName;"
        "Database=MyDatabaseName;Uid=MyUsername;Pwd=MyPassword;");

If you want to use DSN:

SQL
strConnect = _T("Provider=MSDASQL;PersistSecurityInfo=False;"
        "Trusted_Connection=Yes;"
        "Data Source=MyDSN;catalog=MyDatabase;");

For more information, see: Microsoft OLE DB Provider for ODBC.

OLE DB Provider for OLAP

Microsoft® OLE DB for Online Analytical Processing (OLAP) is a set of objects and interfaces that extends the ability of OLE DB to provide access to multidimensional data stores.

SQL
strConnect = _T("Provider=MSOLAP;Data Source=MyOLAPServerName;"
     "Initial Catalog=MyOLAPDatabaseName;");

Connection using HTTP

This feature enables a client application to connect to an Analysis server through Microsoft® Internet Information Services (IIS) by specifying a URL in the Data Source property in the client application's connection string. This connection method allows PivotTable® Service to tunnel through firewalls or proxy servers to the Analysis server. A special Active Server Pages (ASP) page, Msolap.asp, enables the connection through IIS. The directory in which this file resides must be included as part of the URL when connecting to the server (for example, http://www.myserver.com/myolap/).

Using a URL

SQL
strConnect = _T("Provider=MSOLAP;Data Source=http://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

Using SSL

SQL
strConnect = _T("Provider=MSOLAP;Data Source=https://MyOLAPServerName/;"
     "Initial Catalog=MyOLAPDatabaseName;");

For more information, see: OLE DB for OLAP, Connecting Using HTTP.

OLE DB Provider for Oracle

OLE DB Provider for Oracle (from Microsoft)

The Microsoft OLE DB Provider for Oracle allows ADO to access Oracle databases.

SQL
strConnect = _T("Provider=MSDAORA;Data Source=MyOracleDB;User Id=myUsername;"
     "Password=myPassword;");

For more information, see: Microsoft OLE DB Provider for Oracle.

OLE DB Provider for Oracle (from Oracle)

For Standard security:

SQL
strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
        "User Id=myUsername;Password=myPassword;");

For a Trusted connection:

  • OS Authenticated connect setting user ID to "/":
    SQL
    strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;"
       "User Id=/;Password=;");
  • OS Authenticated connect using OSAuthent:
    SQL
    strConnect = _T("Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;")

    Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name.

For more information, see: Oracle Provider for OLE DB Developer's Guide.

OLE DB Provider for Visual FoxPro

SQL
strConnect = _T("Provider=vfpoledb;"
     "Data Source=C:\\DatabasePath\\MyDatabase.dbc;");

For more information, see: Microsoft OLE DB Provider for Visual FoxPro.

OLE DB Provider for Index Server (By Chris Maunder)

SQL
strConnect = _T("provider=msidxs;Data Source=MyCatalog;");

For more information, see: Microsoft OLE DB Provider for Microsoft Indexing Service.

OLE DB Data Link Connections

Data Link File - UDL

SQL
strConnection = _T("File Name=c:\\DataBasePath\\DatabaseName.udl;");

If you want to create a Data Link File, you can make a new empty text file, change its extension by .udl, then double click over the file, and the operating system calls for you the Data Link app.

[ Data Link Application ]

History

  • 24th July, 2002: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect Citigroup
Argentina Argentina
Carlos Antollini is a software engineer working on Object Oriented, Visual C++, MFC, COM, ATL, ADO, Internet technologies and Business Intelligence.
Carlos is originally from Argentina, he was living for several years in Fort Lauderdale, Florida, working for Citibank. Then he started his own business.
Carlos is the creator of <a href="http://www.piFive.com">piFive</a>[<a target="_blank" title="piFive" href="http://www.piFive.com">^</a>], a family of BI Analytic Platform software, that it deals next to, <a href="http://www.latinsys.com">latinsys</a>[<a target="_blank" title="latinsys" href="http://www.latinsys.com">^</a>], his partner in businesses...
Currently he is sharing his passion for project management and BI at Citigroup.

Comments and Discussions

 
Questionadomd.net connection string problem while connectiong via desktop remote server Pin
sai40116-Sep-16 19:13
sai40116-Sep-16 19:13 
QuestionSQL Connect Pin
Mahdi Nejadsahebi10-Oct-12 21:52
Mahdi Nejadsahebi10-Oct-12 21:52 
GeneralMy vote of 5 Pin
Mahdi Nejadsahebi10-Oct-12 21:47
Mahdi Nejadsahebi10-Oct-12 21:47 
QuestionC++ MFC Oracle Pin
Mahdi Nejadsahebi10-Oct-12 1:59
Mahdi Nejadsahebi10-Oct-12 1:59 
Questionlinkage of access with Visual Studio Pin
lm19927-Oct-12 23:33
lm19927-Oct-12 23:33 
QuestionGetting Error while connecting to TextFile using Oledb Pin
shanawazway7-Aug-12 3:08
shanawazway7-Aug-12 3:08 
QuestionODBC Drive for oracle in linux clint machine Pin
Arun Kumar K S5-Aug-12 18:29
Arun Kumar K S5-Aug-12 18:29 
GeneralMy vote of 5 Pin
Honnappa M S26-Jun-12 22:27
Honnappa M S26-Jun-12 22:27 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey18-Jan-12 19:42
professionalManoj Kumar Choubey18-Jan-12 19:42 
GeneralMy vote of 4 Pin
yichangzyh22-May-11 18:03
yichangzyh22-May-11 18:03 
good
GeneralOutlook 2010 connection string? Pin
T21026-May-11 7:27
T21026-May-11 7:27 
GeneralCon open wait Pin
Ajay Kale New13-Feb-11 22:02
Ajay Kale New13-Feb-11 22:02 
QuestionSQL Server Connection string..Help needed urgently.... Pin
vijay.victory2-Mar-10 20:01
vijay.victory2-Mar-10 20:01 
AnswerRe: SQL Server Connection string..Help needed urgently.... [modified] Pin
Shahriar Nour Khondokar30-Mar-10 6:26
Shahriar Nour Khondokar30-Mar-10 6:26 
GeneralFIELDS NAMES - AS/400 ODBC [modified] Pin
DomenicoGIACCHERO23-Oct-09 0:20
DomenicoGIACCHERO23-Oct-09 0:20 
Generalexecute select query if data available Pin
sukeshtech28-Sep-09 0:38
sukeshtech28-Sep-09 0:38 
GeneralShowCase ODBC Driver for AS400 Pin
Syed Habeeb Ullah Quadri31-Aug-09 1:34
Syed Habeeb Ullah Quadri31-Aug-09 1:34 
Generalproblem with oledb connection...URGENT Pin
Shashikant_200629-Jun-09 20:13
Shashikant_200629-Jun-09 20:13 
GeneralRe: problem with oledb connection...URGENT Pin
rht34116-Oct-09 2:48
rht34116-Oct-09 2:48 
GeneralODBC Connection String for Excel 2007 with Macros Pin
kjgamble7-Nov-08 6:24
kjgamble7-Nov-08 6:24 
QuestionConnectionstring Pin
Thao Nam21-Oct-08 16:53
Thao Nam21-Oct-08 16:53 
Questionhow can you use this to connect access database? Pin
yulin111-Oct-08 16:43
yulin111-Oct-08 16:43 
QuestionNeed Help Urgently Pin
Pavlikas23-Sep-08 2:55
Pavlikas23-Sep-08 2:55 
GeneralA simple method to get DB connection string Pin
paul100016-Sep-08 22:05
paul100016-Sep-08 22:05 
GeneralRe: A simple method to get DB connection string Pin
SandraS16-Mar-10 5:43
SandraS16-Mar-10 5:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.