|
|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
|
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;"
For more information, see: OLE DB Provider for DB2
and 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;"
For more information, see: 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=myUsername;" & _
"Password=myPassword;"
For more information, see: Microsoft OLE DB Provider for Internet Publishing and Q245359
--------------------------------------------------------------------------------------------------
OLE DB Provider for Microsoft Jet
For standard security:
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password=;"
If using a Workgroup (System Database):
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.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 MDB has a database password:
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDBatabase Password=MyDbPassword;", _
"admin", ""
If MDB is located on a network share:
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\myServer\myShare\myPath\myDb.mdb;
If want to open up the Access database exclusively:
oConn.Mode = adModeShareExclusive
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password=;"
For more information, see: OLE DB Provider for Microsoft Jet, Q191754, Q225048, Q239114, and Q271908
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=c:\somepath\myExcelSpreadsheet.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.
For more information, see: Q278973
You can also open a Text File using the "OLE DB Provider for Microsoft Jet"
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited;"";"
' Then open a recordset based on a select on the actual file
oRs.Open "Select * From MyTextFile.txt", oConn, adOpenStatic, adLockReadOnly, adCmdText
For more information, see: Q262537
----------------------------------------------------------------------------------------------------------
OLE DB Provider for ODBC Databases
For Access (Jet):
oConn.Open "Provider=MSDASQL;" & _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\somepath\mydb.mdb;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"
For SQL Server:
oConn.Open "Provider=MSDASQL;" & _
"Driver={SQL Server};" & _
"Server=myServerName;" & _
"Database=myDatabaseName;" & _
"Uid=myUsername;" & _
"Pwd=myPassword;"
For more information, see: Microsoft OLE DB Provider for ODBC
------------------------------------------------------------------------------------------------------------
OLE DB Provider for Oracle (from Microsoft)
oConn.Open "Provider=msdaora;" & _
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" & _
"Password=myPassword;"
For more information, see: 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=myUsername;" & _
"Password=myPassword;"
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;"
Note: "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.
For more information, see: 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 Simple Provider
The Microsoft OLE DB Simple Provider (OSP) allows ADO to access any data for which a provider has
been written using the OLE DB Simple Provider Toolkit. Simple providers are intended to access data
sources that require only fundamental OLE DB support, such as in-memory arrays or XML document．.
OSP in MDAC 2.6 has been enhanced to support opening hierarchical ADO Recordsets over arbitrary
XML files. These XML files may contain the ADO XML persistence schema, but it is not required. This
has been implemented by connecting the OSP to the MSXML2.DLL, therefore MSXML2.DLL or newer is
required.
oConn.Open "Provider=MSDAOSP;" & _
"Data Source=MSXML2.DSOControl.2.6;"
oRS.Open "http://WebServer/VirtualRoot/MyXMLFile.xml";, oConn
For more information, see: Microsoft OLE DB Simple Provider
----------------------------------------------------------------------------------------
OLE DB Provider for SQL Server
For Standard Security:
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword;"
For a Trusted Connection:
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI;"
To connect to a "Named Instance" (SQL Server 2000)
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName\Inst2;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword;"
To Prompt user for username and password:
oConn.Provider = "sqloledb"
oConn.Properties("Prompt" = adPromptAlways
oConn.Open "Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;"
To connect via an IP address:
oConn.Open "Provider=sqloledb;" & _
"Data Source=xxx.xxx.xxx.xxx,1433;" & _
"Network Library=DBMSSOCN;" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword;"
Note:
- xxx.xxx.xxx.xxx is an IP address
- "Network Library=DBMSSOCN" tells OLE DB to use TCP/IP rather than Named Pipes (Q238949)
- 1433 is the default port number for SQL Server
- You can also add "Encrypt=yes" for encryption
For more information, see: Microsoft OLE DB Provider for SQL Server
==================================================
Remote Data Service (RDS) Connections
The following examples show how to connect to a remote database using the RDS Data Control.
When using the RDS DataControl's Server/SQL/Connect properties, the RDS DataControl uses the
RDS DataFactory on the remote server. If you use the RDS DataControl's URL property,
then the RDS DataFactory is not used at all.
WARNING: The RDS DataFactory can be a major security hole if not setup and configured correctly!
For more information, see RDS FAQ #24
RDS DataControl - Connect Property
With the RDS default handler disabled (not recommend due to security risks):
With oRdc
.Server = "http://carl2";
.Sql = "Select * From Authors Where State = 'CA'"
.Connect = "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=pubs;" & _
"User Id=sa;" & _
"Password=;"
.Refresh
End With
With the RDS default handler enabled (recommend):
With oRdc
.Server = "http://carl2";
.Handler = "MSDFMAP.Handler"
.Connect = "Data Source=MyConnectTag;"
.Sql = "MySQLTag(""CA"""
.Refresh
End With
The corresponding CONNECT and SQL sections in the default handler \WINNT\MSDFMAP.INI file would be:
[connect MyConnectTag]
Access = ReadWrite
Connect = "Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User Id=sa;Password=;"
[sql MySQLTag]
Sql = "Select * From Authors Where State = '?'"
For more information about the RDS Default Handler, see:
Q243245, Q230680, and RDS Customization Handler Microsoft articles
RDS DataControl - URL Property
To get records from a remote database:
With oRdc
.URL = "http://carlp0/Authors_GetByState.asp?state=CA";
.Refresh
End With
To save, set the URL property to an ASP web page:
With oRdc
.URL = "http://carlp0/rdsdatacontrol/Authors_Save.asp";
.SubmitChanges
End With
For more information, see: RDS URL Property |
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2006-5-12 09:50
楼主
