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