Class OLEDBConnection (Excel VBA)

The class OLEDBConnection represents the OLE DB connection.


Other Methods

SaveAsODC - Saves the OLE DB connection as a Microsoft Office Data Connection file.

Sub UseSaveAsODC() 
 Application.ActiveWorkbook.OLEDBConnection.SaveAsODC ("ODCFile") 
End Sub


ADOConnection returns an ADO connection adoconnection if the PivotTable cache is connected to an OLE DB data source. Read-only.

AlwaysUseConnectionFile true if the connection file is always used to establish a connection to the data source.

BackgroundQuery true if queries for the OLE DB connection are performed asynchronously (in the background).

CalculatedMembers returns the CalculatedMembers collection for the specified connection.

CommandText returns or sets the command string for the specified data source. Read/write Variant.

CommandType returns or sets one of the XlCmdType constants.

Connection returns or sets a string that contains OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source.

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) 
 .Connection = _ 
 "OLEDB;Provider=MSOLAP;Location=srvdata;Initial Catalog=National" 
 .MaintainConnection = True 
 .CreatePivotTable TableDestination:=Range("A3"), _ 
 TableName:= "PivotTable1" 
End With 
With ActiveSheet.PivotTables("PivotTable1") 
 .SmallGrid = False 
 .PivotCache.RefreshPeriod = 0 
 With .CubeFields("[state]") 
 .Orientation = xlColumnField 
 .Position = 0 
 End With 
 With .CubeFields("[Measures].[Count Of au_id]") 
 .Orientation = xlDataField 
 .Position = 0 
 End With 
End With 

EnableRefresh true if the connection can be refreshed by the user. The default value is True.

IsConnected returns True if the MaintainConnection property is True. Returns False if it is not currently connected to its source.

LocalConnection returns or sets the connection string to an offline cube file. Read/write String.

LocaleID returns or sets the locale identifier for the specified connection.

Dim myConnection As OLEDBConnection 
Set myConnection = ThisWorkbook.Connections(1) 
With myConnection 
 .RetrieveInOfficeUILang = False 
 .LocaleID = 3082 
End With

MaintainConnection returns True if the connection to the specified data source is maintained after the refresh operation and until the workbook is closed.

MaxDrillthroughRecords returns or sets the maximum number of records to retrieve.

OLAP returns True if the OLE DB connection is connected to an Online Analytical Processing (OLAP) server.

Parent returns the parent object for the specified object. Read-only.

RefreshDate returns the date on which the OLE DB connection was last refreshed.

Refreshing true if a background OLE DB query is in progress for the specified OLE DB connection.

RefreshOnFileOpen true if the connection is automatically updated each time the workbook is opened. The default value is False.

RefreshPeriod returns or sets the number of minutes between refreshes.

RetrieveInOfficeUILang true if the data and errors are to be retrieved in the Office user interface display language when available.

RobustConnect returns or sets how an OLE DB connection connects to its data source.

SavePassword true if password information in an OLE DB connection string is saved in the connection string. False if the password is removed.

ServerCredentialsMethod returns or sets the type of credentials that should be used for server authentication.

ServerFillColor true if the fill color format for the OLAP server is retrieved from the server when using the specified connection.

ServerFontStyle true if the font style format for the OLAP server is retrieved from the server when using the specified connection.

ServerNumberFormat true if the number format for the OLAP server is retrieved from the server when using the specified connection.

ServerSSOApplicationID returns or sets a single sign-on application (SSO) identifier that is used to perform a lookup in the SSO database for credentials.

ServerTextColor true if the text color format for the OLAP server is retrieved from the server when using the specified connection.

SourceConnectionFile returns or sets a String indicating the Microsoft Office Data Connection file or similar file that was used to create the connection.

SourceDataFile returns or sets a String indicating the source data file for an OLE DB connection.

UseLocalConnection true if the LocalConnection property is used to specify the string that enables Microsoft Excel to connect to a data source. False if the connection string specified by the Connection property is used.

With ActiveWorkbook.PivotCaches(1) 
 .LocalConnection = _ 
 "OLEDB;Provider=MSOLAP;Data Source=C:\Data\DataCube.cub" 
 .UseLocalConnection = True 
End With