For Standard Security:
oConn.Open "Driver={Microsoft Access" & _ "Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=admin;" & _
"Pwd="
If you are using a Workgroup (System database):
oConn.Open "Driver={Microsoft Access" & _ "Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"SystemDB=c:\somepath\mydb.mdw;", _
"myUsername", "myPassword"
If want to open up the MDB exclusively
oConn.Open "Driver={Microsoft Access" & _ " Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Exclusive=1;" & _
"Uid=admin;" & _
"Pwd="
If MDB is located on a Network Share
oConn.Open "Driver={Microsoft Access" & _ " Driver (*.mdb)};" & _
"Dbq=\\\myPath\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="
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)
<% ' ASP server-side code
oConn.Open "Driver={Microsoft Access" & _ " Driver (*.mdb)};" & _
"Dbq=" & Server.MapPath(".") & " \myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="
%>
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)
oConn.Open "Driver={Microsoft Access" & _ " Driver (*.mdb)};" & _
"Dbq=" & App.Path & "\myDb.mdb;" & _
"Uid=admin;" & _
"Pwd="
This assumes the MDB is in the same directory
where the application is running.
For more information, see:
Microsoft
Access Driver Programming Considerations
To view Microsoft KB articles related to Microsoft
Access Driver, click
here
oConn.Open "Driver={Client Access" & _ " ODBC Driver (32-bit)};" & _
"System=myAS400;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see:
A
Fast Path to AS/400 Client/Server
oConn.Open "Driver={Microsoft " & _ " dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=c:\somepath"
Then specify the filename in the SQL statement:
oRs.Open "Select * From user.dbf", oConn, , ,adCmdText
Note: MDAC 2.1 (or
greater)
requires the Borland Database Engine
(BDE)
to
update
dBase DBF files. (Q238431).
For more information, see:
dBASE
Driver Programming Considerations
To view Microsoft KB articles related to Microsoft
dBASE Driver, click
here
oConn.Open "Driver={Microsoft" & _ " Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=c:\somepath\mySpreadsheet.xls;" & _
"DefaultDir=c:\somepath"
For more information, see:
Microsoft
Excel Driver Programming Considerations To view Microsoft KB articles related to Microsoft
Excel Driver, click
here
If using INFORMIX 3.30 ODBC Driver
oConn.Open "Dsn='';" & _
"Driver={INFORMIX 3.30 32 BIT};" & _
"Host=myHostname;" & _
"Server=myServerName;" & _
"Service=myServiceName;" & _
"Protocol=olsoctcp;" & _
"Database=myDbName;" & _
"UID=myUsername;" & _
"PWD=myPassword" & _
' Or oConn.Open "Dsn=myDsn;" & _
"Host=myHostname;" & _
"Server=myServerName;" & _
"Service=myServiceName;" & _
"Protocol=onsoctcp;" & _
"Database=myDbName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
If using INFORMIX-CLI 2.5 ODBC Driver
oConn.Open "Driver={Informix-CLI " & _ " 2.5 (32 Bit)};" & _
"Server=myServerName;" & _
"Database=myDbName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword" & _
For more information, see: Informix
Developer Zone, Connection
to ODBC Data Source,
For the local machine
oConn.Open "Driver={Easysoft IB6 ODBC};" & _
"Server=localhost;" & _
"Database=localhost:C:\Mydb.gdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For a remote machine
oConn.Open "Driver={Easysoft IB6 ODBC};" & _
"Server=myMachineName;" & _
"Database=myMachineName:C:\Mydb.gdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see: Connecting
to InterBase and Easysoft
For the local machine
oConn.Open "Driver={INTERSOLV " & _ " InterBase ODBC Driver (*.gdb)};" & _
"Server=localhost;" & _
"Database=localhost:C:\Mydb.gdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For a remote machine
oConn.Open "Driver={INTERSOLV" & _ " InterBase ODBC Driver (*.gdb)};" & _
"Server=myMachineName;" & _
"Database=myMachineName:C:\Mydb.gdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see: Google
Search (if you know a direct URL email
me)
oConn.Open "Driver={Lotus NotesSQL " & _ " 3.01 (32-bit) ODBC DRIVER (*.nsf)};" & _
"Server=myServerName;" & _
"Database=mydir\myDbName.nsf;" & _
"Uid=myUsername;" & _
"Pwd=myPassword" & _
For more information, see: Connection keywords
To connect to a local database
oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"
To connect to a remote database
oConn.Open "Driver={mySQL};" & _
"Server=db1.database.com;" & _
"Port=3306;" & _
"Option=131072;" & _
"Stmt=;" & _
"Database=mydb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see:
Programs
Known to Work with MyODBC
For the current Oracle ODBC Driver from Microsoft
oConn.Open "Driver={Microsoft " & _ " ODBC for Oracle};" & _
"Server=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For the older Oracle ODBC Driver from Microsoft
oConn.Open "Driver={Microsoft " & _ " ODBC Driver for Oracle};" & _
"ConnectString=OracleServer.world;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see:
Connection
String Format and Attributes
To view Microsoft KB articles related to Microsoft
ODBC for Oracle, click
here
oConn.Open "Driver={Oracle " & _ " ODBC Driver};" & _
"Dbq=myDBName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword" Where: The DBQ name must be
defined in the tnsnames.ora file
For more information, see: Oracle8
ODBC Driver Help, Oracle ODBC
FAQs, [asporacle] listserv FAQs, and ASPDB
Oracle
oConn.Open "Driver={Microsoft " & _ " Paradox Driver (*.db )};" & _
"DriverID=538;" & _
"Fil=Paradox 5.X;" & _
"DefaultDir=c:\dbpath\;" & _
"Dbq=c:\dbpath\;" & _
"CollatingSequence=ASCII"
Note: MDAC 2.1 (or greater)
requires the Borland Database Engine (BDE)
to update
Paradox ISAM fDBF files. (Q230126).
Note: There is an extra space after "db" in the
Paradox Driver name
For more information, see:
Paradox
Driver Programming Considerations To view Microsoft KB articles related to Microsoft
Paradox Driver, click
here
For Standard Security
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For Trusted Connection security
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Uid=;" & _
"Pwd="
' Or oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"Database=myDatabaseName;" & _
"Trusted_Connection=yes"
To Prompt user for username and password
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
"Server=MyServerName;" & _
"DataBase=myDatabaseName"
To
connect to SQL Server running on the same computer
oConn.Open "Driver={SQL Server};" & _
"Server=(local);" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
To
connect to SQL Server running on a remote computer (via an IP address)
oConn.Open "Driver={SQL Server};" & _
"Server=xxx.xxx.xxx.xxx;" & _
"Address=xxx.xxx.xxx.xxx,1433;" & _
"Network=DBMSSOCN;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
Where:
- xxx.xxx.xxx.xxx is an IP address
- 1433 is the default port number for SQL Server.
- "Network=DBMSSOCN" tells ODBC to <
BR> use TCP/IP rather than Named
Pipes (Q238949)
For more information, see: SQLDriverConnect
(ODBC)
To view Microsoft KB articles related to ODBC
Driver
for SQL Server, click
here
If
using the Sybase System 12 (or 12.5) Enterprise
Open Client ODBC Driver
oConn.Open "Driver={SYBASE " & _ " ASE ODBC Driver};" & _
"Srvr=myServerName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
If using the Sybase System 11 ODBC Driver
oConn.Open "Driver={SYBASE SYSTEM 11};" & _
"Srvr=myServerName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
If using
the Intersolv 3.10 Sybase ODBC Driver
oConn.Open "Driver={INTERSOLV " & _ " 3.10 32-BIT Sybase};" & _
"Srvr=myServerName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see: Sybase
System 10 ODBC Driver Reference Guide
To view Microsoft KB articles related to ODBC Driver
for Sybase, click
here
oConn.Open "ODBC; Driver=Sybase " & _ " SQL Anywhere 5.0;" & _
"DefaultDir=c:\dbpath\;" & _
"Dbf=c:\sqlany50\mydb.db;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;" & _
"Dsn="""""
Note: Including the DSN tag with a null string is
absolutely critical or else you
get the dreaded -7778 error.
For more information, see: Sybase
SQL Anywhere User Guide
oConn.Open "Provider=Teradata;" & _
"DBCName=MyDbcName;" & _
"Database=MyDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword"
For more information, see Teradata
ODBC Driver
oConn.Open _
"Driver={Microsoft Text " & _ " Driver (*.txt; *.csv)};" & _
"Dbq=c:\somepath\;" & _
"Extensions=asc,csv,tab,txt"
Then specify the filename in the SQL statement:
oRs.Open "Select * From customer.csv", _
oConn, adOpenStatic, adLockReadOnly, adCmdText
Note: If you are using a Tab delimited file, then
make sure you create
a schema.ini file,
and include the "Format=TabDelimited" option.
For more information, see:
Text
File Driver Programming Considerations
To view Microsoft KB articles related to Microsoft
Text Driver, click
here
With a database container
oConn.Open "Driver={Microsoft " & _ " Visual FoxPro Driver};" & _
"SourceType=DBC;" & _
"SourceDB=c:\somepath\mySourceDb.dbc;" & _
"Exclusive=No"
Without a database container (Free Table
Directory)
oConn.Open "Driver={Microsoft " & _ " Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=c:\somepath\mySourceDbFolder;" & _
"Exclusive=No"
For more information, see:
Visual
FoxPro ODBC Driver and
Q165492
To view Microsoft KB articles related to ODBC
Driver
for Visual FoxPro, click
here
|