Solved

Access97 Pass-through-Query

Posted on 2012-03-13
9
433 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 500 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
 

Author Comment

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

however,

QueryDefs.Refresh is valid.

:)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now