- ODBC Driver for Access
For Standard Security:
oConn.Open "Driver={ Microsoft Access Driver (*.mdb)};" & _ "Dbq=c:\somepath\mydb.mdb;" 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;" If MDB is located on a Network Share
oConn.Open "Driver={ Microsoft Access Driver (*.mdb)};" & _ "Dbq=\\myServer\myShare\myPath\myDb.mdb;" If MDB is located on a remote machine
- Call an XML Web Service that contains data access web methods for MDB- Or upgrade to SQL Server and use an
If you don't know the path to the MDB (using ASP)
<% ' ASP server-side codeoConn.Open "Driver={ Microsoft Access Driver (*.mdb)};" & _ "Dbq=" & Server.MapPath(".") & "\db\myDb.mdb;" %> Make sure the Web identity has read/write permissions to the directory
the MDB is located in. e.g. "db" would need the read/write permissions.If you don't know the path to the MDB (using VB)
oConn.Open "Driver={ Microsoft Access Driver (*.mdb)};" & _ "Dbq=" & App.Path & "\myDb.mdb;" This assumes the MDB is in the same directory where the application is running.
For more information, see:
To view Microsoft KB articles related to Microsoft Access Driver,
- ODBC Driver for AS/400 (from IBM)
oConn.Open "Driver={ Client Access ODBC Driver (32-bit)};" & _ "System=myAS400;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For more information, see:
- ODBC Driver for dBASE
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. ().
For more information, see:
To view Microsoft KB articles related to Microsoft dBASE Driver,
- ODBC Driver for Excel
oConn.Open "Driver={ Microsoft Excel Driver (*.xls)};" & _ "DriverId=790;" & _ "Dbq=c:\somepath\mySpreadsheet.xls;" & _ "DefaultDir=c:\somepath" For more information, see:
To view Microsoft KB articles related to Microsoft Excel Driver,
- ODBC Driver for Informix
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: , ,
- ODBC Driver for Interbase - from Easysoft
For the local machine
oConn.Open "Driver={ Easysoft IB6 ODBC};" & _ "Server=localhost;" & _ "Database=localhost:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For a remote machine
oConn.Open "Driver={ Easysoft IB6 ODBC};" & _ "Server=myMachineName;" & _ "Database=myMachineName:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For more information, see: and
- ODBC Driver for Interbase - from InterSolv
For the local machine
oConn.Open "Driver={ INTERSOLV InterBase ODBC Driver (*.gdb)};" & _ "Server=localhost;" & _ "Database=localhost:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For a remote machine
oConn.Open "Driver={ INTERSOLV InterBase ODBC Driver (*.gdb)};" & _ "Server=myMachineName;" & _ "Database=myMachineName:C:\Home\Data\Mydb.gdb;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For more information, see: (if you know a direct URL )
- ODBC Driver for Lotus Notes
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:
- ODBC Driver for Mimer
oConn.Open "Driver={ MIMER};" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For more information, see:
- ODBC Driver for MySQL (via MyODBC)
To connect to a local database (using MyODBC Driver)
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" To connect to a local database (using MySQL ODBC 3.51 Driver)
oConn.Open "DRIVER={ MySQL ODBC 3.51 Driver};" & _ "Server=myServerName;" & _ "Port=3306;" & _ "Option=16384;" & _ "Stmt=;" & _ "Database=mydatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" Or
oConn.Open "DRIVER={ MySQL ODBC 3.51 Driver};" & _"SERVER=myServerName;" & _"DATABASE=myDatabaseName;" & _"USER=myUsername;" & _"PASSWORD=myPassword;" Note: When you first install MySQL, it creates a "root" user account (in the sys datbase's user table) with a blank password.
For more information, see:
- ODBC Driver for Oracle - from Microsoft
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:
To view Microsoft KB articles related to Microsoft ODBC for Oracle,
- ODBC Driver for Oracle - from Oracle
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: , , , and
- ODBC Driver for Oracle Rdb
oConn.Open "Driver={ Oracle ODBC Driver for Rdb};" & _ "UID=myUserID;" & _ "PWD=myPassword;" & _ "SVR=aServerName;" & _ "XPT=2;" & _ "DATABASE=Attach 'filename disk:[dir1.dir2]rootfile';" & _ "CLS=aClassName;" & _ "DBA=W;" & _ "CSO=1;" & _ "TLL=aLibName;" & _ "TLO=0;" & _ "DSO=0;" & _ "PWV=secPassword" For connecting to a database over TCP/IP, not using a specific SQL/Services class:
oConn.Open "Driver={ Oracle ODBC Driver for Rdb};" & _ "UID=myUserID;" & _ "PWD=myPassword;" & _ "SVR=aServerName;" & _ "XPT=2;" & _ "DATABASE=Attach 'filename disk:[dir1.dir2]rootfile'" For connecting to a database over DECNET using a specific class, not using pre-attached connection:
oConn.Open "Driver={ Oracle ODBC Driver for Rdb};" & _ "UID=myUserID;" & _ "PWD=myPassword;" & _ "SVR=aServerName;" & _ "XPT=1;" & _ "DATABASE=Attach 'filename disk:[dir1.dir2]rootfile';" & _ "CLS=aClassName" For connecting to a database over TCP/IP through a class that uses pre-attached connections:
oConn.Open "Driver={ Oracle ODBC Driver for Rdb};" & _ "UID=myUserID;" & _ "PWD=myPassword;" & _ "SVR=aServerName;" & _ "XPT=2;" & _ "CLS=aClassName" For more information, see:
- ODBC Driver for Paradox
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. ().
Note: There is an extra space after "db" in the Paradox Driver name
For more information, see:
To view Microsoft KB articles related to Microsoft Paradox Driver,
- ODBC Driver for SQL Server
oConn.Open "ODBC;Driver={SQL Native Client};" & "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword" For Trusted Connection Security oConn.Open "ODBC;Driver={SQL Native Client};" & _ "Server=MyServerName;" & _ "Database=myDatabaseName;" & _ "Trusted_Connection=yes" Using the MDAC ODBC Driver 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;" & _ "Trusted_Connection=yes" 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 use TCP/IP rather than Named Pipes ()For more information, see:
To view Microsoft KB articles related to ODBC Driver for SQL Server,
- ODBC Driver for Sybase
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:
To view Microsoft KB articles related to ODBC Driver for Sybase,
- ODBC Driver for Sybase SQL Anywhere
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:
- ODBC Driver for Teradata
oConn.Open "Provider=Teradata;" & _ "DBCName=MyDbcName;" & _ "Database=MyDatabaseName;" & _ "Uid=myUsername;" & _ "Pwd=myPassword"
For more information, see
- ODBC Driver for Text
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 file, and include the "Format=TabDelimited" option.
For more information, see:
To view Microsoft KB articles related to Microsoft Text Driver,
- ODBC Driver for Visual FoxPro
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"