ending a connection between the front and back ends of a split database

[Paul]

I've got a split Access 2003 database where the back end is on a shared
network drive and the front ends are on the users' C drives. I've noticed
that sometimes the back end mdb file thinks there is an open connection with
one or more of the front ends, even after those front end files have been
closed. When this occurs, several things are evident:

-- The ldb file for the back end does not close;
-- When I open that ldb file with Notepad it displays the ID numbers of the
computers that previously had a connection but subsequently closed, and
-- the back end mdb file will not permit any changes to the tables, and will
not allow me to run the Compact and Repair Database command. Instead, it
displays a message that the file is opened exclusively by another user, even
when I'm the only one that has the front end open, and
-- it won't even let me delete, rename or overwrite the back end mdb file,
again stating that it's being used by another user or program.

I realize that the MS knowledge base has articles to the effect that network
interruptions or front end crashes can cause this, but we haven't
experienced either of those events.

Two questions:
1. What can I do to convince the back end file that no one has it open
(other than me), so that I can modify tables and/or Compact and Repair the
file?
2. What can I do to prevent this from occurring in the future? I'm using
the VBA command DoCmd.Quit to close the front end databases. Is there some
additional VBA code I can add to that procedure so it will let the back end
know that we're closing down the connection?

Thanks in advance,

Paul

 

[david]

1. What can I do to convince the back end file that no one has it open

(other than me), so that I can modify tables and/or Compact and Repair the

1) Wait a couple of minutes. A network optimisation caches open
connections in case the user wants to re-open.

2) Come in early in the morning. Windows Server will eventually
delete broken connections. It knows that connections are broken
if they are held by a computer/user that is not connected. It takes
a while to find and delete these broken connections, and the user/computer
has to be disconnected.

3) Explicitly break the open connections. (Administrative Tools |Computer
Management | Shared Folders | Open Files)

4) Apply all server patches.

5) Turn off the BITS service. Disable file caching. Disable Browse updates.
Remove Admin permissions from the users. Disable Task Manager in
group policy.

(david)

 

[Paul]

My thanks to David and Tom for their suggestions and insights into this
issue. I've copied their responses below since both of them don't show up
in all of the newsgroups in this post.

Paul


1) Wait a couple of minutes. A network optimisation caches open
connections in case the user wants to re-open.
2) Come in early in the morning. Windows Server will eventually
delete broken connections. It knows that connections are broken
if they are held by a computer/user that is not connected. It takes
a while to find and delete these broken connections, and the user/computer
has to be disconnected.
3) Explicitly break the open connections. (Administrative Tools |Computer
Management | Shared Folders | Open Files)
4) Apply all server patches.
5) Turn off the BITS service. Disable file caching. Disable Browse updates.
Remove Admin permissions from the users. Disable Task Manager in
group policy.

(david)


Re 1: Reboot the server. Or if you're lucky: Control Panel >
Administrative Tools > Computer Management > Shared Folders > Open
Files.
Re 2: Live with it. It's going to happen occasionally. I think this is
more a Windows issue than an Access issue.

Tom. - Microsoft Access MVP

 

[Tony Toews [MVP]]

-- When I open that ldb file with Notepad it displays the ID numbers of the
computers that previously had a connection but subsequently closed,

This is normal behavior although your root cause is not normal. If
during the day you have 25 users in the ldb file and at the end of the
day you still have one user in the mdb then you will see all 25 users
in the ldb file. Jet does not clean up the "slots" in the ldb file.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Paul Ponzelli]

Thanks for that additional insight, Tony.

Now that you have pointed it out, that matches my experience with the ldb
file.

Paul