Changing Query Source Location?
 

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!

Changing Query Source Location?
 

-----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.

Changing Query Source Location?
 

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:\D ata;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:\D ata;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 wrote in message
...
-----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.

Changing Query Source Location?
 

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...
-----Original Message-----
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!
.

Changing Query Source Location?
 

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.

Changing Query Source Location?
 

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" wrote in message
...
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.

Changing Query Source Location?
 

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

Changing Query Source Location?
 

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" wrote in message
...
Thank you!!! That was the missing piece - it works
perfectly.