ODBC Link

Question

Hi,

I have excel sheet VBA to fetch data in MS access.Below is the connection string info. When  qt.Refresh is executed it displays the message "General ODBC Error". Intially the connection info was set for ms access2003 to make it work with 2007 I have added *.accdb. Could you please help with this.

qt.Connection = newConnStrf

    newConnStr = _

    "ODBC;" & _

    "DBQ=" & dbsFilePathForDataRefresh & dataSourceMSAccessFileName & ";" & _

    "DefaultDir=" & Left(dbsFilePathForDataRefresh, Len(dbsFilePathForDataRefresh) - 1) & ";" & _

    "Driver={Microsoft Access Driver (*.mdb,*.accdb)};" & _

    "DriverId=25;" & _

    "FIL=MS Access;" & _

    "MaxBufferSize=2048;" & _

    "MaxScanRows=8;" & _

    "PageTimeout=5;" & _

    "SafeTransactions=0;" & _

    "Threads=3;" & _

    "UserCommitSync=Yes;"                   'form the new conn str w/ latest path & db name

Answer 1

Some questions:

Did the query work when you recorded the macro?

What error do you get?

Are you running the macro on the same machine as when you recorded it?

Answer 2

Still i am gettng error after using these strings

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _

        "ODBC;DSN=MS Access Database;DBQ=C:\Users\arju13\Documents\Database5.accdb;DefaultDir=C:\Users\arju13\Documents;DriverId=25;FIL=MS Ac" _

        ), Array("cess;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1" _

        )).QueryTable

        .CommandText = Array( _

        "SELECT Table1.ID" & Chr(13) & "" & Chr(10) & "FROM C:\Users\arju13\Documents\Database5.accdb.Table1 Table1" _

        )

        .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

Answer 3

Post the macro you recorded ( if it contains anything).

Answer 4

When I record a macro, I couldn't find a connection string like this. Could you please help

Answer 5

I would record a macro while setting up a new connection from Excel 2007. That way you can double-check if the connect string is OK.