Ïðèìåðû ïîäêëþ÷åíèÿ ðàçëè÷íûõ Áàç Äàííûõ ÷åðåç ADO

Êàòåãîðèÿ: Áàçû äàííûõ
Äàòà ïóáëèêàöèè:06-12-2004

Àâòîð: Carl Prothman, Microsoft Corp.

Äàííûé ìàòåðèàë ñîäåðæèò ïðèìåðû ñîçäàíèÿ ñòðîê ADO connection äëÿ ðàçëè÷íûõ òèïîâ ÷àñòî èñïîëüçóåìûõ Áàç Äàííûõ, íàïðèìåð: ODBC DSN, ODBC DSN-Less, OLE DB Provider, MS Remote, Oracle, Excel, FoxPro è ò.ä.

ODBC DSN Connections

DSN

oConn.Open "DSN=AdvWorks;" & _
                   "Uid=Admin;" & _
                   "Pwd=; File DSN

oConn.Open "FILEDSN=somepathmydb.dsn;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

Ïîäðîáíåå ñì.:  About ODBC data sources è  How to Use File DSNs and DSN-less Connections

  ODBC DSN-Less Connections

Äðàéâåð ODBC äëÿ Access

Ñòàíäàðòíàÿ ïîëèòèêà áåçîïàñíîñòè:

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                   "Dbq=somepathmydb.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

Åñëè èñïîëüçóåòñÿ Workgroup (System database):

oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                   "Dbq=somepathmydb.mdb;" & _
                   "SystemDB=somepathmydb.mdw;", _
                   "admin", ""

Ïîäðîáíåå ñì.:  Microsoft Access Driver Programming Considerations

  Äðàéâåð ODBC äëÿ dBASE

oConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
                  "DriverID=277;" & _
                  "Dbq=somepath;"

Ïðèìå÷àíèå: Çàäàéòå èìÿ ôàéëà â çàïðîñå SQL. Íàïðèìåð:
         oRs.Open "Select * From user.dbf", oConn, , ,adCmdText

Ïðèìå÷àíèå: MDAC 2.1 (è äàëåå) òðåáóåò Borland Database Engine (BDE) äëÿ îáíîâëåíèÿ DBF-ôàéëîâ dBase. (Q238431).

Ïîäðîáíåå ñì.:  dBASE Driver Programming Considerations

 

Äðàéâåð ODBC äëÿ Excel

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                   "DriverId=790;" & _
                   "Dbq=somepathmySpreadsheet.xls;" & _
                   "DefaultDir=somepath;" 

Ïîäðîáíåå ñì.:  Microsoft Excel Driver Programming Considerations

  Äðàéâåð ODBC äëÿ Oracle

Äëÿ òåêóùåãî ODBC-äðàéâåðà Microsoft äëÿ Oracle:

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=OracleServer.world;" & _
                   "Uid=demo;" & _
                   "Pwd=demo;"

Äëÿ ñòàðîãî ODBC-äðàéâåðà Microsoft äëÿ Oracle:

oConn.Open "Driver={Microsoft Äðàéâåð ODBC äëÿ Oracle};" & _
                   "ConnectString=OracleServer.world;" & _
                   "Uid=demo;" & _
                   "Pwd=demo;"

Ïîäðîáíåå ñì.:  Connection String Format and Attributes

  Äðàéâåð ODBC äëÿ Paradox

oConn.Open "Driver={Microsoft Paradox Driver (*.db)};" & _
                   "DriverID=538;" & _
                   "Fil=Paradox 5.X;" & _
                   "DefaultDir=c:dbpath;" & _
                   "Dbq=c:dbpath;" & _
                   "CollatingSequence=ASCII;" 

Ïðèìå÷àíèå: MDAC 2.1 (è äàëåå) òðåáóåò Borland Database Engine (BDE) äëÿ îáíîâëåíèÿ fDBF-ôàéëîâ Paradox ISAM. (Q230126).

Ïîäðîáíåå ñì.:  Paradox Driver Programming Considerations

  Äðàéâåð ODBC äëÿ SQL Server

Ñòàíäàðòíàÿ ïîëèòèêà áåçîïàñíîñòè:

