Multiple database files created

[Ken Warthen]

I have an Access 2007 database that resides on our company's server. The
database tracks incoming and outgoing shipments and is used by several
employees throughout the day. I've noticed that in the folder where the
database resides several additional database files are created throughout the
day with names like database.mdb, database1.mdb, database2.mdb, etc. Can
anyone tell me why this is happening?

The database was created with Access 2007 using the 2003 mdb file format, as
most of the end users have Office 2003 applications.

Ken Warthen
(e-mail address removed)

 

[Jerry Whittle]

1. Turn off Compact on Close.

2. Make double sure that all users have Read, Write, Create, and Delete
privileges to the folder holding the database. Not just the database file but
the entire folder.

Something is going wrong with Compact and Repair. Most likely it's happening
due to someone not having the properly privs to that folder. It's also likely
that it's happening during a Compact or Close or you might have noticed a
problem with a manual Compact and repair.

 

[Ken Warthen]

Jerry,

Thanks for the help. That did the trick.

Ken

 

[John W. Vinson]

I have an Access 2007 database that resides on our company's server. The
database tracks incoming and outgoing shipments and is used by several
employees throughout the day. I've noticed that in the folder where the
database resides several additional database files are created throughout the
day with names like database.mdb, database1.mdb, database2.mdb, etc. Can
anyone tell me why this is happening?

The database probably has "Compact on Close" enabled. When a database is
compacted, Access copies the .mdb file to a new file (in A2003 and before it
was usually db1, db2, db3 etc.) with a number to make the filename unique. It
then deletes the original database and renames the db1 file to the old
filename.

If the user of the database doesn't have full read/write/create/delete
privileges to the folder, though, the process can't complete. Hence you're
getting these copies.

If this is (as appears the case) a multiuser database, you should "split" it
into a backend file containing only the tables, and a frontend containing the
forms, reports, etc.; each user would get a copy of the frontend. I'd strongly
suggest turning OFF "compact on close" on both databases. There's usually no
reason to compact a frontend at all (just replace it), and the backend should
be compacted when nobody's in the database on an appropriate schedule.

John W. Vinson [MVP]

 

[Ken Warthen]

John,

Thanks for the advice. I have split the database into a frontend and
backend, though I only give endusers a shortcut to the frontend database file
which lies in a folder on our server. If I had end users copy the frontend
file to their workstations, I would have to get them to repeat that process
everytime I made a change to the database, which is fairly regularly,
particular with regard to adding new reports. Is there any significant
performance gain, or other reason why this would be worthwhile?

Ken

 

[John W. Vinson]

John,

Thanks for the advice. I have split the database into a frontend and
backend, though I only give endusers a shortcut to the frontend database file
which lies in a folder on our server. If I had end users copy the frontend
file to their workstations, I would have to get them to repeat that process
everytime I made a change to the database, which is fairly regularly,
particular with regard to adding new reports. Is there any significant
performance gain, or other reason why this would be worthwhile?

Well... sharing a single frontend is a pretty risky business, leading to bad
performance, bloating and corruption.

See Tony Toews' site http://www.granite.ab.ca/access/splitapp.htm for a
discussion. He also has a page and a utility which allow frequent updating of
frontends, or you can roll your own.

At the very least each user should have their own copy of the frontend in a
separate folder on the server.

John W. Vinson [MVP]