Refresh linked tables through code.

[Sajid]

Hi,

I'm using MS Access XP (mdb) with SQL Server as the
backend. The tables are connected using linked tables with
System DSN.

I'm working on a code which refreshes the linked table
based on the dsn settings. Here is the function.

' strLinkName is the name of the table as see in MS Access
' strDBName is the database name in SQL Server 2000
' strTableName is the name with the dbo. prefix
' strDSNname is the system DSN name

Public Function LinkTableDAO( _
strLinkName As String, _
strDBName As String, _
strTableName As String, _
strDSNname As String) As Boolean

' Links or re-links a single table.
' Returns True or False based on Err value.

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

On Error Resume Next
Set db = CurrentDb
' if the link already exists, delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
' Found an existing tabledef.
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' No existing tabledef.
' Ignore error and reset.
Err.Clear
End If
' Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
' Set the Connect and SourceTableName
' properties to establish the link
tdf.Connect = _
"ODBC;Database=" & strDBName _
& ";DSN=" & strDSNname _
& ";Integrated Security=True"
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
db.TableDefs.Append tdf

LinkTableDAO = (Err = 0)
End Function


This function works really great except one problem. When
I open the table strLinkName after running this function,
I still see the old data which was tied of my older DSN.
However when I close the MS Access front end and open it
again, the table shows the new data.

Please help.

Thanks,

Sajid

 

[Douglas J. Steele]

While it really shouldn't be necessary, try putting in a tdf.RefreshLink
statement.

 

[Sajid]

Nope that still doesn't work. While googling I found an
article which says that MS Access caches the schema
information even after we refresh the links and that might
be one of the possible reasons. If that is the case, then
how can i "flush" the cache?

Please help.

TIA

Sajid

 

[Rick Brandt]

Nope that still doesn't work. While googling I found an
article which says that MS Access caches the schema
information even after we refresh the links and that might
be one of the possible reasons. If that is the case, then
how can i "flush" the cache?

Delete the links and recreate them rather than refreshing them.

 

[Sajid]

I'm deleting and recreating as you can see through the
code with no success.

 

[Rick Brandt]

I'm deleting and recreating as you can see through the
code with no success.

Sorry, I focused too much on the subject line rather than the content.

That sort of code has always worked for me.

 

[Sajid]

This is what I've found. Initially my DSN (named 'MAS')
was linked to server 'TESTSERVER'. 'TESTSERVER' is, as
name implies is a test server. I want to connect my access
front end to a live server named 'FLASH'. What I did is I
configured my DSN (MAS) to point to 'FLASH' instead
of 'TESTSITE'. I then used to code to refresh the links
progmatically. The code worked 'find' without any errors.
When I opened one of the tables in MS Access, it still
shows data attached to my old server ('TESTSITE'). However
if i close and open my front end, I can see the new data
from 'FLASH' (Strange).

I did some research and found out that code is perfectly
fine. The problem occurs because the DSN name is the same.
If I change the DSN name (from 'MAS' to 'NewMAS',
where 'MAS' was connected to 'TESTSITE' and 'NewMAS' was
connected to 'FLASH') then it does refresh the tables with
the data from the 'FLASH'.

My question is why doesn't MS Access refresh the data in
the tables when the DSN name remains the same (even though
the DSN was modified to point to a new server)?

Any help would be appreciated ...

Thanks,

Sajid

PS : In the above mentioned code, I'm deleting the old
link table and recreating the new linked tables

 

[Sajid]

Hi,

If working with file DSN, then there are no issues. Looks
like they have closed this question .. my question is
still widely open. Any response is appreciated.

Sajid