By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,275 Members | 1,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,275 IT Pros & Developers. It's quick & easy.

Strange behavior with Dev Ashish's fRefreshLinks function (from Access Web)

P: n/a
Hi all,

(WinXP Pro SP2, Access 2003)

I'm using Dev Ashish's fRefreshLinks function from AccessWeb
(http://www.mvps.org/access/tables/tbl0009.htm) to relink tables in my
front end to one of several back end files.

When I first dropped in the function I was getting an error on one of
my tables saying "Couldn't relink table, table doesn't exist"... or
something similar, for one of my tables. Of course, this wasn't the
case, the table exists, and in fact could be relinked using the Linked
Table Manager in Access. After a few hours of confusion, I deleted the
linked table, reimported it, then reran Dev Ashish's code. This worked,
and the function, continues to work at this time.

My question: any thoughts on why this happened and what can be done to
prevent it in the future.

Within the fRefreshLinks function, this is where the error originates:

If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
Err.Raise cERR_NOREMOTETABLE
End If
End If

fIsRemoteTable(dbLink, strTbl) takes you to the following:

Function fIsRemoteTable(dbRemote As Database, strTbl As String) As
Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (Err = 0)
Set tdf = Nothing
End Function

Which returns False, thus putting you back into Err.Raise
cERR_NOREMOTETABLE, and thusly the error.

Now to be perfectly clear, I don't completely understand all of the
code in the functions, although I have the Access 2002 Developer's
Handbook and I'm attempting to figure it all out :D.

Anyhow, any thoughts would be greatly appreciated on why this happened
and how to prevent it from happening in the future would be
appreciated.

Best,

Kelii

Jan 26 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Kelii, is this a secured database (i.e. permissions controlled by an MDW)?
If so, the message may mean that the user did not have permission to set the
properties.

It could also fail if there were a deleted linked table.
You could avoid that with:
With tdfLocal
If Not tdfLocal.Name Like "~*" Then
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End If
End With

Acess 2000 and later cache much more information about the linked tables
than used to be the case when Dev first wrote that. There are cases where
the cached info can cause problems. You could probably avoid those problems
(and the deleted table issue above) by compacing the database, and then
trying to link again.

The cached table info can even cause problems after a successful relink.
Compacting after the relink also can avoid those problems.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kelii" <ke****@yahoo.comwrote in message
news:11**********************@q2g2000cwa.googlegro ups.com...
Hi all,

(WinXP Pro SP2, Access 2003)

I'm using Dev Ashish's fRefreshLinks function from AccessWeb
(http://www.mvps.org/access/tables/tbl0009.htm) to relink tables in my
front end to one of several back end files.

When I first dropped in the function I was getting an error on one of
my tables saying "Couldn't relink table, table doesn't exist"... or
something similar, for one of my tables. Of course, this wasn't the
case, the table exists, and in fact could be relinked using the Linked
Table Manager in Access. After a few hours of confusion, I deleted the
linked table, reimported it, then reran Dev Ashish's code. This worked,
and the function, continues to work at this time.

My question: any thoughts on why this happened and what can be done to
prevent it in the future.

Within the fRefreshLinks function, this is where the error originates:

If fIsRemoteTable(dbLink, strTbl) Then
'everything's ok, reconnect
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
Err.Raise cERR_NOREMOTETABLE
End If
End If

fIsRemoteTable(dbLink, strTbl) takes you to the following:

Function fIsRemoteTable(dbRemote As Database, strTbl As String) As
Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (Err = 0)
Set tdf = Nothing
End Function

Which returns False, thus putting you back into Err.Raise
cERR_NOREMOTETABLE, and thusly the error.

Now to be perfectly clear, I don't completely understand all of the
code in the functions, although I have the Access 2002 Developer's
Handbook and I'm attempting to figure it all out :D.

Anyhow, any thoughts would be greatly appreciated on why this happened
and how to prevent it from happening in the future would be
appreciated.

Best,

Kelii
Jan 27 '07 #2

P: n/a

Allen,

Thanks for the reply.

The database isn't secured at all, so permissions shouldn't be an
issue.

As far as the deleted table is concerned, that's a great add to the
code, I'll definately use it.

So, the problem must be the cached info as you suggest. I did not try
compacting the database back end / front end; in fact I did very little
other than stare at my laptop in bewilderment and randomly flip through
my Access library. Next time, I'll do as you suggest, compact the files
and see if that works.

Again, thanks for the reply; and btw, I use your MVP site frequently,
you've provided a great service to Access novices such as myself.

Kelii

Jan 27 '07 #3

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:45***********************@per-qv1-newsreader-01.iinet.net.au:
Acess 2000 and later cache much more information about the linked
tables than used to be the case when Dev first wrote that. There
are cases where the cached info can cause problems. You could
probably avoid those problems (and the deleted table issue above)
by compacing the database, and then trying to link again.

The cached table info can even cause problems after a successful
relink. Compacting after the relink also can avoid those problems.
And sometimes the links have to be completely deleted and recreated
to get rid of certain types of cached data that cause extreme
performance degradation.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 27 '07 #4

P: n/a
"Kelii" <ke****@yahoo.comwrote in
news:11**********************@q2g2000cwa.googlegro ups.com:

[To Allen Browne:]
Again, thanks for the reply; and btw, I use your MVP site
frequently, you've provided a great service to Access novices such
as myself.
Not just to novices, Keri -- I use Allen's site almost daily and I
do this for a living and have been doing so for 10+ years.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jan 27 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.