Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access97 Pass-through-Query

Posted on 2012-03-13
9
Medium Priority
?
448 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

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.

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.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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