The database connection query manual will briefly introduce several ADO connection methods: ODBC DSN, ODBC DSN-LESS,
OLE DB Provider and MS Remote Provider
I.dsn
Oconn.open "DSN = Advworks;" & _ "UID = Admin;" & _ "PWD =;" Note: You can't use this way from MDAC2.1, it is only put the DSN file name 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: You need to specify the file name used in the SQL statement. For example: 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;" & _ "data = pubs;" & _ "uid =;" & _ "PWD =;" pay attention : To use blank UID and PWDD) 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 = Oracleserver.world;" & _ "UID = Demo;" & _ "PWD = DEMO; "
IV. Using Microsoft OLE DB DATA LINK Connections Data Link File (UDL)
a) Using absolute path Oconn.open "file name = / somepath / pubs.udl;"
b) Use the relative path Oconn.Open "file name = pubs.ud;"
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 the OLE DB Provider 4.0 version, you need to convert MDB and MDW files into 4.0 database format III) If MDB needs a database password: Oconn.open _ "provike = 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 / expenss.xls;" & _ "extended Properties =" "" "" : "HDR = YES" means that in the first line is a head title, and the first line will be included in the Recordset in the provider.
d) OLE DB Provider for SQL Server
i) normal security mode: Oconn.open "provider = SQLOLEDB;" & _ "network library = dbmsocco;" & _ "data source = carl2;" & _ "initial catalog = public;" & _ "user ID = sa;" & _ " "& _" Password =; "
II) Use trust security mode: Oconn.open "provider = sqloledb;" & _ network library = dbmsoccn; "& _" data source = carl2; "& _" initial catalog = pubs; "& _" trusted_connection = yes; "& _" 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 "provoder = msdaora;" & _ "data source = oracleserver.world;" & _ "user id = sa;" & _ "password =;"
Vi.Remote Ole DB Provider Connections
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: OLE DB Provider: OCONN.Open "provmote;" & _ "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 "provmote;" & _ "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 "Province = ms Remote;" & _ "Remote Server = http: // carl2;" & _ "Remote Provider = SQLOLEDB;" & _ "NetWork Library = dbmssoCn;" & _ "Data Source = carl2; "& _" initial catalog = public; "& _" user ID = sa; "& _" password =; "
