Connection Strings

9106245.doc

DBMS/RDBMS Database Server Connection Strings SQL Server 2000 Connection Strings MS SQL Server 2000 Connection Strings including ODBC, OLE DB and SQL Client Connection Strings. This is the complied and tested collection of Microsoft SQL Server 2000 Connection strings including MS SQL Server 2000 ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an SQL Server instance , Connect via an IP address, SqlConnection (.NET), Standard Security, Trusted Connection String ODBC Connection String for SQL Server 2000 Standard Security Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; ' VB.NET Imports System.Data.Odbc ... Dim oODBCConnection As OdbcConnection Dim sConnString As String = _ "Driver={SQL Server};" & _ "Server=MySQLServerName;" & _ "Database=MyDatabaseName;" & _ "Uid=MyUsername;" & _ "Pwd=MyPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() Trusted connection Driver={SQL Server};Server=myServerAddress;Database=myDataBase;Trusted_Connection=Yes; OLE DB, OleDbConnection (.NET) Connection String for SQL Server 2000

Standard Security

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; ' VB.NET Imports System.Data.OleDb ... Dim oOleDbConnection As OleDbConnection Dim sConnString As String = _ "Provider=sqloledb;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User Id=myUsername;" & _

"Password=myPassword" oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() Trusted connection

Page 1 To 18

9106245.doc Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; Connecting to an SQL Server instance Provider=sqloledb;Data Source=myServerName\theInstanceName;Initial Catalog=myDataBase; Integrated Security=SSPI; Connect via an IP address and Port Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword; SqlConnection (.NET) Connection String for SQL Server 2000 Standard Security Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername; Password=myPassword; Trusted connection Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; Imports System.Data.SqlClient ... Dim oSQLConn As SqlConnection = New SqlConnection() oSQLConn.ConnectionString = _ "Data Source=(local);" & _ "Initial Catalog=myDatabaseName;" & _ "Integrated Security=SSPI" oSQLConn.Open() Connecting to an SQL Server instance Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True; Connect via an IP address and Port Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Page 2 To 18

9106245.doc

SQL Server 2005 Connection Strings MS SQL Server 2005 Connection Strings including ODBC, OLE DB and SQL Client Connection Strings. This is the complied and tested collection of Microsoft SQL Server 2005 Connection strings including MS SQL Server 2005 ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an SQL Server instance , Connect via an IP address, SqlConnection (.NET), Standard Security, Trusted Connection String ODBC Connection String for SQL Server 2005

Standard Security

Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myP assword; ' VB.NET Imports System.Data.Odbc ... Dim oODBCConnection As OdbcConnection Dim sConnString As String = _ "Driver={SQL Server};" & _ "Server=MySQLServerName;" & _ "Database=MyDatabaseName;" & _ "Uid=MyUsername;" & _ "Pwd=MyPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() Trusted connection Driver={SQL Native Client};Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes; Connecting to an SQL Server instance Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Con nection=yes; OLE DB, OleDbConnection (.NET) Connection String for SQL Server 2005

Standard

Security Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUserna me;Pwd=myPassword;

Page 3 To 18

9106245.doc

' VB.NET Imports System.Data.OleDb ... Dim oOleDbConnection As OleDbConnection Dim sConnString As String = _ "Provider=sqloledb;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User Id=myUsername;" & _ "Password=myPassword" oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() Trusted connection Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connec tion=yes; Connecting to an SQL Server instance Provider=SQLNCLI;Server=myServerName\theInstanceName;Database=myDataBase; Trusted_Connection=yes; Standard Security Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword; SqlConnection (.NET) Connection String for SQL Server 2005

Trusted connection Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI; Imports System.Data.SqlClient ... Dim oSQLConn As SqlConnection = New SqlConnection() oSQLConn.ConnectionString = _ "Data Source=(local);" & _ "Initial Catalog=myDatabaseName;" & _ "Integrated Security=SSPI" oSQLConn.Open() Connecting to an SQL Server instance Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection= True; Connect via an IP address and Port Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Page 4 To 18

9106245.doc

MS SQL Server 2005 Compact Edition (CE) Connection Strings

MS SQL Server 2005 CE Connection Strings including ODBC, OLE DB and SQL Client Connection Strings.

This is the complied and tested collection of Microsoft SQL Server 2005 Compact Edition (CE) Connection strings including MS SQL Server 2005 CE ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an SQL Server instance , Connect via an IP address, SqlConnection (.NET), Standard Security, Trusted Connection String Standard Security Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;

ODBC Connection String for SQL Server 2005 CE

' VB.NET Imports System.Data.Odbc ... Dim oODBCConnection As OdbcConnection Dim sConnString As String = _ "Driver={SQL Server};" & _ "Server=MySQLServerName;" & _ "Database=MyDatabaseName;" & _ "Uid=MyUsername;" & _ "Pwd=MyPassword" oODBCConnection = New Odbc.OdbcConnection(sConnString) oODBCConnection.Open() Specifying the maximum database size and buffer size Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf;SSCE:Max Database Size=256;SSCE:Max Buffer Size=1024; Supplying the database password Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=myPath\myData.sdf; SSCE:Database Password='myPassword'; SqlCeConnection (.NET) Connection String for SQL Server 2005 CE

