Replacing backend database

[FPS, Romney]

I use a utility database and the following code to link from an
original backend to a temporary backend, in order to replace the
original with a newer version:

Dim dbs As Database
Dim tdf As TableDef
Dim Tdfs As TableDefs
Dim Pathname As String
Set dbs = CurrentDb
Set Tdfs = dbs.TableDefs

'LINKING TO THE ORIGINAL BACKEND
Pathname = "C:\Access97\fpsdata.mdb"
For Each tdf In Tdfs
On Error Resume Next
If tdf.SourceTableName <> "" Then
tdf.Connect = ";DATABASE=" & Pathname
tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table

'LINKING TO A TEMPORARY BACKEND:

Pathname = "C:\Transfer\fpsdata.mdb"
For Each tdf In Tdfs
On Error Resume Next
If tdf.SourceTableName <> "" Then
tdf.Connect = ";DATABASE=" & Pathname
tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table

'ATTEMPTING TO REPLACE THE FIRST BACKEND WITH ANOTHER VERSION:
Filecopy "C:\NewVersion\fpsdata.mdb, "C:\Access97\fpsdata.mdb"

-- THE FILECOPY ACTION FAILS TO REPLACE C:\Access97\fpsdata.mdb. THE
PROBLEM IS THAT THE UTILITY DATABASE I'M USING EVIDENTLY DOES NOT FULLY
RELEASE OR CLOSE C:\ACCESS97\FPSDATA.MDB, EVEN AFTER LINKING TO THE
SECOND DATABASE. --WHAT AM I MISSING HERE?

THANK YOU.
MARK.

 

[Douglas J. Steele]

Looks as though it should work.

I'd check for

If Len(tdf.Connect) > 0 Then

rather than

If tdf.SourceTableName <> "" Then

but that's just personal preference.

What happens if you remove the On Error Resume Next and actually trap any
errors that might arise?

BTW, why are you linking to the old database before linking to the new one?
(Or are those two blocks of code not executed one after the other?)

 

[FPS, Romney]

Hi Doug,

I'm linking to the 'old' database in order to retrieve any records from the
user's database (on their home computer) prior to replacing the user's
database with the newer version -- then, appending those records back into
the new database. This is basically a replication situation, I guess.

I've noticed that even though the utility database has its tables already
linked to the 'old' database -- from the last time it was used -- I can
nevertheless delete, copy, and/or rename the 'old' database with no problem.
It's only after I run the code to refresh the links that the permission to
do these things goes away.

I'm rethinking my approach and I may just open the utility database, copy
the 'old' database to another name without refreshing any links, and then
link to that new database to retrieve the user's records. That way I won't
have to refresh the links to the 'old' database until after I replace it
with the newer version.
I still wonder, however, why the FileCopy or Kill commands work when I first
open the utility database -- with its tables already linked to that other
database -- but don't work if I've refreshed the links through code. No
records are opened, i.e., there's no .ldb file created during this part of
the process. Thanks for the "If Len(tdf.Connect) > 0" suggestion.

Mark.