Changing Query source location?
 

I have set up an Excel worksheet so that it pulls data
from an Access database when the user fills out two cells.

When I distribute this worksheet, the location for the
Access database will have changed.

Is there a way for me to somehow programatically update
the location for the Query source (database) without
having to manually go recreate the query to point to the
new source?

I should note that I have tried to actually manually
overwrite the path name for the database in the Query SQL
window, but this still gives me errors.

Thanks!

Jennifer Crawford

Changing Query source location?
 

Download Flexfind from this site..

http://www.bmsltd.co.uk/MVP/MVPPage.asp

If you want to do it with VBA you'll have to do s'thing similar to
following:

Sub ChangeQtSource()
Dim qTbl As QueryTable
Dim sCon As String
Dim sOld As String
Dim sNew As String

sOld = "c:\databases"
sNew = "d:\shared\databases"

Set qTbl = ActiveSheet.QueryTables(1)
sCon = qTbl.Connection
WorksheetFunction.Substitute sCon, sOld, sNew
qTbl.Refresh

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jennifer Crawford" wrote:

I have set up an Excel worksheet so that it pulls data
from an Access database when the user fills out two cells.

When I distribute this worksheet, the location for the
Access database will have changed.

Is there a way for me to somehow programatically update
the location for the Query source (database) without
having to manually go recreate the query to point to the
new source?

I should note that I have tried to actually manually
overwrite the path name for the database in the Query SQL
window, but this still gives me errors.

Thanks!

Jennifer Crawford

Changing Query source location?
 

The path of the Access database you are using as your data
source is stored in the query table's Connection property.
Print this in your Immediate window by adapting and
executing the following line. This example applies to a
a query table called "MyQuery" on Sheet1.

?Sheet1.QueryTables("MyQuery").Connection

Youl should see something like the following (ignoring everything
after the DBQ parameter).

ODBC;DSN=MS Access Database;DBQ=C:\db1.mdb;

To point to a different database you just need to change the full
path in the DBQ parameter. Example code for doing this might be:

Dim str As String
str = "ODBC;DSN=MS Access Database;DBQ="
str = str & ThisWorkbook.Path & "\Copy of db1.mdb;"
Sheet1.QueryTables("MyQuery").Connection = str

Regards,
Rob Rutherford

"Jennifer Crawford" wrote in message ...
I have set up an Excel worksheet so that it pulls data
from an Access database when the user fills out two cells.

When I distribute this worksheet, the location for the
Access database will have changed.

Is there a way for me to somehow programatically update
the location for the Query source (database) without
having to manually go recreate the query to point to the
new source?

I should note that I have tried to actually manually
overwrite the path name for the database in the Query SQL
window, but this still gives me errors.

Thanks!

Jennifer Crawford

Changing Query source location?
 

oops!

just reviewing my own code... and I fear I forgot to update
the actual Connection string..

but if you'd do following you might get a result... sorry!

Set qTbl = ActiveSheet.QueryTables(1)
sCon = qTbl.Connection
qTbl.Connection = WorksheetFunction.Substitute(sCon, sOld, sNew)
qTbl.Refresh



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


keepitcool wrote:

Download Flexfind from this site..

http://www.bmsltd.co.uk/MVP/MVPPage.asp

If you want to do it with VBA you'll have to do s'thing similar to
following:

Sub ChangeQtSource()
Dim qTbl As QueryTable
Dim sCon As String
Dim sOld As String
Dim sNew As String

sOld = "c:\databases"
sNew = "d:\shared\databases"

Set qTbl = ActiveSheet.QueryTables(1)
sCon = qTbl.Connection
WorksheetFunction.Substitute sCon, sOld, sNew
qTbl.Refresh

End Sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Jennifer Crawford" wrote:

I have set up an Excel worksheet so that it pulls data
from an Access database when the user fills out two cells.

When I distribute this worksheet, the location for the
Access database will have changed.

Is there a way for me to somehow programatically update
the location for the Query source (database) without
having to manually go recreate the query to point to the
new source?

I should note that I have tried to actually manually
overwrite the path name for the database in the Query SQL
window, but this still gives me errors.

Thanks!

Jennifer Crawford