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

Forget it, the logic described in the example works...

My mistake was that I was just using different versions of Access files that
had some differences in field names, which caused the QueryTables to get
confused...


"alexhatzisavas" wrote:


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