MsAccess - Connection String
Standard security
-
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=c:\somepath\myDb.mdb;"
-
If using a Workgroup (System Database)
-
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=c:\somepath\mydb.mdb;" & _
-
"Jet OLEDB:System Database=MySystem.mdw", _
-
"myUsername", "myPassword"
-
If MDB has a database password
-
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=c:\somepath\mydb.mdb;" & _
-
"Jet OLEDB:Database Password=MyDbPassword", _
-
"myUsername", "myPassword"
-
If want to open up the MDB exclusively
-
oConn.Mode = adModeShareExclusive
-
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=c:\somepath\myDb.mdb;"
-
If MDB is located on a network share
-
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
-
"Data Source=\\myServer\myShare\myPath\myDb.mdb"
-
ODBC - Open Database Connectivity
DSN
-
oConn.Open "DSN=mySystemDSN;" & _
-
"Uid=myUsername;" & _
-
"Pwd=myPassword"
-
File DSN
-
oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _
-
"Uid=myUsername;" & _
-
"Pwd=myPassword"
-
MsSQL Server - Connection String
For Standard Security
-
oConn.Open "Provider=sqloledb;" & _
-
"Data Source=myServerName;" & _
-
"Initial Catalog=myDatabaseName;" & _
-
"User Id=myUsername;" & _
-
"Password=myPassword"
-
OR
-
oConn.Open "Provider=sqloledb;" & _
-
"Server=myServerName;" & _
-
"Database=myDatabaseName;" & _
-
"User Id=myUsername;" & _
-
"Password=myPassword"
-
For a Trusted Connection
-
oConn.Open "Provider=sqloledb;" & _
-
"Data Source=myServerName;" & _
-
"Initial Catalog=myDatabaseName;" & _
-
"Integrated Security=SSPI"
-
To connect to a "Named Instance"
-
oConn.Open "Provider=sqloledb;" & _
-
"Data Source=myServerName\myInstanceName;" & _
-
"Initial Catalog=myDatabaseName;" & _
-
"User Id=myUsername;" & _
-
"Password=myPassword"
-
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
-
oConn.Provider = "sqloledb"
-
oConn.Properties("Prompt") = adPromptAlways
-
oConn.Open "Data Source=myServerName;" & _
-
"Initial Catalog=myDatabaseName"
-
To connect to SQL Server running on the same computer
-
oConn.Open "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)
-
oConn.Open "Provider=sqloledb;" & _
-
"Network Library=DBMSSOCN;" & _
-
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
-
"Initial Catalog=myDatabaseName;" & _
-
"User ID=myUsername;" & _
-
"Password=myPassword"
-
