LE DB Provider for Active Directory Service
oConn.Open "Provider=ADSDSOObject;" &_
"User Id=myUsername;" &_
"Password=myPassword"
For more information, see: Microsoft OLE DB Providerfor Microsoft Active Directory Service
To view Microsoft KBarticles related to Data Link File, click here
--------------------------------------------------------------------------------
OLEDB Provider for Advantage
oConn.Open "Provider=Advantage OLEDB Provider;" &_
"Data source=c:\myDbfTableDir;" &_
"ServerType=ADS_LOCAL_SERVER;" &_
"TableType=ADS_CDX"
For more information, see: Advantage OLE DB Provider (forADO)
--------------------------------------------------------------------------------
OLEDB Provider for AS/400 (from IBM)
oConn.Open"Provider=IBMDA400;" &_
"Data source=myAS400;" &_
"User Id=myUsername;" &_
"Password=myPassword"
For more information,see: A Fast Path to AS/400Client/Server
--------------------------------------------------------------------------------
OLEDB Provider for AS/400 and VSAM (from Microsoft)
oConn.Open"Provider=SNAOLEDB;" &_
"Data source=myAS400;" &_
"User Id=myUsername;" &_
"Password=myPassword"
For more information, see: ConnectionString Property
To view Microsoft KB articlesrelated to OLE DB Provider for AS/400 and VSAM, click here
--------------------------------------------------------------------------------
OLEDB Provider for Commerce Server
For DataWarehouse
oConn.Open "Provider=Commerce.DSO.1;" &_
"Data Source=mscop://InProcConn/Server=mySrvName:" &_
"Catalog=DWSchema:Database=myDBname:" &_
"User=myUsername:Password=myPassword:" &_
"FastLoad=True"
' Or
oConn.Open"URL=mscop://InProcConn/Server=myServerName:" &_
"Database=myDBname:Catalog=DWSchema:" &_
"User=myUsername:Password=myPassword:" &_
"FastLoad=True"
For Profiling System
oConn.Open "Provider=Commerce.DSO.1;" &_
"DataSource=mscop://InProcConn/Server=mySrvName:" &_
"Catalog=Profile Definitions:Database=myDBname:" &_
"User=myUsername:Password=myPassword"
'Or
oConn.Open_
"URL=mscop://InProcConnect/Server=myServerName:" &_
"Database=myDBname:Catalog=Profile Definitions:" &_
"User=myUsername:Password=myPassword"
For more information,see: OLE DB Provider for Commerce Server, DataWarehouse, andProfiling System
To view Microsoft KB articles related to OLEDB Provider for Commerce Server, click here
--------------------------------------------------------------------------------
OLEDB Provider for DB2 (from Microsoft)
For TCP/IP connections
oConn.Open = "Provider=DB2OLEDB;" &_
"Network Transport Library=TCPIP;" & _
"Network Address=xxx.xxx.xxx.xxx;" &_
"Initial Catalog=MyCatalog;" &_
"Package Collection=MyPackageCollection;" &_
"Default Schema=MySchema;" &_
"User ID=MyUsername;" &_
"Password=MyPassword"
For APPC connections
oConn.Open= "Provider=DB2OLEDB;" & _
"APPC Local LU Alias=MyLocalLUAlias;" & _
"APPC Remote LU Alias=MyRemoteLUAlias;" & _
"Initial Catalog=MyCatalog;" &_
"Package Collection=MyPackageCollection;" &_
"Default Schema=MySchema;" &_
"User ID=MyUsername;" &_
"Password=MyPassword"
For more information, see:ConnectionString Property, and Q218590
To view Microsoft KBarticles related to OLE DB Provider for DB2, click here
--------------------------------------------------------------------------------
OLEDB Provider for DTS Packages
TheMicrosoft OLE DB Provider for DTS Packages is a read-only provider thatexposes Data Transformation Services Package Data SourceObjects.
oConn.Open = "Provider=DTSPackageDSO;" &_
"Data Source=mydatasource"
For more information,see: OLE DB Providers Tested with SQL Server
Toview Microsoft KB articles related to OLE DB Provider for DTS Packages,click here
--------------------------------------------------------------------------------
OLEDB Provider for Exchange
oConn.Provider ="EXOLEDB.DataSource"
oConn.Open = "http://myServerName/myVirtualRootName"
Formore information, see: Exchange OLE DB Provider, Messaging,Calendaring, Contacts, and Exchange using ADO objects
To view Microsoft KB articles related to OLE DB Provider forExchange, click here
--------------------------------------------------------------------------------
OLEDB Provider for Excel
Currently Excel does not have an OLE DBProvider.
However, you can use the ODBC Driver for Excel.
Or use the OLE DB Provider for JET to read and write data
in an Excel workbook.
--------------------------------------------------------------------------------
OLEDB Provider for Index Server
oConn.Open "Provider=MSIDXS;"&_
"Data source=MyCatalog"
Formore information, see: Microsoft OLE DB Provider for Microsoft IndexingService
To view Microsoft KB articles related to OLE DBProvider for Index Server, click here
--------------------------------------------------------------------------------
OLEDB Provider for Internet Publishing
oConn.Open"Provider=MSDAIPP.DSO;" &_
"Data Source=http://mywebsite/myDir;"& _
"User Id=myUsername;" &_
"Password=myPassword"
' Or
oConn.Open"URL=http://mywebsite/myDir;" & _
"User Id=myUsername;" &_
"Password=myPassword"
For more information, see: Microsoft OLEDB Provider for Internet Publishing and Q245359
Toview Microsoft KB articles related to OLE DB Provider for InternetPublishing, click here
--------------------------------------------------------------------------------
OLEDB Provider for Microsoft Jet
For standardsecurity
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" &_
"Password="
If using a Workgroup (SystemDatabase)
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;"&_
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:System Database=MySystem.mdw",_
"myUsername", "myPassword"
Note, remember to convert both theMDB and the MDW to the 4.0
database format when using the 4.0OLE DB Provider.
If MDB has a database password
oConn.Open"Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=c:\somepath\mydb.mdb;" & _
"Jet OLEDB:Database Password=MyDbPassword",_
"myUsername", "myPassword"
If want to open up the MDBexclusively
oConn.Mode =adModeShareExclusive
oConn.Open"Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=c:\somepath\myDb.mdb;" &_
"User Id=admin;" &_
"Password="
If MDB is located on a network share
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=\\myServer\myShare\myPath\myDb.mdb"
If MDB is located on a remote machine
- Or use an XMLWeb Service via SOAP Toolkit or ASP.NET
- Or upgrade to SQLServer and use an IP connection string
- Or use an ADO URLwith a remote ASP web page
- Or use a MS Remote or RDSconnection string
If you don't know the path to the MDB (using ASP)
<% ' ASP server-sidecode
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & Server.MapPath(".") & "\myDb.mdb;"&_
"User Id=admin;" &_
"Password="
%>
Thisassumes the MDB is in the same directory where the ASP page is running.Also make sure this directory has Write permissions for the useraccount.
If you don't know the path to the MDB (usingVB)
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=" & App.Path & "\myDb.mdb;" &_
"User Id=admin;" &_
"Password="
This assumes the MDB is in the same directorywherethe application is running.
For more information, see: OLE DB Provider for MicrosoftJet, Q191754, and Q225048
Note: Microsoft.Jet.OLEDB.3.51 only gets installed by MDAC2.0. Q197902
Note: MDAC 2.6 and 2.7 do not containany of the JET components. Q271908 and Q239114
To view Microsoft KB articles related to OLE DB Provider forMicrosoft JET, click here
You can also open an ExcelSpreadsheet using the JET OLE DB Provider
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &_
"Data Source=c:\somepath\mySpreadsheet.xls;" &_
"Extended Properties=""Excel 8.0;HDR=Yes"""
Where "HDR=Yes"means that there is a header row in the cell range
(or namedrange), so the provider will not include the first row ofthe
selection into the recordset. If "HDR=No", thenthe provider will include
the first row of the cell range (ornamed ranged) into the recordset.
For more information, see: Q278973
You canalso open a Text file using the JET OLE DB Provider
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\;" & _
"Extended Properties=""text;HDR=Yes;FMT=Delimited"""
'Thenopen 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
--------------------------------------------------------------------------------
OLEDB Provider for Microsoft Project
oConn.Open"Provider=Microsoft.Project.OLEDB.9.0;" &_
"Project Name=c:\somepath\myProject.mpp"
For more information,see: Microsoft Project 2000 OLE DB ProviderInformation
To view Microsoft KB articles related to OLE DB Provider forMicrosoft Project, click here
--------------------------------------------------------------------------------
OLEDB Provider for mySQL
oConn.Open "Provider=MySQLProv;"&_
"Data Source=mySQLDB;" &_
"User Id=myUsername;" &_
"Password=myPassword"
For more information,see: API - OLE DB andSnippet
--------------------------------------------------------------------------------
OLE DB Provider for ODBC Databases
WARNING: This OLE DB Provider is considered obsolete byMicrosoft!
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
To view Microsoft KB articles related to OLE DB Provider forODBC, click here
--------------------------------------------------------------------------------
OLEDB Providerfor OLAP Services
Microsoft OLE DB for Online AnalyticalProcessing (OLAP) is a set of
objects and interfaces thatextends the ability of OLE DB to provide
access tomultidimensional data stores.
For ADOMD.Catalog
oCat.ActiveConnection =_
"Provider=MSOLAP;" &_
"Data Source=myOLAPServerName;" &_
"Initial Catalog=myOLAPDatabaseName"
For ADOMD.Catalog (with URL)
oCat.ActiveConnection =_
"Provider=MSOLAP;" &_
"Data Source=http://myServerName/;" &_
"Initial Catalog=myOLAPDatabaseName"
For Excel PivotTable
WithActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "OLEDB;" &_
"Provider=MSOLAP;" &_
"Location=myServerDataLocation;" &_
"InitialCatalog=myOLAPDatabaseName"
.MaintainConnection = True
.CreatePivotTable TableDestination:=Range("A1"),_
TableName:= "MyPivotTableName"
End With
For more information, see: OLE DB for OLAP, CatalogObject, PivotTable, Connecting Using HTTP
To view Microsoft KB articles related to OLE DB Provider forOLAP Services, click here
--------------------------------------------------------------------------------
OLEDB Provider for Oracle (from Microsoft)
oConn.Open"Provider=msdaora;" &_
"Data Source=MyOracleDB;" & _
"User Id=myUsername;" &_
"Password=myPassword"
For more information, see: Microsoft OLEDB Provider for Oracle
To view Microsoft KB articles related to OLE DB Provider forOracle, click here
--------------------------------------------------------------------------------
OLEDB Provider for Oracle (from Oracle)
For StandardSecurity
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 knownto the naming method in use. For example, for Local Naming, it is thealias in the tnsnames.ora file; for Oracle Names, it is the Net8Service Name.
For more information, see: Oracle Provider for OLE DBDeveloper's Guide
--------------------------------------------------------------------------------
OLEDB Provider for Pervasive
oConn.Open"Provider=PervasiveOLEDB;" &_
"Data Source=C:\PervasiveEB"
For more information,see: OLE DB -ADO
--------------------------------------------------------------------------------
OLEDB Provider for Simple Provider
TheMicrosoft OLE DB Simple Provider (OSP) allows ADO to access any datafor which a provider has been written using the OLE DB Simple ProviderToolkit. Simple providers are intended to access data sources thatrequire only fundamental OLE DB support, such as in-memory arrays orXML documents.
OSP in MDAC 2.6 has been enhanced to support openinghierarchicalADO Recordsets over arbitrary XML files. These XML files may containthe ADO XML persistence schema, but it is not required. This has beenimplemented by connecting the OSP to the MSXML2.DLL, thereforeMSXML2.DLL or newer is required.
oConn.Open "Provider=MSDAOSP;" &_
"Data Source=MSXML2.DSOControl.2.6"
oRS.Open "http://WebServer/VirtualRoot/MyXMLFile.xml",oConn
Formore information, see: Microsoft OLE DB Simple Provider andQ272270
To view Microsoft KB articles related to OLE DB Provider forSimple Provider, click here
--------------------------------------------------------------------------------
OLEDB Provider for SQLBase
oConn.Open "Provider=SQLBaseOLEDB;"&_
"Data source=mySybaseServer;" &_
"Location=mySybaseDB;" &_
"User Id=myUserName;" &_
"Password=myUserPassword"
For more information, see: Books on-line
-------------------------------------------------------------------------------
OLEDB 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"
oConn.Open"Provider=sqloledb;" &_
"Data Source=myServerName\myInstanceName;" &_
"Initial Catalog=myDatabaseName;" &_
"User Id=myUsername;" &_
"Password=myPassword"
Note: In order to connect to a SQLServer 2000 "named instance", you must have MDAC 2.6 (or greater)installed.
To Prompt user for username and password
oConn.Provider ="sqloledb"
oConn.Properties("Prompt") =adPromptAlways
oConn.Open "Data Source=myServerName;"&_
"Initial Catalog=myDatabaseName"
To connect to SQL Server running on the samecomputer
oConn.Open "Provider=sqloledb;" &_
"Data Source=(local);" &_
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" &_
"Password=myPassword"
To connect to SQL Server running on a remote computer (via anIP address)
oConn.Open "Provider=sqloledb;" &_
"Network Library=DBMSSOCN;" &_
"Data Source=xxx.xxx.xxx.xxx,1433;" &_
"Initial Catalog=myDatabaseName;" &_
"User ID=myUsername;" &_
"Password=myPassword"
Where:
- "NetworkLibrary=DBMSSOCN" tells OLE DB to use TCP/IP ratherthan
Named Pipes (Q238949)
-xxx.xxx.xxx.xxx is an IP address
- 1433 is the default portnumber for SQL Server. Q269882 and Q287932
- You canalso add "Encrypt=yes" for encryption
For more information, see: Microsoft OLE DB Provider for SQLServer
To view Microsoft KB articles related to OLE DB Provider forSQL Server, click here
--------------------------------------------------------------------------------
OLEDB Provider for SQL Server via SQLXMLOLEDB
TheSQLXMLOLEDB provider is an OLE DB provider that exposes the MicrosoftSQLXML functionality through ADO. The SQLXMLOLEDB provider is not arowset provider; it can only execute commands in the "write to anoutput stream" mode of ADO.
oConn.Open "Provider=SQLXMLOLEDB.3.0;" & _
"Data Provider=SQLOLEDB;" &_
"Data Source=mySqlServerName;" &_
"Initial Catalog=myDatabaseName;" &_
"User Id=myUserName;" &_
"Password=myUserPassword"
For more information, see: SQLXML 3.0 and A Survey of Microsoft SQL Server 2000 XML Features
To view Microsoft KB articles related to OLE DB Provider forSQL Server via SQLXMLOLEDB, click here
--------------------------------------------------------------------------------
OLEDB Provider for Sybase Adaptive Server Anywhere (ASA)
oConn.Open "Provider=ASAProv;" &_
"Data source=myASA"
For more information, see: ASAProgramming Interfaces Guide and ASA User'sGuide
--------------------------------------------------------------------------------
OLEDB Provider for Sybase Adaptive Server Enterprise (ASE)
oConn.Open "Provider=Sybase ASE OLE DB Provider;" &_
"Data source=myASEServer"
' Or
oConn.Open"Provider=Sybase.ASEOLEDBProvider;" &_
"Srvr=myASEServer,5000;" &_
"Catalog=myDBName;" &_
"User Id=myUserName;" &_
"Password=myUserPassword"
Where:
- The Sybase ASE OLEDB provider from the Sybase 12.5 client CD
- 5000 is the portnumber for Sybase.
Note: The Open Client 12 Sybase OLE DB Provider fails to workwithout creating a Data Source .IDS file using the SybaseDataAdministrator. These .IDS files resemble ODBC DSNs.
Note: With Open Client 12.5, the server port number featurefinallyworks, allowing fully qualified network connection strings to be usedwithout defining any .IDS Data Source files.
For more information, see: Opening Sybasedatabases
--------------------------------------------------------------------------------
OLEDB Provider for Text Files
Actuallythere is no OLE DB Provider for Text files. However, you canuse theOLE DB Provider for JET to read and write data in Text files. Or youcan use the ODBC Driver forText.
--------------------------------------------------------------------------------
OLEDB Provider for UniData and UniVerse
oConn.Open"Provider=Ardent.UniOLEDB;" &_
"Data source=myServer;" &_
"Location=myDatabase;" &_
"User ID=myUsername;" &_
"Password=myPassword"
For more information, see: IBM UsingUniOLEDB, Informix Using UniOLEDB5.2
--------------------------------------------------------------------------------
OLEDB Provider for Visual FoxPro
oConn.Open "Provider=vfpoledb;"& _
"Data Source=C:\vfp8\Samples\Data\myVFPDB.dbc;" & _
"Mode=ReadWrite|Share Deny None;" & _
"Collating Sequence=MACHINE;" & _
"Password=''"
TheVisual FoxPro OLE DB Provider is not installed by MDAC 2.x. Youmust install Visual FoxPro or download the OLE DB Provider
For more information, see: Microsoft OLE DB Provider forVisual FoxPro
To view Microsoft KB articles related to OLE DBProvider for Visual FoxPro, click here.
Microsoft
Most Valuable Professional
(MVP)
Web Site Hosting
SharePointHosting
Questions or comments about this web site, please sendemail to: WebMaster or Feedback
Copyright © 1997 - 2004 Able Consulting,Inc. Terms Of Use
Last Modified: Sunday,June 20, 2004 03:50:40 PM
(0) 
(0)
