I have a small database that is split into frontend and backend. The
frontend uses the "Reconnect" function by Peter Vukovic to connect to
the backend if it is in the same folder.

From the frontend I can overwrite the backend with an updated backend
file. I am doing this by closing all forms that are bound to backend
tables, running a vbs script that overwrites the backend file and then
I reopen the main bound form.

The problem that I am having is that this form is not seeing the data
from the updated backend file. It still sees the data from the
backend file that has been overwritten. I can overcome this by
closing and then reopening the frontend but this is not my preference.

I assume that if I can break the links to the backend tables and then
relink them, that I will then see the updated data without the need to
close and reopen the frontend. If I am correct in assuming that
breaking and then reinstating the links will give me the desired
outcome, how can I do this?

josephlee (AT) live (DOT) com.au wrote:

Why not disconnect from the tables in the old file first before
reconnecting?

I'm not sure why you'd overwrite the backend ever, but I leave those
decisions to people that do that.

Sub ListTablesConnectStrings()
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.name, 4) <> "MSys" Then
Debug.Print tdf.name & " " & tdf.Connect
End If
Next
dbs.Close
Set dbs = Nothing
MsgBox "done"

End Sub

I would look at Connect and Refreshlink in help. Or view the code at
http://www.mvps.org/access/tables/tbl0009.htm. Look at DeleteObject for
how to remove an object as well.

Duffy
http://www.youtube.com/watch?v=KE2orthS3TQ

On Mar 9, 5:07*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
Thanks. The code you posted did the trick. The logic behind
overwriting the backend may be flawed but it goes like this:

The database is used to manage Contacts in a small office
environment. The Admin person is always connected to the network and
is the only person who can add and edit data (Manager's request). The
Admin backend db is on the network.

The other users work on laptops that are connected to the network when
they are in the office and standalone when they are out of the
office. When in the office the laptop users can click a button in
their database that closes all forms bound to the backend, calls a
script to overwrite their backend db which is local on their laptop
and then reopens the main bound table.

As a side note, you should not actually have to exit the access application.

(you mentioned VBS, but I *think* you mean VBA?????).

Furthermore, if you close all forms and recordsets, then you *can* overwrite
the back end, and you not have to re-link. (and, you not have to exit the
application). I do this all the time.

keep in mind:

If you have a bound form, that *calls* code that closes that bound form,
your backend will STILL be opened. This is because how the program stack
works:

bound form
---------------> call code to close bound form
code to copy backend table
<------------ returns to calling form code

What means is that even though you requested a close form, the code will
RETURN back to the form, and the form is not actually un-loaded
until the code returns back.

In a sense, this means that you must call the code that closes all forms,
and reocrdsets from a UN-BOUND form. I repeat, you must call the code from a
un-bound form. If you do this then will not have to re-link at all....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal (AT) msn (DOT) com

On Mar 9, 7:48*pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal... (AT) msn (DOT) com>
wrote:
Thanks Albert. That clarifies a few things. It is an external vbs
script that I'm using though, not VBA.