Database on network

[skydiver]

I'm using Access 2003. I've recently created a database that is currently being stored on a network. Our network consists of several drives. On the same drive is a "Backup" folder with a backup of the database. When utilizing the database (i.e. updating) I'm noticing the file automatically doubles in size upon saving. It is not doing this in the backup folder. I've tried compacting and repairing, it will reduce the size, however goes back to double the size automatically after the fact. My question is: Why is the file doubling in size?

 

[The_Doc_Man]

Exactly doubling or approximately doubling?

 

[boblarson]

Is this database a SPLIT database? (see here for why it is important to split, if it isn't already split.)

 

[skydiver]

Doc Man,

Approximately doubling. Bob Larson - I'll be reading your article.

 

[Simon_MT]

One of the things you have to realise is the objects are stored in a blob 'binary large object", in the older versions only individual objects would be changed, now the entire blob is written back. I totally agree with Bob FE/BE solution but you will still get bloating on the FE when you change something. Nothing a compact won't sort out.

Simon

 

[skydiver]

One of the things you have to realise is the objects are stored in a blob 'binary large object", in the older versions only individual objects would be changed, now the entire blob is written back. I totally agree with Bob FE/BE solution but you will still get bloating on the FE when you change something. Nothing a compact won't sort out.

Simon

So are you saying a compact should suffice Simon? Splitting the database sounds scary to me.

 

[boblarson]

So are you saying a compact should suffice

Heaven's NO.

Splitting the database sounds scary to me.

Losing everything should be scarier to you.

It is simple to split.

Make a copy of the database and save it before doing anything.

Then:

1. Make another copy
2. Take the original and delete everything but the tables and put that on the network file share (change the name to have something like BE to identify that it is the backend file).
3. In the extra copy, delete the tables.
4. Using FILE > GET EXTERNAL DATA > LINK TABLES and navigate to the copy which has only the tables.
5. select all of the tables
6. Click OK

There, you've just split your database and you can give a copy
of the frontend to any of the users and it should work fine.

 

[skydiver]

Heaven's NO.

Losing everything should be scarier to you.

It is simple to split.

Make a copy of the database and save it before doing anything.

Then:

1. Make another copy
2. Take the original and delete everything but the tables and put that on the network file share (change the name to have something like BE to identify that it is the backend file).
3. In the extra copy, delete the tables.
4. Using FILE > GET EXTERNAL DATA > LINK TABLES and navigate to the copy which has only the tables.
5. select all of the tables
6. Click OK

There, you've just split your database and you can give a copy
of the frontend to any of the users and it should work fine.

Okay...I'm about to ask a really stupid question...please excuse me. So, the reason for the splitting is to prevent the doubling of the file size or is this also a protection of data remedy?

 

[boblarson]

Okay...I'm about to ask a really stupid question...please excuse me. So, the reason for the splitting is to prevent the doubling of the file size or is this also a protection of data remedy?

It depends on what you are doing in the database as to the file size doubling. If you run a bunch of make table queries, or delete objects and create new ones, etc. it can bloat.

The splitting may help in that only the part which is really having the issue will bloat and you can possibly fix that. But if you don't split then Corruption is very, very likely. It isn't IF it will but a matter of WHEN it will. If a user is in a non-split database and there is a lost packet on the network or a network disruption (no matter how brief) it can totally hose your database. If it is split and those happen then the single user might have their frontend go belly up but you can then just give them a new copy (and others aren't forced to wait while you fix it for them, whereas if the whole thing goes down then NOBODY can do their work).

 

[skydiver]

It depends on what you are doing in the database as to the file size doubling. If you run a bunch of make table queries, or delete objects and create new ones, etc. it can bloat.

The splitting may help in that only the part which is really having the issue will bloat and you can possibly fix that. But if you don't split then Corruption is very, very likely. It isn't IF it will but a matter of WHEN it will. If a user is in a non-split database and there is a lost packet on the network or a network disruption (no matter how brief) it can totally hose your database. If it is split and those happen then the single user might have their frontend go belly up but you can then just give them a new copy (and others aren't forced to wait while you fix it for them, whereas if the whole thing goes down then NOBODY can do their work).

So, I've just completed a successful split. I have the original file, the BE file, and the FE file. Which file should we be using now? Is the original file considered the master file?

 

[boblarson]

So, I've just completed a successful split. I have the original file, the BE file, and the FE file. Which file should we be using now? Is the original file considered the master file?

The original unsplit file was just there for safety while you get the other up and running. Once you do, you can archive it somewhere or get rid of it (I usually save it somewhere but where nobody can use it).

You need to keep a copy of the frontend as a master copy where you will make all of your changes. When you make changes you then can stick a COPY of the master where it can be downloaded by users to their machine, or you can use a batch file on their desktop to open up the database and within that batch file you can just copy a new frontend down to them everytime. OR you can use an auto update utility like the one referenced in my signature (Access MVP Tony Toews also has a good autoupdater).

So, the backend should already be where it needs to be and if it isn't you will need to put it there and then open the frontend while holding the shift key down and then go to the Linked Table Manager and relink the tables.