Standard Security

Data Source=MyData.sdf;Persist Security Info=False; ' VB.NET Page 5 To 18

9106245.doc Imports System.Data.OleDb ... Dim oOleDbConnection As OleDbConnection Dim sConnString As String = _ "Provider=sqloledb;" & _ "Data Source=myServerName;" & _ "Initial Catalog=myDatabaseName;" & _ "User Id=myUsername;" & _ "Password=myPassword" oOleDbConnection = New OleDb.OleDbConnection(sConnString) oOleDbConnection.Open() How to specify the location of the SDF file Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssemb ly().GetName().CodeBase) + "\\MyData.sdf;Persist Security Info=False; Encryption enabled Data Source=MyData.sdf;Encrypt Database=True;Password=myPassword;File Mode=shared read;Persist Security Info=False;

Page 6 To 18

9106245.doc

Oracle Connection Strings Oracle Connection Strings including ODBC, OLE DB and Oracle Client Connection Strings.

This is the complied and tested collection of Oracle Database Connection strings including Oracle Database ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an remote Oracle Database , Connect via an IP address, OracleConnection (.NET), Standard Security, Trusted Connection String ODBC Connection String for Oracle

Standard Security

Driver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword; ' VB.NET Imports System.Data.Odbc ... oConn.Open "Driver={Microsoft ODBC for Oracle};" & _ "Dbq=myDBName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" OLE DB, OleDbConnection (.NET) Connection String for Oracle

Standard Security

Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword; ' VB.NET Imports System.Data.OleDb ... oConn.Open "Provider=msdaora;" & _ "Data Source=MyOracleDB;" & _ "User Id=myUsername;" & _ "Password=myPassword" Trusted connection Provider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes; OracleConnection, Oracle Data Provider, ODP.NET for Oracle Database

Standard Security

Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no; Using Connection Pooling

Page 7 To 18

9106245.doc Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2; Windows Authentication Data Source=myOracleDB;User Id=/; Privileged Connection Data Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;

Page 8 To 18

9106245.doc

MySql Connection Strings

MySql Connection Strings including ODBC, OLE DB and SQL Client Connection Strings. This is the complied and tested collection of MySql Connection strings including MySQL ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String. Local Database Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3;

ODBC Connection String for MySql

' VB.NET Imports System.Data.Odbc ... oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _ "Server=myServerName;" & _ "Port=3306;" & _ "Option=16384;" & _ "Stmt=;" & _ "Database=mydatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" Remote Database Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Port=3306;Database=myDataBase;User=myUsernam e; Password=myPassword;Option=3; dim Cn as New Odbc.OdbcConnection() If Cn.State = 1 Then Cn.Close() Cn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=deltas01; DATABASE=survey; UID=root; PWD=123#000;" Cn.Open()

OLE DB, OleDbConnection (.NET) Connection String for MySql

Standard Security

Provider=MySQLProv;Data Source=mydb;User Id=myUsername;Password=myPassword; ' VB.NET

Page 9 To 18

9106245.doc Imports System.Data.OleDb ... oConn.Open "Provider=MySQLProv;" & _ "Data Source=mySQLDB;" & _ "User Id=myUsername;" & _ "Password=myPassword"

Page 10 To 18

9106245.doc

Postgre SQL Connection Strings Postgre SQL Connection Strings including ODBC, OLE DB Connection Strings. This is the complied and tested collection of Postgree Database Connection strings including Postgre SQL ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Note : PgOleDb requires a PQLib of version 7.4 or up and it also requires a backend of version 7.4 or up. Timestamps are only guarenteed to work with backends of version 8.0 and up. ODBC Connection String for Postgre SQL

Standard Security

Driver={PostgreSQL};Server=IP address;Port=5432;Database=myDataBase;Uid=myUsername;Pwd=myPassword; OLE DB, OleDbConnection (.NET) Connection String for Postgre SQL

Standard Security

Provider=PostgreSQL OLE DB Provider;Data Source=myServerAddress;location=myDataBase;User ID=myUsername;password=myPassword;timeout=1000;

Page 11 To 18

9106245.doc

Flat/Data File Connection Strings

MS Excel Connection Strings MS Excel 97, 2000, XP, 2003 Connection Strings including ODBC, OLE DB Connection Strings which can be used to connect MS Excel sheet to your frontend application.

This is the complied and tested collection of Microsoft Excel including

MS Excel 97 -2003 ODBC and OLE DB Connection String. ODBC Connection String for MS Excel

Standard Security

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath; Standard Security Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"; OLE DB Connection String for MS Excel

