Автор: Carl Prothman, Microsoft Corp.
Добавлено: 26.02.2001 « предыдущая следующая » все материалы
Данный материал содержит примеры создания строк ADO connection для различных типов часто используемых Баз Данных, например: ODBC DSN, ODBC DSN-Less, OLE DB Provider, MS Remote, Oracle, Excel, FoxPro и т.д.

ODBC DSN Connections

DSN

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

oConn.Open "FILEDSN=\somepath\mydb.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 "river={Microsoft Access Driver (*.mdb)};" & _
                   "bq=\somepath\mydb.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

Если используется Workgroup (System database):

oConn.Open "river={Microsoft Access Driver (*.mdb)};" & _
                   "bq=\somepath\mydb.mdb;" & _
                   "SystemDB=\somepath\mydb.mdw;", _
                   "admin", ""

Подробнее см.: Microsoft Access Driver Programming Considerations

  Драйвер ODBC для dBASE

oConn.Open "river={Microsoft dBASE Driver (*.dbf)};" & _
                  "riverID=277;" & _
                  "bq=\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 "river={Microsoft Excel Driver (*.xls)};" & _
                   "riverId=790;" & _
                   "bq=\somepath\mySpreadsheet.xls;" & _
                   "efaultDir=\somepath;"

Подробнее см.: Microsoft Excel Driver Programming Considerations

  Драйвер ODBC для Oracle

Для текущего ODBC-драйвера Microsoft для Oracle:

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

Для старого ODBC-драйвера Microsoft для Oracle:

oConn.Open "river={Microsoft Драйвер ODBC для Oracle};" & _
                   "ConnectString=OracleServer.world;" & _
                   "Uid=demo;" & _
                   "Pwd=demo;"

Подробнее см.: Connection String Format and Attributes

  Драйвер ODBC для Paradox

oConn.Open "river={Microsoft Paradox Driver (*.db)};" & _
                   "riverID=538;" & _
                   "Fil=Paradox 5.X;" & _
                   "efaultDir=c:\dbpath\;" & _
                   "bq=c:\dbpath\;" & _
                   "CollatingSequence=ASCII;"

Примечание: MDAC 2.1 (и далее) требует Borland Database Engine (BDE) для обновления fDBF-файлов Paradox ISAM. (Q230126).

Подробнее см.: Paradox Driver Programming Considerations

  Драйвер ODBC для SQL Server

Стандартная политика безопасности:

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

Trusted Connection:

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

Примечание: используйте пустой UID и PWD

С запросом имени пользователя и пароля

oConn.Properties("Prompt" = adPromptAlways
oConn.Open "river={SQL Server};" & _
                   "Server=carl2;" & _
                   "ataBase=pubs;"
      

Подробнее см.: SQLDriverConnect (ODBC)

  Драйвер ODBC для Sybase

Использование ODBC-драйвера Sybase System 11:

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

Использование ODBC-драйвера Intersolv 3.10 Sybase:

oConn.Open "river={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;" & _
                   "efaultDir=c:\dbpath\;" & _
                   "bf=c:\sqlany50\mydb.db;" & _
                   "Uid=dbagroup;" & _
                   "Pwd=;"
                   "sn="""";"

Примечание: Необходимо включение тега DSN с нулевой строкой, иначе возникает ошибка 7778.

Подробнее см.: Sybase SQL Anywhere User Guide

  Драйвер ODBC для текста

oConn.Open "river={Microsoft Text Driver (*.txt; *.csv)};" & _
                   "bq=\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 "river={Microsoft Visual FoxPro Driver};" & _
                   "SourceType=DBC;" & _
                   "SourceDB=\somepath\mySourceDb.dbc;" & _
                   "Exclusive=No;"

Без database container (Free Table Directory):

oConn.Open "river={Microsoft Visual FoxPro Driver};" & _
                   "SourceType=DBF;" & _
                   "SourceDB=\somepath\mySourceDbFolder;" & _
                   "Exclusive=No;"
  

Подробнее см.: Visual FoxPro ODBC Driver and Q165492

  OLE DB Data Link Connections Data Link File (UDL)

Для абсолютного пути:

oConn.Open "File Name=\somepath\pubs.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;" & _
                   "ata source=MyCatalog;"
    

Подробнее см.: Microsoft OLE DB Provider for Microsoft Indexing Service

 OLE DB Provider for Internet Publishing

oConn.Open "Provider=MSDAIPP.DSO;" & _
            "ata 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;" & _
                   "ata Source=\somepath\mydb.mdb;" & _
                   "User Id=admin;" & _
                   "Password=;"

If you are using a workgroup (system database):

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "ata Source=\somepath\mydb.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;" & _
                   "ata Source=\somepath\mydb.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;" & _
                   "ata Source=\somepath\expenses.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;" & _
                   "river={Microsoft Access Driver (*.mdb)};" & _
                   "bq=\somepath\mydb.mdb;" & _
                   "Uid=Admin;" & _
                   "Pwd=;"

For SQL Server:

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

Подробнее см.: Microsoft OLE DB Provider for ODBC

 OLE DB Provider for Oracle (from Microsoft)

oConn.Open "Provider=msdaora;" & _
                   "ata 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;" & _
                   "ata Source=MyOracleDB;" & _
                   "User Id=carl;" & _
                   "Password=;"

For a Trusted Connection:

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

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

Примечание: "ata 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;" & _
                   "ata Source=carl2;" & _
                   "Initial Catalog=pubs;" & _
                   "User Id=sa;" & _
                   "Password=;"

For a Trusted Connection:

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

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

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

To Prompt user for username and password:

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

To connect via an IP address

oConn.Open "Provider=sqloledb;" & _
                   "ata 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;" & _
                   "SN=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;" & _
                   "ata Source=\somepath\mydb.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;" & _
                   "ata Source=MyAdvworksConn;"

The corresponding entry in the \winnt\Msdfmap.ini file would be:

[connect MyAdvworksConn]
Access = ReadWrite
Connect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "ata 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;" & _
                   "SN=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;" & _
                   "ata 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;" & _
                   "ata Source=MyPubsConn;"

The corresponding entry in the \winnt\Msdfmap.ini file would be:

[connect MyPubsConn]
Access = ReadWrite
Connect = "Provider=SQLOLEDB;" & _
                "Network Library=DBMSSOCN;" & _
                "ata 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