>> Refresh links with SQL

[Jonathan]

Hi, Access 2000 linking to SQL2000.

To update database structure I have a procedure that will add columns to a
table (pass-through query).

In sql, I can see the new columns. However, in Access the new fields are not
available until using the linked table manager to refresh the links.

So, how do I use code to refresh links to a sql back end?

Any ideas or suggestions appreciated

Many thanks, Jonathan

 

[Dirk Goldgar]

Hi, Access 2000 linking to SQL2000.

To update database structure I have a procedure that will add columns
to a table (pass-through query).

In sql, I can see the new columns. However, in Access the new fields
are not available until using the linked table manager to refresh the
links.

So, how do I use code to refresh links to a sql back end?

Any ideas or suggestions appreciated

Many thanks, Jonathan

Have you tried calling the RefreshLink method of the DAO TableDef
object?

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set tdf = db.TableDefs("MyLinkedTable")
tdf.RefreshLink

I haven't tested it, but that should work. It's possible you might have
to reassign the tabledef's .Connect property and then call .RefreshLink.

 

[Jonathan]

Hi Dirk, thanks for your response.

Have you tried calling the RefreshLink method of the DAO TableDef
object?

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb
Set tdf = db.TableDefs("MyLinkedTable")
tdf.RefreshLink

I haven't tested it, but that should work. It's possible you might have
to reassign the tabledef's .Connect property and then call .RefreshLink.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I had tried using the refreshlinks method with the tabledef. The solution
was actually to refresh the querydefs collection.

Many thanks, Jonathan

 

[Dirk Goldgar]

I had tried using the refreshlinks method with the tabledef. The
solution was actually to refresh the querydefs collection.

I'm puzzled, but maybe I misunderstood where your problem was coming
from. I'm glad you found a solution.