Dim QueryExcel As String Dim CommExcel As OleDb.OleDbCommand Dim DaExcel As New OleDb.OleDbDataAdapter Dim TableExcel As New DataTable Dim CnExcel As New OleDb.OleDbConnection CnExcel.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & TxtExcelPath.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""" If CnExcel.State = ConnectionState.Closed Then CnExcel.Open() QueryExcel = "Select * from [" & SName & "$]" CommExcel = New OleDb.OleDbCommand(QueryExcel, CnExcel) DaExcel.SelectCommand = CommExcel DaExcel.Fill(TableExcel) CnExcel.Close()

Page 12 To 18

9106245.doc

MS Excel 2007 Connection Strings MS Excel 2007 Connection Strings which connect MS Excel 2007 sheet to the front end application. This is the complied and tested collection of Microsoft Excel 2007 Connection strings including MS Excel 2007 Connection strings which connect. however you can also connect MS Excel 2007 via previous MS Excel 2000 connection strings. Standard Security Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Connection String for MS Excel 2007

Page 13 To 18

9106245.doc

Text File and CSV File Connection Strings Text and CSV file Connection Strings including ODBC and OLE DB Connection Strings. This is the complied and tested collection of Text and CSV (*txt, *.csv) File including Text File nad Comma Separated Vale (CSV) ODBCand OLE DB, Connection String for Visual basic application. ODBC Connection String for text and csv file.

Standard Security

Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt; oConn.Open _ "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "Dbq=c:\somepath\;" & _ "Extensions=asc,csv,tab,txt" Then specify the filename in the SQL statement: oRs.Open "Select * From customer.csv", _ oConn, adOpenStatic, adLockReadOnly, adCmdText Note: If you are using a Tab delimited file, then make sure you create a schema.ini file, and include the "Format=TabDelimited" option. OLE DB, OleDbConnection (.NET) Connection String for text and csv file.

Standard

Security Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited"; oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\;" & _ "Extended Properties=""text;HDR=Yes;FMT=Delimited""" 'Then open a recordset based on a select on the actual file oRs.Open "Select * From MyTextFile.txt", oConn, _ adOpenStatic, adLockReadOnly, adCmdText Fixed length columns Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Fixed";

Page 14 To 18

9106245.doc

MS Access Connection Strings MS Access Connection Strings including ODBC, OLE DB Connection Strings. This is the complied and tested collection of Microsoft Access Connection strings including MS Access ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an MS Access database ODBC Connection String for MS Access

Standard Security

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=; Workgroup Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;SystemDB=C:\mydatabase.mdw; Workgroup Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=; OLE DB, OleDbConnection (.NET) Connection String for MS Access

Standard Security

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=; With database password Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;Jet OLEDB:Database Password=MyDbPassword; oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=c:\somepath\mydb.mdb;" & _ "Jet OLEDB:Database Password=MyDbPassword", _ "myUsername", "myPassword"

Page 15 To 18

9106245.doc

MS Access 2007 Connection Strings MS Access 2007 Connection Strings, which connect MS Excel 2007, sheet to the front-end application. This is the complied and tested collection of Microsoft Access 2007 Connection strings including MS Access 2007 Connection strings, which connect. However you can also connect MS Access 2007 via previous MS Access connection strings.

OLEDB Connection String for MS Access 2007

Standard Security Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False; With Database Password Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;

Page 16 To 18

9106245.doc

Visual FoxPro and FoxPro 2.6 Connection Strings Visual FoxPro Connection Strings including ODBC and OLE DB Connection Strings. This is the complied and tested collection of Microsoft Visual FoxPro and FoxPro 2.6 Connection strings including Foxpro ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Trusted connection String, Connecting to an Visual FoxPro database , Connect via an IP address, Standard Security, Trusted Connection String ODBC Connection String for FoxPro

Database Container

Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=c:\myvfpdb.dbc;Exclusive=No; NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO; oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBC;" & _ "SourceDB=c:\somepath\mySourceDb.dbc;" & _ "Exclusive=No" Free Table directory Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=c:\myvfpdbfolder;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO; oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _ "SourceType=DBF;" & _ "SourceDB=c:\somepath\mySourceDbFolder;" & _ "Exclusive=No" OLE DB, OleDbConnection (.NET) Connection String FoxPro Database container (.DBC) Provider=vfpoledb.1;Data Source=C:\MyDbFolder\MyDbContainer.dbc;Collating Sequence=machine; oConn.Open "Provider=vfpoledb;" & _ "Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _ "Mode=ReadWrite|Share Deny None;" & _ "Collating Sequence=MACHINE;" & _ "Password=''" Free Table directory Provider=vfpoledb.1;Data Source=C:\MyDataDirectory\;Collating Sequence=general;

Page 17 To 18

9106245.doc

DBF and FoxPro Connection Strings FoxPro and DBF Connection Strings including ODBC, OLE DB and SQL Client Connection Strings. This is the complied and tested collection of DBF and FoxPro Connection strings including FoxPro and DBF ODBC Standard Security Connection String, Trusted connection, OLE DB, OleDbConnection (.NET) Standard Security Connection Strings, Connecting to an FoxPro and DBF File Standard Security Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\mydbpath;

ODBC Connection String for FoxPro and DBF

oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _ "DriverID=277;" & _ "Dbq=c:\somepath" Then specify the filename in the SQL statement: oRs.Open "Select * From user.dbf", oConn, , ,adCmdText OLE DB, OleDbConnection (.NET) Connection String for FoxPro and DBF

Standard

Security Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended Properties=dBASE IV;User ID=Admin;Password=;

Page 18 To 18