Ïðèìåðû ïîäêëþ÷åíèÿ ðàçëè÷íûõ Áàç Äàííûõ ÷åðåç ADO
Äàòà ïóáëèêàöèè: 07/07/2003
Êàòåãîðèÿ: Áàçû äàííûõ
Âåðñèÿ äëÿ ïå÷àòè Àâòîð: 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=\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 "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb;" & _
"Uid=Admin;" & _
"Pwd=;"
Åñëè èñïîëüçóåòñÿ Workgroup (System database):
oConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.mdb;" & _
"SystemDB=\somepath\mydb.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=\somepath\mySpreadsheet.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:\sqlany50\mydb.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=\somepath\mySourceDb.dbc;" & _
"Exclusive=No;"
Áåç database container (Free Table Directory):
oConn.Open "Driver={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;" & _
"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=\somepath\mydb.mdb;" & _
"User Id=admin;" & _
"Password=;"
If you are using a workgroup (system database):
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data 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;" & _
"Data 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;" & _
"Data 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;" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=\somepath\mydb.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=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 "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=\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;" & _
"Data Source=MyAdvworksConn;"
The corresponding entry in the \winnt\Msdfmap.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 \winnt\Msdfmap.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
Ñòàòüè ïî òåìå:
