We help IT Professionals succeed at work.

Access97 Pass-through-Query

SOTA
SOTA asked
on
Medium Priority
512 Views
Last Modified: 2012-03-13
I wish to use a pass-through-query in Access97 to speed up retrieving a recordset from an SQL server.

I can setup the parameters for the ODBC Connection String by opening up Properties for the pass-through-query in Access97.

Question is, how do I open the pass-through-query and programatically supply the ODBC Connection String needed to connect to the SQL server?

High points for a speedy answer!
Comment
Watch Question

IT Manager
CERTIFIED EXPERT
Commented:
Public Function GetQueryLinks()

On Error GoTo Err_GetQueryLinks

    Dim qdf As DAO.QueryDef
    
    For Each qdf In CurrentDb.QueryDefs
        If qdf.Connect <> "" Then
            Debug.Print qdf.Connect
        End If
    Next
    
Exit_GetQueryLinks:
    Set qdf = Nothing
    Exit Function

Err_GetQueryLinks:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure GetQueryLinks of Module basTableLinks"
    Resume Exit_GetQueryLinks

End Function

Open in new window

This public function will retrieve the connection string for your pass-through queries.  A simple modification to then change/update the connection string.

qdf.Connect = "the new connection string"
qdf.RefreshLink

OM Gang

Author

Commented:
Thanks OM Gang.
I have Access97 and the command qdf.RefreshLink is not valid.
Any thoughts?
Thanks!
omgangIT Manager
CERTIFIED EXPERT

Commented:
Try qdf.Refresh
Let me know if it still doesn't work.
OM Gang

Author

Commented:
There is no QueryDef.Refresh

however,

QueryDefs.Refresh is valid.

:)
omgangIT Manager
CERTIFIED EXPERT

Commented:
Does CurrentDb.QueryDefs.Refresh successfully update the connection string for the pass-through query?
OM Gang

Author

Commented:
Yes!! That's it!!

Perfect...thanks!!

Cheers,
Russ :)

Author

Commented:
Awesome!!!!!
omgangIT Manager
CERTIFIED EXPERT

Commented:
I tried it in Access 2007 and it works - the pass-through connection string is updated.
OM Gang
omgangIT Manager
CERTIFIED EXPERT

Commented:
You're welcome.
OM Gang

Explore More ContentExplore courses, solutions, and other research materials related to this topic.