OLEDB Providers

OLE DB Provider for Microsoft Jet (Access Database)

For 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;" 


OLE DB Provider for Oracle (Microsoft) 

oConn.Open "Provider=msdaora;" & _
           "Data Source=MyOracleDB;" & _ 
           "User Id=myUsername;" & _
           "Password=myPassword"


OLE DB Provider for Oracle (Oracle) 

For Standard Security

oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _ 
           "User Id=myUsername;" & _
           "Password=myPassword"

For a Trusted Connection

oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _ 
           "User Id=/;" & _
           "Password="
' Or

oConn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _ 
           "OSAuthent=1"

Note: "Data Source=" must be set to the appropriate Net8 name which is known to the naming 
method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; 
for Oracle Names, it is the Net8 Service Name. 

OLE DB Provider for SQL Server 

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"

Where: 
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than
   Named Pipes (Q238949)
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.  Q269882 and Q287932
- You can also add "Encrypt=yes" for encryption

OLE DB Provider for Pervasive

oConn.Open "Provider=PervasiveOLEDB;" & _
           "Data Source=C:\PervasiveEB" 

OLE DB Provider for mySQL
 
oConn.Open "Provider=MySQLProv;" & _
           "Data Source=mySQLDB;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"