Advice needed - Split/replication

[peddyp]

Hi



I have very little Access experience and have inherited a critical database that was setup by ex employee.
It is accessed by multiple users and is currently around 400MB. It is having constant issues with crashing and some corruption.

The database is split and the backend appears to be replicated. FE and BE currently on network share.

Some questions :

1) No idea why FE are on server, I realise all staff need this on their own local PCs. If we update the FE anytime, whats the best way to control staff having the correct FE. I cant rely on them doing it themselves, as some would no doubt use old FE.

2) I have taken a copy of BE and tested running Compact and repair. The database went from 412MB to 6MB! Is this normal?

3) I have no idea why replication was setup. All users are on same LAN. We are soon to move to Office 2013 and I have read it isnt supported. How easy to remove this?

Any advice would be appreciated.

[orange]

Why are you moving to 2013?
Before doing anything with the current database, I would put effort into understanding it.
You have questions already re FE on server, replication... are you going to track these down and resolve before moving?
Do you have test data? Specifications for this database?
Does it do what it is suppose to do --- for the business?
Reduction in database size is normal with compact and repair. Your numbers seem "phenomenal though".
Whenever edits/mods/deletes etc occur Access uses disk space for his purposes. You get that space back for general use when you compact and repair. So either it's been a long time between C&Rs, or there is something in the BE that gets created/deleted with each session??? Something has to account for the huge space difference.
Do you see any automation in the FE, that would go to the server and look at the "official/latest" FE, compare it to the local copy and download as necessary?
Research/google MSAccess FE auto updater. I know Tony Toews has a version and there are others.
I don't know replication, but don't see why it would be needed if everyone is on the same LAN???
The impending move to 2013 may be enough to get management/users to review current database against requirements.

Good luck.

[peddyp]

thanks very much for the reply.

Office 2013 is a company decision. No urgency on this. I can delay.

The current setup etc is still getting looked at. As Im a novice it takes longer ;-) and I am reluctant to mess about too much.
I thought that size reduction would be out of the ordinary. Maybe it has never been compacted since it was created.

That auto FE updater looks perfect. That could be the answer to that dilemma. Thanks!

As for replication, more research needed on that one. If anyone else has experience in that and how to remove, that would be good.

cheers

[peddyp]

just been reading a bit more about replication. This could be a reason for the database bloating so much.

Can anyone confirm this could increase the size so much?

[ItsMe]

What is replication? This can mean different things to me. Perhaps you are looking at the file that Access creates after your app crashes. I do not consider this to be replication.

If multiple users are opening the same FE file, they may be contributing to its growth as they use Named Saved Query objects.

[peddyp]

The BE database is replicated so there are 2 files, same name and same size. I believe this may have been setup to allow design changes while not affecting user access, however this just seems to complicate matters.

Yes, multiple users are opening the same file.

[June7]

For managing distribution of frontend, review https://www.accessforums.net/code-re...end-33331.html

[orange]

Multiple users should be using their own copy of the FE. Your set up processes may be that every user takes a copy of the FE from the server to their local PC. Users should not be sharing a single FE.

[ItsMe]

The BE database is replicated so there are 2 files, same name and same size...

Still not sure what you are talking about. Replicating a database should not have any effect on a database. However, I do not know what your perception of what is happening is.

Against my better judgment, I will guess... Double check the links for tables using the Link Manager.

As for the size of the FE files...
Definitely a good idea to have FE files on the User's local machine. A different copy for everyone (already mentioned here by others).

[peddyp]

Thanks for all the input.

I have read that replicating a database can double or triple the size, however doesn explain the 400MB to 6MB.

One question regarding replication, which by the way seems to be very hard to find much info on.
What would be the best way to compact the database. do the replica , then sync the change. Or will I have to compact botht the master and the replica?

Also, If I wanted rid of the replication, In the FE could I just change the linked tables to look at the master? and then forget about the replica?

[ItsMe]

...Also, If I wanted rid of the replication...

So you do not need it? Why did you opt to use it if you do not need it? Do you have people leave the office with the FE and BE on their laptop and then return to synchronize the Replication copy? Are you dealing with MDB files?

Better yet, explain what it is you are trying to do with replication. You are consistently skirting the subject but then maintain this is the root of your problem.

[peddyp]

If you read my original post, I said I inherited the file so I did not opt to do anything.
I also said all users are on local LAN

I am just asking how to remove the replication as it is just an added distraction, or if I didn't remove it what's the best way to compact the master BE and replica BE.

if I am not clear about things, or "skirt the subject" then I apologise but again, read my first post, this is all new to me.

[June7]

I think replication utility was available with Access 2003 - review:
http://www.blueclaw-db.com/broad_int...eplication.htm
http://office.microsoft.com/en-us/ac...005262336.aspx

Mimicking this functionality in later versions requires lots of code. I have done something like that to import records from satellite copies of database into master file.

What is the file extension of your db files - mdb or accdb?

[peddyp]

Hi June

Thanks Ill take a look at that.

db files are mdb.

[June7]

The solution might be to import everything to a new accdb file. Maybe the replication process will then 'go away'.