Transfer from: Dynamic Network Production Guide www.knowsky.com
Here will briefly introduce several ADO connection: 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: From MDAC2.1, it is not possible to use this way, it is only The DSN file name is placed in ConnectString you must use the DSN, UID, PWD flags at the same time. For example, the following manner will be wrong in MDAC 2.1: Oconn.open "II.File DSN Oconn.open" filedsn = / somepath / mydb.dsn; "& _" uid = admin; "& _" PWD =; "III.odbc DSN-Less Connections 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.cs.csv", _ Oconn, AdopenStatic, AdlockReadOnly, AdcmdTextB) 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) Ordinary security mode Oconn.open "driver = {SQL Server};" & _ "server = carl2;" & _ "database = public;" & _ "uid = sa;" & _ "pwd =;" ii) Trust Safety 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) Using old version ODBC Driver from Microsoft: Oconn.open _ "Driver = {Microsoft ODBC Driver for Oracle};" & _ "ConnectString = Oracleserver.world;" & _ "
Uid = demo; "& _" PWD = DEMO; "III) uses Microsoft OLE DB DATA LINK Connections Mode Data Link File (UDL) a) Using Absolute Path Oconn.open" File Name = / SomePath / Pubs.ud; " b) Use relative path Oconn.Open "file name = pubs.ud;" v) OLE DB Provider Connections method a) OLE DB Provider for ODBC Databases I) Access (JET): Oconn.open _ "provider = msdasql;" _ "Driver = {Microsoft Access Driver (* .mdb)};" & _ "DBQ = / SomePath / mydb.mdb;" & _ "UID =;" & _ "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) Ordinary 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 into 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 in the first line Title, will not include the first line into RecordSet in the Provider, D) OLE DB Provider for SQL Server i) Normal Security Mode: Oconn.open "Province = SQLOLEDB;" & _ "Network Library = dbmssocn;" & _ "Data Source = Carl2;" & _ "Initial Catalog = Pubs;" & _ "User ID = SA;" & _ "PASSWORD =;" II) Using Trust Security Mode: Oconn.open "provider = SQLOLDB;" & _ Network library = dbmssocn; "& _" data source = carl2; "& _" initial catalog = pubs; "& _" trusted_connection = yes; "Note:" NetWork Library = dbmssoCN "declaration OLE DB use TCP / IP replacement NAMED PIPES .e) OLE DB Provider for Oracle Oconn.open "provoder = msdaora;" & _ "DA Ta Source = ORACle Server.World; "& _" User ID = SA; "& _" Password =; "(VI) Remote Ole DB Provider Connections mode (that is, I have been studying RDS mode, huh, huh.
