Question about ADO and Access  
Author Message
cohnerap





PostPosted: Visual Basic for Applications (VBA), Question about ADO and Access Top

Hi,

I've been searching the net for the last 3 hours for a good example of querying access database from excel and then manipulating the recordset, this isn't as easy as I expected.

Can anyone offer an idea (I'm not even sure how to open the database).

Thank you all (and sorry for the silly question),

Raphael



Microsoft ISV Community Center Forums1  
 
 
ADG





PostPosted: Visual Basic for Applications (VBA), Question about ADO and Access Top

The below is from the Office Help System, you don't need to worry about the Pivot Table bit. Once you have the recordset you can work with it from there.

This example creates a new PivotTable cache using an ADO connection to Microsoft Jet, and then it creates a new PivotTable report based on the cache, at cell A3 on the active worksheet.

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
  .ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0"
  .Open "C:\perfdate\record.mdb"
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
  .CommandText = "Select Speed, Pressure, Time From DynoRun"
  .CommandType = adCmdText
  .Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' Create a PivotTable cache and report.
Set objPivotCache = ActiveWorkbook.PivotCaches.Add( _
  SourceType:=xlExternal)
Set objPivotCache.Recordset = rstRecordset
With objPivotCache
  .CreatePivotTable TableDestination:=Range("A3"), _
    TableName:="Performance"
End With

With ActiveSheet.PivotTables("Performance")
  .SmallGrid = False
  With .PivotFields("Pressure")
    .Orientation = xlRowField
    .Position = 1
  End With
  With .PivotFields("Speed")
    .Orientation = xlColumnField
    .Position = 1
  End With
  With .PivotFields("Time")
    .Orientation = xlDataField
    .Position = 1
  End With
End With

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordSet = Nothing
Set cnnConn = Nothing