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