Below is an overview of how to choose the correct database connection string for your situation. This is particularly useful when setting up Ultimate Survey Professional online survey software

OLEDB vs ODBC -- Which should you use?

Given the option, it's a no brainer to use OLEDB over ODBC. ODBC has been

around for years and is based on older and more bug laden technology. Therefore, ALWAYS use OLEDB if your server/host supports it. Microsoft even says it themselves:

When running Microsoft Jet in an IIS environment, it is recommended that you use the native Jet OLE DB Provider in place of the Microsoft Access ODBC driver. The Microsoft Access ODBC driver (Jet ODBC driver) can have stability issues due to the version of Visual Basic for Applications that is invoked because the version is not thread safe. As a result, when multiple concurrent users make requests of a Microsoft Access database, unpredictable results may occur. The native Jet OLE DB Provider includes fixes and enhancements for stability, performance, and thread pooling (including calling a thread-safe version of Visual Basic for Applications).

Now that you've picked which database method you will be using, here is a

list of possible connection strings, based on whether you are using Microsoft Access or SQL Server and OLEDB or ODBC:

SQL Server

   ODBC:

•  Standard Security:

  "Driver={SQL Server};Server=Aron1;Database=pubs;Uid=sa;Pwd=asdasd;"  

•  Trusted connection:

    "Driver={SQL Server};Server=Aron1;Database=pubs;Trusted_Connection=yes;"

•  Prompt for username and password:

    oConn.Properties("Prompt") = adPromptAlways

    oConn.Open "Driver={SQL Server};Server=Aron1;DataBase=pubs;"

  OLEDB:       

•  Standard Security:

  "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;")

•  Trusted Connection:

  "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;"

  (use serverName\instanceName as Data Source to use a specific SQLServer instance, only SQLServer2000)

•  Prompt for username and password:

    oConn.Provider = "sqloledb"

    oConn.Properties("Prompt") = adPromptAlways

    oConn.Open "Data Source=Aron1;Initial Catalog=pubs;" 

•  Connect via an IP address:

    "Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;"

      (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default))

Access:

     ODBC

• Standard Security:

    "Driver={Microsoft Access Driver(*.mdb)};Dbq=\somepath\mydb.mdb;Uid=Admin;Pwd=asdasd;" 

•  Workgroup:

    "Driver={Microsoft Access Driver

     *.mdb)};Dbq=\somepath\mydb.mdb;SystemDB=\somepath\mydb.mdw;","admin",""

    OLEDB

•  Standard security:

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=asdasd;"

   

•  Workgroup (system database):

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet

  OLEDB:System Database=system.mdw;","admin", ""  

•  With password:

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet

  OLEDB:Database Password=MyDbPassword;","admin", ""