How to reset/refresh connection string for pass-through query

E

Emily

I have an Access application that built with a lot of
pass-through queries by using ODBC connection to
manipulate data at sql server. When I deploy it to a
user, I find the connection that being used by the user
is under my name instead of the user if I don't opening
up each individual query in design view and click the
properties and re-make the connection at the user's
computer.

Can someone tell me how I can programmatically
reset/refresh to reflect the actual user's connection
without doing that?

It's because there is a field in a table that set up with
default value of Host_Name() function to capture the
user's computer name. All the records come up with my
computer name instead of the user's name.

Thank you very much for the help.

Emily
 
V

Van T. Dinh

(DAO Library included in the References)

Public Function FixConnStr_ODBCQueries() As Boolean
' gMSSQL_ODBC is the new Connection String you need to supply
'--------
' Author : Van T. Dinh, 19/Apr/2004
'--------
' Revision History
' 19/Apr/2004 (VTD): First-coded
'================

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

On Error GoTo FixConnStr_ODBCQueries_Err
Set db = DBEngine(0)(0)

For Each qdf In db.QueryDefs
If qdf.Type = dbQSQLPassThrough Then
qdf.Connect = gMSSQL_ODBC
End If
Next

FixConnStr_ODBCQueries = True
MsgBox "Connection String for all Pass-through Queries are updated."

FixConnStr_ODBCQueries_Exit:
Set qdf = Nothing
Set db = Nothing
Exit Function

FixConnStr_ODBCQueries_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: vbaFixServerConnection.FixConnStr_ODBCQueries)"
& vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
FixConnStr_ODBCQueries = False
Resume FixConnStr_ODBCQueries_Exit
End Function
*******


If you need to vary the Connection String regularly, use declaration like:

Public Function FixConnStr_ODBCQueries(ServerName As String, _
AddressPort As String, _
DatabaseName As String) As Boolean

and supply the required value to construct the connection String.
 
Top