Changing Query Source Location?

[Jennifer Crawford]

Reiteration of problem - I have a query on an Excel
worksheet template which calls an Access database. When i
distribute this, the location of the database will be
different. I need to be able to update the database
location in the query definition somehow.

I tried getting the connection string by using:
?Sheet1.QueryTables("MyQuery").Connection
(obviously modified with my worksheet name, etc.), and
only end up getting Run-time Error 424 - Object required.

I'm new to Excel programming so I am probably missing
something obvious about how to do this, but I'm really
getting stumped and need help. The *only* way around this
that I have found so far is to literally start from a
fresh copy of the template and create the query from
scratch. Since this will be deployed in offices where I'm
not located, I don't want to have to make people there go
in and create queries like this when they are even more
unfamiliar with Excel than I am.

Please help!

[Tom Ogilvy]

Are you sure your query is named MyQuery. I create a query to the employees
table in northwind and this is what I got:

? activesheet.QueryTables(1).Name
ExternalData1

? activesheet.QueryTables(1).Connection
ODBC;DSN=MS Access 97 Database;DBQ=c:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;DefaultDir=c:\Program Files\Microsoft
Office\Office\Samples;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

--
Regards,
Tom Ogilvy

Jennifer Crawford <jcra...@dladamson.com_nospam> wrote in message
news:072801c36059$d5351ed0$a101...@phx.gbl...

[Jennifer Crawford]

>-----Original Message-----
>Are you sure your query is named MyQuery?

Actually, my query has no name at all, so using the number
instead of the name worked much better. That at least
allowed me to get the connection string - which is one
step further than where I was before!

So.

Using some code Rob Rutherford gave me as a sample
earlier, I tried the following to reset the connection:

Function ChangePath()
Dim str As String
str = "ODBC;DSN=MS Access Database;DBQ="
str = str & "C:\Jennifer\ProjectOrganizer.mdb;"
ActiveSheet.QueryTables(1).Connection = str
End Function

This function compiles with no errors.

However, when I try to run it from the Immediate window, I
get this error:

"Compile error: Expected = "

And obviously, it doesn't update the connection.

I'm not sure where this should even be called, quite
frankly, or how to set it up.

[Tom Ogilvy]

I made a copy of northwind.mdb and copied to c:\data directory. Then using
these routines and I could change back and forth between the two copies as
the source for my query table.

Sub ChangeDataSource()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection
Debug.Print "===================="
.Connection = Application.Substitute( _
.Connection, _
"c:\Program Files\Microsoft Office\Office\Samples", _
"c:\Data")

Debug.Print .Connection
.Refresh
End With
End Sub

Sub ChangeDataSource1()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection
Debug.Print "===================="
.Connection = Application.Substitute( _
.Connection, _
"c:\Data", _
"c:\Program Files\Microsoft Office\Office\Samples")

Debug.Print .Connection
.Refresh
End With
End Sub

Sample printout in Debug/immediate window:

ODBC;DSN=MS Access 97 Database;DBQ=c:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;DefaultDir=c:\Program Files\Microsoft
Office\Office\Samples;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

====================
ODBC;DSN=MS Access 97
Database;DBQ=c:\Data\Northwind.mdb;DefaultDir=c:\Data;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

---------------------------------------
Run the second routine to change back
---------------------------------------

ODBC;DSN=MS Access 97
Database;DBQ=c:\Data\Northwind.mdb;DefaultDir=c:\Data;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;
====================

ODBC;DSN=MS Access 97 Database;DBQ=c:\Program Files\Microsoft
Office\Office\Samples\Northwind.mdb;DefaultDir=c:\Program Files\Microsoft
Office\Office\Samples;DriverId=281;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;

--
Regards,
Tom Ogilvy

Jennifer Crawford <jcra...@dladamson.com_nospam> wrote in message

news:07c801c36062$a75e1c10$a101...@phx.gbl...

[Pete Tringale]

I am grappling with this same issue.
I have done this in the Workbook_Open sub:

Private Const strPath As String = Environ("TEMP")
Private Const gstrConnect As String = _
"ODBC;Driver={Microsoft Text Driver (*.txt; *.csv)};" _
& "Dbq=" & strPath & ";DefaultDir=" & strPath & _
& ";Extensions=asc,csv,tab,txt;"

Private Sub Workbook_Open()
Dim objSheet As Excel.Worksheet
Dim objQuery As Excel.QueryTable

For Each objSheet In Application.Worksheets
With objSheet
For Each objQuery In .QueryTables
With objQuery
.Connection = gstrConnect
End With
Next objQuery
End With
Next objSheet
Me.RefreshAll
End Sub

Hope this helps.

Pete...

>.
>

[Jennifer Crawford]

Both of these functions definitely seem to work - when I
run either of them, yes, I do see that Connection is
printed as if it is pointing to my new test database.

But then, just to test, I renamed the *old* database, and
now it fails. No matter that according to the Connection
string I am pointing to the new database, it is still
trying to point to the old database.

I'm not sure what it is I'm doing wrong.

[Tom Ogilvy]

You have to change the SQL property as well. I thought the connection would
cascade to the SQL, but apparently not:

Sub ChangeDataSource()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection

Debug.Print .Sql

Debug.Print "===================="
.Connection = Application.Substitute( _
.Connection, _

"C:\Program Files\Microsoft Office\Office\Samples", _
"c:\Data")
.Sql = Application.Substitute( _
.Sql, _
"C:\Program Files\Microsoft Office\Office\Samples", _
"c:\Data")

Debug.Print .Connection
Debug.Print .Sql

.Refresh
End With
End Sub

Sub ChangeDataSource1()
With ActiveSheet.QueryTables(1)
Debug.Print .Connection

Debug.Print .Sql

Debug.Print "===================="
.Connection = Application.Substitute( _
.Connection, _
"c:\Data", _

"C:\Program Files\Microsoft Office\Office\Samples")
.Sql = Application.Substitute( _
.Sql, _
"c:\Data", _
"C:\Program Files\Microsoft Office\Office\Samples")

Debug.Print .Connection
Debug.Print .Sql

.Refresh
End With
End Sub

--
Regards,
Tom Ogilvy

"Jennifer Crawford" <jcra...@dladamson.com_nospam> wrote in message

news:106501c360ec$8a046cc0$a501...@phx.gbl...

[Jennifer Crawford]

Thank you!!! That was the missing piece - it works
perfectly.

[R. Choate]

Just curious - How is the performance? Is it fast? Also, have you looked at
the DSN-less connection alternative with ADO?
Thanks,
--
RMC,CPA

"Jennifer Crawford" <jcra...@dladamson.com_nospam> wrote in message

news:02b801c360fe$1bfd2340$a301...@phx.gbl...

[Jennifer Crawford]

>-----Original Message-----
>Just curious - How is the performance? Is it fast? Also,
>have you looked at the DSN-less connection alternative
>with ADO?

I muddled my way through setting up the query in the first
place and have no idea how to use a different sort of
connection (I'm really not very conversant in Excel,
unfortunately). The performance seems to be extremely
quick, even when the database is on a shared server. It's
a one-time extract so even if it took a few seconds I
doubt this would be an issue.