oConn.Open "Driver={SQL Server};" & _
                   "Server=carl2;" & _
                   "Database=pubs;" & _
                   "Uid=sa;" & _
                   "Pwd=;"

Trusted Connection:

oConn.Open "Driver={SQL Server};" & _
                   "Server=carl2;" & _
                   "Database=pubs;" & _
                   "Uid=;" & _
                   "Pwd=;"

Ïðèìå÷àíèå: èñïîëüçóéòå ïóñòîé UID è PWD

Ñ çàïðîñîì èìåíè ïîëüçîâàòåëÿ è ïàðîëÿ

oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Driver={SQL Server};" & _
                   "Server=carl2;" & _
                   "DataBase=pubs;"
     

Ïîäðîáíåå ñì.:  SQLDriverConnect (ODBC)

  Äðàéâåð ODBC äëÿ Sybase

Èñïîëüçîâàíèå ODBC-äðàéâåðà Sybase System 11:

oConn.Open "Driver={SYBASE SYSTEM 11};" & _
                   "Srvr=myServerName;" & _
                   "Uid=myUsername;" & _
                   "Pwd=myPassword;"

Èñïîëüçîâàíèå ODBC-äðàéâåðà Intersolv 3.10 Sybase:

oConn.Open "Driver={INTERSOLV 3.10 32-BIT Sybase};" & _
                   "Srvr=myServerName;" & _
                   "Uid=myUsername;" & _
                   "Pwd=myPassword;"

Ïîäðîáíåå ñì.: Sybase System 10 ODBC Driver Reference Guide

  Äðàéâåð ODBC äëÿ Sybase SQL Anywhere

