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

"Jonathan" <(E-Mail Removed)> wrote in message
news:6BC348F7-2ACA-41CA-BC3A-(E-Mail Removed)
> 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.

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

(please reply to the newsgroup)

Hi Dirk, thanks for your response.

"Dirk Goldgar" wrote:

> "Jonathan" <(E-Mail Removed)> wrote in message
> news:6BC348F7-2ACA-41CA-BC3A-(E-Mail Removed)
> > 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.
>
> --
> 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

"Jonathan" <(E-Mail Removed)> wrote in message
news:2334A639-2A34-4026-810D-(E-Mail Removed)
>
> 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.

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

(please reply to the newsgroup)