How to Refresh a Linked Query?

[DraguVaso]

Hi,

I have some Access-applications (Access 97) that contains several Linked
Tables and Linked query's. The password is saved in the links.

I now want to do a Refresh of my Tables and Query's, so the application asks
the user on startup for the right ODBC Login and Password to link the Linked
Tables and Linked query's.

For the Linked Tables tihs is simple: I just use the Linked Table Manager
(Tools - Options) and do a refresh there. But I can't find a similar thing
for the Linked Query's, and they keep the old Login and Password.

Does anybody knows a solution for this problem?

Thanks,

Pieter

 

[Brian]

Hi,

I have some Access-applications (Access 97) that contains several Linked
Tables and Linked query's. The password is saved in the links.

I now want to do a Refresh of my Tables and Query's, so the application asks
the user on startup for the right ODBC Login and Password to link the Linked
Tables and Linked query's.

For the Linked Tables tihs is simple: I just use the Linked Table Manager
(Tools - Options) and do a refresh there. But I can't find a similar thing
for the Linked Query's, and they keep the old Login and Password.

Does anybody knows a solution for this problem?

Thanks,

Pieter

What's a linked query? I've never heard of such a thing.

 

[DraguVaso]

Well:
You have Linked Tables in an Access Database: those are Tables that are
originally on an SQL Server or another Datbase, and that you link, so you
can use them in your access-application. They have a little green globe as
icon.

The linked query's are Query's that use Linked Tables, but they use directly
the Table on the SQL Server, not the Linked table in tha Access-application.
hey too have a green globe as icon...

The problem is: When you refresh for exemple the Linked Tables to point to
an other SQL Server or with a different Login/Password, the Linked Query's
keep on using the old ones...

Any idea how to find a solution?

Thanks,

Pieter

 

[Brian]

Well:
You have Linked Tables in an Access Database: those are Tables that are
originally on an SQL Server or another Datbase, and that you link, so you
can use them in your access-application. They have a little green globe as
icon.

The linked query's are Query's that use Linked Tables, but they use directly
the Table on the SQL Server, not the Linked table in tha Access-application.
hey too have a green globe as icon...

The problem is: When you refresh for exemple the Linked Tables to point to
an other SQL Server or with a different Login/Password, the Linked Query's
keep on using the old ones...

Any idea how to find a solution?

Thanks,

Pieter

Oh I see, you mean a pass-through query. What you need to do is to update
each query's connect property. A bit of code like this would probably do
it, where some_linked_table has already had it's link changed:

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim strConnect As String

Set db = CurrentDb
strConnect = "ODBC;" & db.TableDefs("some_linked_table").Connect

For Each qry In db.QueryDefs
If qry.Connect <> "" Then qry.Connect = strConnect
Next

Set qry = Nothing
Set db = Nothing

 

[DraguVaso]

Ok yhanks!
But how can I do this that the access application doesn't store the
password, but that it asks on every startup the username and login, and redo
the linking/pass through?

 

[david epsom dot com dot au]

Note, queries can also have a SourceDatabase property
in the properties sheet if there is SQL like this:
"select * from [odbc;dsn=mydsn].tblA"
Unlike the connect property of 'passthrough' queries
(the ones with the green icons), this property is not
available in vb code: you need to update the SQL of
the query to change queries that are linked that way.

(david)

 

[Brian]

Ok yhanks!
But how can I do this that the access application doesn't store the
password, but that it asks on every startup the username and login, and redo
the linking/pass through?

I don't really understand what you are trying to achieve. You say you will
use the Linked Table Manager to refresh the links to the tables, so it's
hardly going to be a semi-automated or user-friendly procedure, is it?

The code I gave you can be run from anywhere once the links to the tables
have been updated. It will not prompt for a user name or password, because
it sneakily steals the connection information from one of the linked tables.