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

  • Thread starter Thread starter Emily
  • Start date Start date
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
 
(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.
 
Back
Top