ADO connection

[Yves]

Any help would greatly be appreciated.

I am trying to understand ADO connections and finding very little info and help. I am setting up Excel Spreadsheet and need to import some information from Access Database VBA to manipulate data.

I found this piece of code from a site but I now get the following error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Added a reference to the Microsoft ActiveX Data Object x.x library.

Sub CreatePivotTableFromDB()
    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim DBFile As Variant
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("PivotSheet").Delete
    On Error GoTo 0
    
    'Create a Pivot Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    
    'Connect to database and do query
    DBFile = ThisWorkbook.Path & "\budget.mdb"
    ConString = "ODBC;DSN=MS Acess Database;DBQ=" & DBFile

    QueryString = "SELECT * FROM BUDGET"
    With PTCache
        .Connection = ConString
        .CommandText = QueryString
    End With
    
    'add new worksheet
    Worksheets.Add
    ActiveSheet.Name = "PivotSheet"
    
    'create pivot table
    Set PT = PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"), TableName:="BudgetPivot")
    
    'add fields
    With PT
        .PivotFields("DEPARTMENT").Orientation = xlRowField
        .PivotFields("MONTH").Orientation = xlColumnField
        .PivotFields("DIVISION").Orientation = xlPageField
        .PivotFields("BUDGET").Orientation = xlDataField
        .PivotFields("ACTUAL").Orientation = xlDataField
    End With
End Sub

 

[nIGHTmAYOR]

now this proves my theory, nobody knows ado and its all about tailoring heritage of ms access 97 dao scripts.
Best regards and sorrow yves

 

[boblarson]

Check this site out which might help:

http://www.connectionstrings.com/

 

[LPurvis]

To follow on from Bob's suggestion...
Your connection string just needs to be formed in an appropriate way for your scenario.

You can maintain the ODBC connection as suggested in the article (though side step the DSN requirement)..
ConString = "ODBC;Driver={Microsoft Access Driver (*.mdb)};DBQ=" & DBFile

However it's often preferable to go with an OLEDB connection
ConString = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & DBFile

Excel will create all the data connections for you based on the connection string you supply. There is no requirement for you to have an ADO object reference added to your project. You can remove it again (unless you're using early bound ADO code in some other capacity).


<aside>
nIGHTmAYOR - I really don't know what you're saying here - other than perhaps attempting to continue your thread from here, but I don't see how this really relates to anything. Or what you feel it "proves".
</aside>

Cheers.