Code to extract data into spreadsheet not working 3

[47redlands]

Hello I am trying to connect to a MS Access database using Excel.

I have put the database on the D drive of my PC. I am using Access 2007 and Excel 2007. The database is located here and is called:

D:\test.accdb. The table's name is tbDataSumproduct and the three columns(fields) containing the data are Month, Product and City

Sub proSQLQuery1()
Dim varConnection
Dim varSQL

       Range("A1").CurrentRegion.ClearContents

       varConnection = "ODBC; DSN=MS Access Database;DBQ=C:\test.accdb; Driver={Driver do Microsoft Access (*.accdb)}"

       varSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City FROM        tbDataSumproduct"

       With ActiveSheet.QueryTables.Add(Connection:=varConnection, Destination:=ActiveSheet.Range("A1"))
               .CommandText = varSQL
               .Name = "Query-39008"
               .Refresh BackgroundQuery:=False
       End With

End Sub

---------------------------
ODBC Microsoft Access Driver Login Failed
---------------------------
Unrecognized database format 'D:\test.accdb'.
---------------------------
OK Cancel
---------------------------


Also this occurs Run time error '-2147217842 (80040e4e)': Automation error



Why is it not working?

Note I have not put a username and password but it is also requesting on

What am i do wrong?

 

[PaulDalyTT]

I think the problem is with your connection string.. Not 100% sure what you need to set it to, but this example below works with Exel & Access 2003.
note sure of Excel/Access 2007.

It may be easier to use the query builder in excel to generate the quiery for you. You can then either refresh it each time the sheet opens or via a VBA macro which you can control in your own way... (Date -> Import external data -> New Database Query)

Sub Access_Data()
     'Requires reference to Microsoft ActiveX Data Objects xx Library
     
    Dim Cn As ADODB.Connection, Rs As ADODB.Recordset
    Dim MyConn, sSQL As String
     
    Dim Rw As Long, Col As Long, c As Long
    Dim MyField, Location As Range
     
     'Set destination
    Set Location = [A1]
     'Set source
    MyConn = "D:\test.accdb"
     'Create query
    sSQL = "SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City FROM tbDataSumproduct;"
     
     'Create RecordSet
    Set Cn = New ADODB.Connection
    With Cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open MyConn
        Set Rs = .Execute(sSQL)
    End With
     
     'Write RecordSet to results area
    Rw = Location.Row
    Col = Location.Column
    c = Col
    Do Until Rs.EOF
        For Each MyField In Rs.Fields
            Cells(Rw, c) = MyField
            c = c + 1
        Next MyField
        Rs.MoveNext
        Rw = Rw + 1
        c = Col
    Loop
    Set Location = Nothing
    Set Cn = Nothing
End Sub

 

[bubba100]

Using the Macro recorder in Excel:
Then Data>From other Sources-Get External Data> From Microsoft Query

Range("A1").CurrentRegion.ClearContents
'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\My Documents\Test.accdb;DefaultDir=C:\My Documents;DriverId=25;FIL=MS Access;MaxBufferSize=2048;P" _
), Array("ageTimeout=5;")), Destination:=Range("$A$1")).QueryTable
' .CommandText = Array( _
"SELECT tbDataSumproduct.Month, tbDataSumproduct.Product, tbDataSumproduct.City" & Chr(13) & "" & Chr(10) & "FROM `C:\My Documents\Test.accdb`.tbDataSumproduct tbDataSumproduct")
.CommandText = Array( _
"SELECT * FROM `C:\My Documents\Test.accdb`.`tbDataSumproduct`")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With

 

[SkipVought]

What happens when you IMPORT EXTRENAL DATA... on your sheet WITHOUT VBA?

Do you get a connection and can you execute your query?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]