Hi all.

I'm trying to dynamically direct some XL QueryTables to the 'correct' MS
Access database (local on C:\ ,  or on network drive).

The XL file with the QueryTables may be executed either on a network drive
or on my hard drive.

To achieve this, i'm constructing the full path name to the Access database
based on ThisWorkbook.Path.

So depending on which version of the XL file is being executed (network or
local), the variable that holds the path to the Access database
(strAppAccFullName) is constructed accordingly.

Then i'm using this variable (strAppAccFullName) to define the ODBC string
of the QueryTables.

***

Dim strConnODBC As String
Dim QryTbl as QueryTable

   ' By dynamically re-creating this string each time, we ensure that the
XL queries
   ' are directed to the correct database (local or online).
   ' Note that the DB path is included in the string as a variable
(strAppAccFullName).

   strConnODBC = _
       "ODBC;" _
       & "DSN=MS Access Database;" _
       & "DBQ=" _
       & strAppAccFullName & ";" _
       & "DefaultDir=" _
       & strAppAccFullName & ";" _
       & "DriverID=25;" _
       & "FIL=MS Access;" _
       & "MaxBufferSize=2048;" _
       & "PageTimeout=5;"
       
   intCnt = 0
   For Each ws In ThisWorkbook.Worksheets
       intCnt = intCnt + 1
       ws.Activate
       
       Set QryTbl = ActiveSheet.QueryTables(1)
       With QryTbl
           .Connection = strConnODBC
           .Refresh BackgroundQuery:=False
       End With
       Set QryTbl = Nothing
   Next

***

The problem is i get an error when i run the local XL (the network XL runs
ok).
The error is: 1004 - General ODBC error.

The funny thing is that when i manually redirect a specific QueryTable that
generates an error to the 'correct' Access database, and then compare the
Connection string of the 'adapted' QueryTable with what is generated above,
the strings are identical...

Any insights / tips on this one?

Many thanks,
Alex