Here will briefly introduce several ADO connection methods: ODBC DSN, ODBC DSN-LESS,
OLE DB Provider, and "MS Remote" provider.
1. ODBC DSN connection
I.dsn
Oconn.open "DSN = Advworks;" & _
"Uid = admin;" & _
"Pwd =;"
Note: You cannot use this way from MDAC2.1, just put the name of the DSN file in ConnectString.
You must use the DSN, UID, and PWD flags at the same time. For example, the following manner will be wrong in MDAC 2.1:
Oconn.open "Advworks"
Ii.file DSN
Oconn.open "filedsn = / somepath / mydb.dsn;" & _
"Uid = admin;" & _
"Pwd =;"
III.odbc DSN-Less Connections
a) ODBC TEXT DRIVER
Oconn.open _
"Driver = {Microsoft text driver (* .txt; * .csv)};" & _
"Dbq = / somepath /;" & _
"Extensions = ASC, CSV, Tab, TXT;" & _
"Persist security info = false"
Note: The file name you need to specify in the SQL statement. E.g:
Ors.open "SELECT * from Customer.csv", _
Oconn, AdopenStatic, AdlockReadonly, AdcmdText
b) ODBC Driver for Access
i) normal security mode:
Oconn.open _
"Driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = / somepath / mydb.mdb;" & _
"Uid = admin;" & _
"Pwd =;"
II) If you use System Database:
Oconn.open _
"Driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = / somepath / mydb.mdb;" & _
"Systemdb = / somepath / mydb.mdw;", _
"admin", ""
c) ODBC DRIVER for SQL Server
i) normal security mode
Oconn.open "driver = {SQL Server};" & _
"Server = carl2;" & _
"Database = Pubs;" & _
"UID = sa;" & _
"Pwd =;"
II) Use trust security mode:
Oconn.open "driver = {SQL Server};" & _
"Server = carl2;" & _
"Database = Pubs;" & _
"UID =;" & _
"Pwd =;"
Note: To use blank UID and PWD
d) ODBC Driver for Oracle
i) Use existing Oracle ODBC Driver from Microsoft:
Oconn.open _
"Driver = {microsoft odbc for oracle};" & _
"Server = oracleserver.world;" & _
"UID = Demo;" & _
"PWD = DEMO;"
II) Use old versions of Oracle ODBC Driver from Microsoft:
Oconn.open _
"Driver = {Microsoft ODBC Driver for Oracle};" & _
"ConnectString = OraclesRver.world;" & _
"UID = Demo;" & _
"PWD = DEMO;"
IIII) Use Microsoft OLE DB DATA LINK Connections Data Link File (UDL)
a) use absolute path
Oconn.open "file name = / SomePath / Pubs.ud;"
b) use the relative path
Oconn.open "file name = pubs.ud1;"
V) OLE DB Provider Connections
a) OLE DB Provider for ODBC Databases
I) Access (JET):
Oconn.open _
"Provider = msdasql;" & _
"Driver = {Microsoft Access Driver (* .mdb)};" & _
"DBQ = / somepath / mydb.mdb;" & _
"Uid = admin;" & _
"Pwd =;"
II) SQL Server:
Oconn.open _
"Provider = msdasql;" & _
Driver = {SQL Server}; "& _
"Server = carl2;" & _
"Database = Pubs;" & _
"UID = sa;" & _
"Pwd =;"
b) OLE DB Provider for Microsoft Jet (Access)
i) normal security mode:
Oconn.open _
"Provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = / SomePath / mydb.mdb;" & _
"User ID = admin;" & _
"Password =;"
II) If you use System Database:
Oconn.open _
"Provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = / SomePath / mydb.mdb;" & _
"Jet OLEDB: System Database = System.mdw;", _ "admin", ""
Note: When using OLE DB Provider 4.0, you need to convert MDB and MDW files to 4.0 database format.
III) If MDB needs a database password:
Oconn.open _
"Provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = / SomePath / mydb.mdb;" & _
"Jet OLEDB: Database Password = mydbpassword;", _
"admin", ""
c) OLE DB Provider for Excel Spreadsheet:
Oconn.open _
"Provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = / SomePath / Expenses.xls;" & _
"Extended Properties =" "Excel 8.0; HDR = YES;" ";"
Note: "HDR = YES" means that in the first line is a tag, the first line will not include the first line into the Recordset in the provider.
d) OLE DB Provider for SQL Server
i) normal security mode:
Oconn.open "provider = sqloledb;" & _
"NetWork Library = DBMSSOCN;" & _
"Data Source = Carl2;" & _
"InTIAL CATALOG = PUBS;" & _
User ID = sa; "& _
"Password =;"
II) Use trust security mode:
Oconn.open "provider = sqloledb;" & _
Network library = dbmsoccn; "& _
"Data Source = Carl2;" & _
"InTIAL CATALOG = PUBS;" & _
Trusted_Connection = YES; "
Note: "NetWork Library = DBMSSOCN" declares that OLE DB uses TCP / IP instead of Named Pipes.
e) OLE DB Provider for Oracle
Oconn.open "provider = msdaora;" & _
"Data Source = Oracleserver.world;" & _
User ID = sa; "& _
"Password =;"
(Vi) Remote Ole DB Provider Connections mode (that is, I have been studying RDS mode, huh, huh.):
a) MS Remote - Access (Jet)
i) ODBC DSN:
Oconn.open "Remote Server = http: // carl2;" & _
"Remote Provider = msdasql;" & _ "DSN = Advworks;" & _
"UID = admin" & _
"Pwd =;"
II) OLE DB Provider:
Oconn.open "provider = ms remote;" & _
"Remote Server = http:// carl2;" & _
"Remote Provider = Microsoft.jet.Oledb.4.0;" & _
"Data Source = / SomePath / mydb.mdb;", _
"admin", ""
III) OLE DB Provider Custom Transaction Object
Oconn.open "provider = ms remote;" & _
"Remote Server = http:// carl2;" & _
"Handler = msdfmap.handler;" & _
"Data Source = myadvworksoledbconnecttag;"
b) MS Remote - SQL Server
i) ODBC DSN:
Oconn.open "Remote Server = http: // carl2;" & _
"Remote Provider = MSDasql;" & _
"NetWork Library = DBMSSOCN;" & _
"DSN = PUBS;" & _
"UID = SA" & _
"Pwd =;"
II) OLE DB Provider
Oconn.open "provider = ms remote;" & _
"Remote Server = http:// carl2;" & _
"Remote Provider = SQLOLEDB;" & _
"NetWork Library = DBMSSOCN;" & _
"Data Source = Carl2;" & _
"InTIAL CATALOG = PUBS;" & _
User ID = sa; "& _
"Password =;"
