By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Visual Basic Tip Of The Week #1

Expert 5K+
P: 8,434
Originally posted by sashi...

Connection string More samples

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;"
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"
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"
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;"
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"
ODBC - Open Database Connectivity

DSN
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "DSN=mySystemDSN;" & _ 
  2.            "Uid=myUsername;" & _ 
  3.            "Pwd=myPassword"
File DSN
Expand|Select|Wrap|Line Numbers
  1. oConn.Open "FILEDSN=c:\somepath\mydb.dsn;" & _ 
  2.            "Uid=myUsername;" & _
  3.            "Pwd=myPassword"
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"
OR

Expand|Select|Wrap|Line Numbers
  1. oConn.Open "Provider=sqloledb;" & _ 
  2.            "Server=myServerName;" & _
  3.            "Database=myDatabaseName;" & _
  4.            "User Id=myUsername;" & _
  5.            "Password=myPassword"
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"
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"
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"
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"
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"
Apr 8 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.