oConn.Open "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _
                   "DefaultDir=c:dbpath;" & _
                   "Dbf=c:sqlany50mydb.db;" & _
                   "Uid=dbagroup;" & _
                   "Pwd=;"
                   "Dsn="""";"

Ïðèìå÷àíèå: Íåîáõîäèìî âêëþ÷åíèå òåãà DSN ñ íóëåâîé ñòðîêîé, èíà÷å âîçíèêàåò îøèáêà 7778.

Ïîäðîáíåå ñì.:  Sybase SQL Anywhere User Guide

  Äðàéâåð ODBC äëÿ òåêñòà

oConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
                   "Dbq=somepath;" & _
                   "Extensions=asc,csv,tab,txt;" & _
                   "Persist Security Info=False"

Ïðèìå÷àíèå: Çàäàéòå èìÿ ôàéëà çàïðîñå SQL. Íàïðèìåð:

oRs.Open "Select * From customer.csv", _
               oConn, adOpenStatic, adLockReadOnly, adCmdText

Ïîäðîáíåå ñì.:  Text File Driver Programming Considerations

  Äðàéâåð ODBC äëÿ Visual FoxPro

Èñïîëüçóÿ database container:

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
                   "SourceType=DBC;" & _
                   "SourceDB=somepathmySourceDb.dbc;" & _
                   "Exclusive=No;" 

Áåç database container (Free Table Directory):

oConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
                   "SourceType=DBF;" & _
                   "SourceDB=somepathmySourceDbFolder;" & _
                   "Exclusive=No;" 
 

Ïîäðîáíåå ñì.:  Visual FoxPro ODBC Driver and Q165492

  OLE DB Data Link Connections Data Link File (UDL)

Äëÿ àáñîëþòíîãî ïóòè:

oConn.Open "File Name=somepathpubs.udl;"

Äëÿ îòíîñèòåëüíîãî ïóòè:

oConn.Open "File Name=pubs.udl;"
  

Ïîäðîáíåå ñì.:  HOWTO: Use Data Link Files with ADO

Ïðèìå÷àíèå: Windows 2000 áîëüøå íå ïîääåðæèâàåò ìåíþ "New | Microsoft Data Link". Ïîýòîìó äëÿ òîãî, ÷òîáû ñîçäàòü Data Link File, ñîçäàéòå òåêñòîâûé ôàéë, çàòåì èçìåíèòå åãî ðàñøèðåíèå íà ".udl".  Çàòåì äâàæäû ùåëêíèòå ïî ôàéëó è óñòàíîâèòå ñâîéñòâà Data Link.

  OLE DB Provider Connections OLE DB Provider for Active Directory Service

oConn.Open "Provider=ADSDSOObject;" & _
                   "User Id=admin;" & _
                   "Password=;"
     

Ïîäðîáíåå ñì.:  Microsoft OLE DB Provider for Microsoft Active Directory Service

 OLE DB Provider for DB2

oConn.Open = "Provider=DB2OLEDB;" & 
                     "Network Transport Library=TCPIP;" & 
                     "Network Address=MyServer;" & _
                     "Package Collection=MyPackage;" & 
                     "Host CCSID=1142"
                     "Initial Catalog=MyDB;" & 
                     "User ID=MyUsername;" & _
                     "Password=MyPassword;"

Ïîäðîáíåå ñì.:  INF: Configuring Data Sources for the Microsoft OLE DB Provider for DB2

 OLE DB Provider for Index Server

oConn.Open "Provider=msidxs;" & _
                   "Data source=MyCatalog;"
   

Ïîäðîáíåå ñì.:  Microsoft OLE DB Provider for Microsoft Indexing Service

 OLE DB Provider for Internet Publishing

oConn.Open "Provider=MSDAIPP.DSO;" & _
            "Data Source=http://mywebsite/mydir;" & _
            "User Id=administrator;" & _
            "Password=mypassword;"
  

Ïîäðîáíåå ñì.: Microsoft OLE DB Provider for Internet Publishing

 OLE DB Provider for Microsoft Jet

For standard security:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=somepathmydb.mdb;" & _
                   "User Id=admin;" & _
                   "Password=;"

If you are using a workgroup (system database):

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=somepathmydb.mdb;" & _
                   "Jet OLEDB:System Database=system.mdw;", _
                   "admin", ""

Note, remember to convert both the MDB and the MDW to the 4.0 database format when using the 4.0 OLE DB Provider.

If your MDB has a database password:

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=somepathmydb.mdb;" & _
                   "Jet OLEDB:Database Password=MyDbPassword;", _
                   "admin", ""

Ïîäðîáíåå ñì.: OLE DB Provider for Microsoft Jet  and  Q191754

 

Ïðèìå÷àíèå: You can also open an Excel spreadsheet using the "OLE DB Provider for Microsoft Jet"

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=somepathexpenses.xls;" & _
                   "Extended Properties=""Excel 8.0;HDR=Yes;"";"

Where "HDR=Yes" means that there is a header row in the cell range 
(or named range), so the provider will not include the first row of the
selection into the recordset.  If "HDR=No", then the provider will include
the first row of the cell range (or named ranged) into the recordset.

 OLE DB Provider for ODBC Databases

For Access (Jet):

oConn.Open "Provider=MSDASQL;" & _
                   "Driver={Microsoft Access Driver (*.mdb)};" & _
                   "Dbq=somepathmydb.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

For SQL Server:

oConn.Open "Provider=MSDASQL;" & _  
                   "Driver={SQL Server};" & _
                   "Server=carl2;" & _
                   "Database=pubs;" & _
                   "Uid=sa;" & _
                   "Pwd=;"

Ïîäðîáíåå ñì.:  Microsoft OLE DB Provider for ODBC

 OLE DB Provider for Oracle (from Microsoft)

oConn.Open "Provider=msdaora;" & _
                   "Data Source=OracleServer.world;" & _
                   "User Id=carl;" & _
                   "Password=;"

Ïîäðîáíåå ñì.: Microsoft OLE DB Provider for Oracle

 OLE DB Provider for Oracle (from Oracle)

For Standard Security:

oConn.Open "Provider=OraOLEDB.Oracle;" & _
                   "Data Source=MyOracleDB;" & _
                   "User Id=carl;" & _
                   "Password=;"

For a Trusted Connection:

oConn.Open "Provider=OraOLEDB.Oracle;" & _
                   "Data Source=MyOracleDB;" & _
                   "User Id=/;" & _
                   "Password=;"
" Or

oConn.Open "Provider=OraOLEDB.Oracle;" & _
                   "Data Source=MyOracleDB;" & _
                   "OSAuthent=1;"
  

Ïðèìå÷àíèå: "Data Source=" must be set to the appropriate Net8 name which is known to the naming method in use. For example, for Local Naming, it is the alias in the tnsnames.ora file; for Oracle Names, it is the Net8 Service Name. 

Ïîäðîáíåå ñì.: Connecting to an Oracle Database
(Note, if you get a Logon dialog, then click Cancel, then perform a one-time free signup with Oracle"s TechNet system)

  OLE DB Provider for SQL Server

For Standard Security:

oConn.Open "Provider=sqloledb;" & _
                   "Data Source=carl2;" & _
                   "Initial Catalog=pubs;" & _
                   "User Id=sa;" & _
                   "Password=;"

For a Trusted Connection:

oConn.Open "Provider=sqloledb;" & _
                   "Data Source=carl2;" & _
                   "Initial Catalog=pubs;" & _
                   "Trusted_Connection=yes;"

To connect to a "Named Instance" (SQL Server 2000)

oConn.Open "Provider=sqloledb;" & _
                   "Data Source=carl2Inst2;" & _
                   "Initial Catalog=pubs;" & _
                   "User Id=sa;" & _
                   "Password=;"

To Prompt user for username and password:

oConn.Provider = "sqloledb"
oConn.Properties("Prompt") = adPromptAlways
oConn.Open "Data Source=carl2;" & _
                   "Initial Catalog=pubs;" 

To connect via an IP address

oConn.Open "Provider=sqloledb;" & _
                   "Data Source=xxx.xxx.xxx.xxx,1433;" & _
                   "Network Library=DBMSSOCN;" & _
                   "Initial Catalog=pubs;" & _
                   "User ID=sa;" & _
                   "Password=;"

Ïðèìå÷àíèå: 
"Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949). 
And 1433 is the default port number for SQL Server

Ïîäðîáíåå ñì.: Connecting to a SQL Server Data Source

  Remote OLE DB Provider Connections

The following connections strings use Microsoft"s remote provider  (MS Remote). The MS Remote provider tells ADO to communicate  with the remote server (via the RDS DataFactory) and to use the  remote provider that is installed on the remote server.
     MS Remote - Access (Jet)

If you want to use an ODBC DSN on the remote machine:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Remote Provider=MSDASQL;" & _
                   "DSN=AdvWorks;" & _
                   "Uid=admin;" & _
                   "Pwd=;"

If you want to use an OLE DB Provider on the remote machine:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Remote Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=somepathmydb.mdb;", _
                   "admin", ""

If you want to use an OLE DB Provider on the remote machine with a RDS DataFactory Handler:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Handler=MSDFMAP.Handler;" & _
                   "Data Source=MyAdvworksConn;"

The corresponding entry in the winntMsdfmap.ini file would be:

[connect MyAdvworksConn]
Access = ReadWrite
Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=mydb.mdb;" & _
                "User Id=admin;" & _
                "Password=;"
   MS Remote - SQL Server

If you want to use an ODBC DSN on the remote machine:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Remote Provider=MSDASQL;" & _
                   "Network Library=DBMSSOCN;" & _
                   "DSN=Pubs;" & _
                   "Uid=sa;" & _
                   "Pwd=;"

If you want to use an OLE DB Provider on the remote machine:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Remote Provider=SQLOLEDB;" & _
                   "Network Library=DBMSSOCN;" & _
                   "Data Source=carl2;" & _
                   "Initial Catalog=pubs;" & _
                   "User ID=sa;" & _
                   "Password=;"

If you want to use an OLE DB Provider on the remote machine with a RDS DataFactory Handler:

oConn.Open "Provider=MS Remote;" & _
                   "Remote Server=http://carl2;" & _
                   "Handler=MSDFMAP.Handler;" & _
                   "Data Source=MyPubsConn;"

The corresponding entry in the winntMsdfmap.ini file would be:

[connect MyPubsConn]
Access = ReadWrite
Connect = "Provider=SQLOLEDB;" & _
                "Network Library=DBMSSOCN;" & _
                "Data Source=carl2;" & _
                "Initial Catalog=pubs;" & _
                "User ID=sa;" & _
                "Password=;"

Ïðèìå÷àíèå: "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949).
  

Ïîäðîáíåå ñì.:  Microsoft OLE DB Remoting Provider