Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access97 Pass-through-Query

Posted on 2012-03-13
9
Medium Priority
?
445 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!
0
Comment
Question by:SOTA
  • 5
  • 4
9 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 37716401
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
0
 

Author Comment

by:SOTA
ID: 37717005
Thanks OM Gang.
I have Access97 and the command qdf.RefreshLink is not valid.
Any thoughts?
Thanks!
0
 
LVL 28

Expert Comment

by:omgang
ID: 37717049
Try qdf.Refresh
Let me know if it still doesn't work.
OM Gang
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:SOTA
ID: 37717077
There is no QueryDef.Refresh

however,

QueryDefs.Refresh is valid.

:)
0
 
LVL 28

Expert Comment

by:omgang
ID: 37717094
Does CurrentDb.QueryDefs.Refresh successfully update the connection string for the pass-through query?
OM Gang
0
 

Author Comment

by:SOTA
ID: 37717170
Yes!! That's it!!

Perfect...thanks!!

Cheers,
Russ :)
0
 

Author Closing Comment

by:SOTA
ID: 37717171
Awesome!!!!!
0
 
LVL 28

Expert Comment

by:omgang
ID: 37717173
I tried it in Access 2007 and it works - the pass-through connection string is updated.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 37717184
You're welcome.
OM Gang
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question