Linking and disconnecting from backend databases

[cmd]

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 IN EVIDENTLY DOES NOT FULLY
RELEASE OR CLOSE C:\ACCESS97\FPSDATA.MDB, EVEN AFTER LINKING TO THE
SECOND DATABASE.

DO I NEED TO SET 'dbs' AND 'Tdfs' TO NOTHING SOMEWHERE ALONG THE WAY
--AND THEN RE-SET THEM PRIOR TO DOING ANOTHER TABLE-LINK?

THANK YOU.
MARK.

[Arno R]

<cm*@mountain.net> schreef in bericht news:11*********************@o13g2000cwo.googlegro ups.com...

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:

<snip code >

Hi Mark,
IMO it's a bit unusual to replace the backend. Mostly we replace the Frontend don't we ??
If you replace the backend, well why would you do so ? You will lose all new data ??
Is this some kind of restore procedure ??

==>> IMO there is NO need to link to another db to replace the file.
To replace the backend this is what I think you need:
You need to make sure there is no 'active' connection; no open recordsets,
And also: No open form with bound controls !

I just tested:
I Opened one of the Access 97 applications I have: (fe-be).
My main menu opens (unbound)
From this menu I click on a button with code like:

Private Sub Button_Click()
FileCopy "C:\ArraCom\Klavier\kla_be.bak", "C:\ArraCom\Klavier\kla_be.mdb"
End Sub

This works and replaces the backend.
If I also have opened one of the bound forms then: No go (error: access refused)
If I close the bound form again: Code works OK

--
Hope this helps
Arno R

[cmd]

Thanks Arno,
This involves updating a user's home computer with data from the
office. The "parent" records for the backend database are based on
client-id numbers (autonumber), with a lot of different types of
"child" records associated with each client-id number. The user does
not add "parent" records, but does create and edit "child" records on
their home computer.

The task is to allow the user to retain their edited/created "child"
records on their home computer, while updating their backend in order
to receive any new "parent" records added at the main office.

Therefore, I was using the utility database to first link to the user's
backend database to retrieve any edited/created "child" records of
theirs. After replacing their backend database with the newer office
copy, the retrieved "child" records are appended, based on matching
client-id numbers. (Actually, any MATCHING "child" records are adjusted
for edits; then any unmatching "child" records are appended).

I've re-thought the approach, however; now I simply open the utility
database and before refreshing any links, use FileCopy to create a
Temp.mdb of their existing backend database. Then I can simply replace
their database with the office version, refresh links to the Temp.mdb
and retrieve (and later append) the user's "child" records from that
database. This avoids refreshing links to the "real" backend databases,
which interferes with the copy process.