470,874 Members | 1,787 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Connection strings

sashi
1,754 Expert 1GB
MsAccess - Connection String

Standard security
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  2.            "Data Source=c:\somepath\myDb.mdb;" 
  3.  
If using a Workgroup (System Database)
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  2.            "Data Source=c:\somepath\mydb.mdb;" & _ 
  3.            "Jet OLEDB:System Database=MySystem.mdw", _
  4.            "myUsername", "myPassword" 
  5.  
If MDB has a database password
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  2.            "Data Source=c:\somepath\mydb.mdb;" & _ 
  3.            "Jet OLEDB:Database Password=MyDbPassword", _
  4.            "myUsername", "myPassword"
  5.  
If want to open up the MDB exclusively
Expand|Select|Wrap|Line Numbers
  1. oConn.Mode = adModeShareExclusive
  2. oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  3.            "Data Source=c:\somepath\myDb.mdb;"  
  4.  
If MDB is located on a network share
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  2.            "Data Source=\\myServer\myShare\myPath\myDb.mdb"
  3.  
ODBC - Open Database Connectivity

DSN
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "DSN=mySystemDSN;" & _ 
  2.            "Uid=myUsername;" & _ 
  3.            "Pwd=myPassword"
  4.  
File DSN
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _ 
  2.            "Uid=myUsername;" & _
  3.            "Pwd=myPassword"
  4.  
MsSQL Server - Connection String

For Standard Security
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _ 
  2.            "Data Source=myServerName;" & _
  3.            "Initial Catalog=myDatabaseName;" & _
  4.            "User Id=myUsername;" & _
  5.            "Password=myPassword"
  6.  
OR

Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _ 
  2.            "Server=myServerName;" & _
  3.            "Database=myDatabaseName;" & _
  4.            "User Id=myUsername;" & _
  5.            "Password=myPassword"
  6.  
For a Trusted Connection
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _
  2.            "Data Source=myServerName;" & _
  3.            "Initial Catalog=myDatabaseName;" & _
  4.            "Integrated Security=SSPI"
  5.  
To connect to a "Named Instance"
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _
  2.            "Data Source=myServerName\myInstanceName;" & _
  3.            "Initial Catalog=myDatabaseName;" & _
  4.            "User Id=myUsername;" & _
  5.            "Password=myPassword"
  6.  
Note: In order to connect to a SQL Server 2000 "named instance", you must have MDAC 2.6 (or greater) installed.

To Prompt user for username and password
Expand|Select|Wrap|Line Numbers
  1. oConn.Provider = "sqloledb"
  2. oConn.Properties("Prompt") = adPromptAlways
  3. oConn.Open "Data Source=myServerName;" & _
  4.            "Initial Catalog=myDatabaseName" 
  5.  
To connect to SQL Server running on the same computer
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _
  2.            "Data Source=(local);" & _
  3.            "Initial Catalog=myDatabaseName;" & _
  4.            "User ID=myUsername;" & _
  5.            "Password=myPassword"
  6.  
To connect to SQL Server running on a remote computer (via an IP address)
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _
  2.            "Network Library=DBMSSOCN;" & _
  3.            "Data Source=xxx.xxx.xxx.xxx,1433;" & _
  4.            "Initial Catalog=myDatabaseName;" & _
  5.            "User ID=myUsername;" & _
  6.            "Password=myPassword"
  7.  
Dec 2 '06 #1
0 12097

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by JWM | last post: by
4 posts views Thread by Matthew Wells | last post: by
1 post views Thread by stewart | last post: by
7 posts views Thread by Gary Brizard | last post: by
3 posts views Thread by Harry Strybos | last post: by
9 posts views Thread by =?Utf-8?B?Vmlua2k=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.