Database Connection Strings

[Smokie]

ODBC Driver for Access
For Standard Security:

<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=admin;" & _
"Pwd="</font id="blue">

If you are using a Workgroup (System database):

<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"SystemDB=c:\somepath\mydb.mdw;", _
"myUsername", "myPassword"</font id="blue">

If want to open up the MDB exclusively

<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd=" </font id="blue">

If MDB is located on a Network Share

<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\\myServer\myShare\myPath\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="</font id="blue">

If MDB is located on a remote machine

- Or use an XML Web Service via SOAP Toolkit or ASP.NET
- Or upgrade to SQL Server and use an IP connection string
- Or use an ADO URL with a remote ASP web page
- Or use a MS Remote or RDS connection string


If you don't know the path to the MDB (using ASP)

<<font color="yellow">%</font id="yellow"> <font color="green">' ASP server-side code</font id="green">
<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & Server.MapPath(".") & "\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="</font id="blue">
<font color="yellow">%</font id="yellow">>
This assumes the MDB is in the same directory where the ASP page is running. Also make sure this directory has Write permissions for the user account.


If you don't know the path to the MDB (using VB)

<font color="blue">oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & App.Path & "\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="</font id="blue">
This assumes the MDB is in the same directory where the application is running.


ODBC Driver for Excel
<font color="blue">oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\somepath\mySpreadsheet.xls;" & _
"DefaultDir=c:\somepath" </font id="blue">



ODBC Driver for Lotus Notes
<font color="blue">oConn.Open "Driver={Lotus NotesSQL 3.01 (32-bit) ODBC DRIVER (*.nsf)};" & _
"Server=myServerName;" & _
"Database=mydir\myDbName.nsf;" & _
"Uid=myUsername;" & _
"Pwd=myPassword" & _</font id="blue">



ODBC Driver for MySQL (via MyODBC)
To connect to a local database

<font color="blue">oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"</font id="blue">

To connect to a remote database

<font color="blue">oConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">


ODBC Driver for Oracle (from Microsoft)
For the current Oracle ODBC Driver from Microsoft

<font color="blue">oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">

For the older Oracle ODBC Driver from Microsoft

<font color="blue">oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
"ConnectString=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">



ODBC Driver for Oracle (from Oracle)
<font color="blue">oConn.Open "Driver={Oracle ODBC Driver};" & _
"Dbq=myDBName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">

Where: The DBQ name must be defined in the tnsnames.ora file



ODBC Driver for SQL Server
For Standard Security

<font color="blue">oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">

For Trusted Connection security

<font color="blue">oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=;" & _
"Pwd="</font id="blue">
' Or
<font color="blue">oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Trusted_Connection=yes"</font id="blue">

To Prompt user for username and password

<font color="blue">oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"DataBase=myDatabaseName"</font id="blue">

To connect to SQL Server running on the same computer

<font color="blue">oConn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">

To connect to SQL Server running on a remote computer (via an IP address)

<font color="blue">oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"</font id="blue">
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to use TCP/IP rather than Named
Pipes (Q238949)



ODBC Driver for Text
<font color="blue">oConn.Open _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt" </font id="blue">
Then specify the filename in the SQL statement:

<font color="blue">oRs.Open "Select * From customer.csv", _
oConn, adOpenStatic, adLockReadOnly, adCmdText</font id="blue">

Note: If you are using a Tab delimited file, then make sure you create a schema.ini file, and include the "Format=TabDelimited" option.