Question Too many forms cause corruption? (1 Viewer)

qqq · Jan 28, 2014

Hi,
we are running a pretty extensive DB, evolved out of 10 years of incremental development, today consisting of accdb frontend with MySQL backend (linked tables).
I am increasingly facing corruption while editing forms / reports, and am trying to find out why corruption occurs at all.
I suspect that the sheer size of the db is creating problems: ~190 tables, ~160 forms, ~30 reports, 9 modules.

Any idea, whether the number of items can cause corruption?
What's the number of maximum items accdb can handle?
And how can I tell, if this is the problem?

Any hints very much appreciated!

Marcus

-----------
frontend: accdb-file for Access 2010, running on Win7 64-bit
backend: MySQL 5.1.50 via OBBC-connector
both backend and frontend backed up regularly.

sensetech · Jan 28, 2014

What you're suggesting doesn't surprise me I but don't think you can put a maximum on the number of elements before you start getting problems. It will probably also depend on the size / complexity of some of these elements especially forms.

Has the front-end been compacted recently? If not, try taking a copy of it (don't do it with the original, just in case!), compacting it then see if things are any better. Before you do the compact , are there any redundant elements that you can safely delete?

Galaxiom · Jan 28, 2014

I wold start by importing everything into a new database.

gemma-the-husky · Jan 29, 2014

I have had this with big databases

I have a A2003 DB, where some forms crash access on opening every time on one machine only, no matter what I do. build it to an mde, and they work. How is that possible?

it's forced us to move to A2010.

qqq · Jan 29, 2014

Thanks so far. Have re-imported everything to a new file several times, no change. Converting it to an mde (or equivalent) will not work, since the problem occurs while editing forms/reports. This is somethin I obviously can't do in mde, so I will need to work with the open files.
What I've also tried is moving to Access 2010 (originally 2003) - fewer crashes, but still some. Decompiling done as well, no sustained improvement.
Any other ideas?

sensetech · Jan 29, 2014

When you say you've moved to Access 2010, have you actually tried going from .mdb to .accdb? Sadly, I think I can guess what your answer's going to be

Alternatively, Is your PC fairly high spec? If not, can you try it on something meatier?

(Yes, I'm clutching at straws!)

qqq · Jan 29, 2014

sensetech, yes, I've moved to accdb - minor improvements though. PC is off-the-shelf, nothing particularly meaty (2013's thinkpad x230).

Another thought occured to me: I am using a Windows batch file to create a copy of the (closed) accdb file and save it with a timestamp in its file name, such as "Database1401291138.accdb". Could it be that this batch routine breaks an accdb file, if it has been closed just some 5 seconds ago?

The batch syntax is:

COPY "MyDB.accdb" "%cd%\backup\MyDB%date:~6,2%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%.accdb"

MarkK · Jan 29, 2014

Does each user have exclusive access to his/her own instance of the FE? It's pretty common to get corruption when multiple users connect to an Access Front End file over a network.

sensetech · Jan 29, 2014

Re. the back-up routine, I'd be amazed if that's causing the issue.

Your PC spec sounds fine. I thought you might have had something state-of-the-ark rather than state-of-the-art.

I'm stumped, I'm afraid. Is there any chance of splitting it into separate front-ends, one that deals with the routine day-to-day stuff and the other that covers less frequently used functionality like maintaining reference tables or running period / annual reports?

gemma-the-husky · Jan 29, 2014

to carry on, with some larger databases, I get similar issues.

sometimes, I just know complex forms will corrupt as I develop them, and save multiple versions as I make changes.

something as simple, as adding unattached labels, or then cutting and pasting them on to controls seems to cause corruption. The forms won't run, or edit. sometimes they won't even delete. no idea what causes it. I suspect it will be the corruption of some pointers within the mdb.

after all, and mdb file might appear to contain all these different objects etc, but it is clearly a complex monolithic file, managed by loads of internal pointers.

qqq · Feb 4, 2014

Brief update from my side: have been watching precisely, on what occcasions corruption occurs. Seems it does goes corrupte DUE TO CREATING A BACKUP, i.e. I can edit, open and close the accdb file as much as I want. But if I close it and wait only a few seconds before creating a backup copy of the file - it renders both files (the original one plus the copied one) corrupted. This is odd and will keep me from making too many backups. Any clues on how to avoid this?
Thanks,
Marcus

spikepl · Feb 4, 2014

I have two apps that run backup on closing. I don't have access to that machine right now, but as far as I recall, I close all the connections to the BE and then copy the BE with code from within the FE, so that whatever happens is synchronous.

But you have probs with your FE... I'd try the same approach, meaning close all connections, and then copy the current db from within itself.

qqq · Feb 4, 2014

hm. Any idea how to check for any connections I have missed to close before doing a backup?

spikepl · Feb 4, 2014

For an Access-based backend it is simple (and perhaps not so relevant for you) : I use the function Dir to check for existence of MyBackendDbName.laccdb - if it does not exist then no front ends are connected to it.

I have another app (not split - on purpose) - there I just close alle the forms and start a hidden unbound form where in some event i copy the current database to backup.

But you have MySql, and I don't know how to check for nothing being connected.

You are backing up the FE - this begs the question: why? What changes in the FE so that it requires backup? In principle the FE should not contain anything variable .. if you do have some local tables you could make a so-called Side end - an auxilliary db holding all the tables you do not wish to store in the backend.

Update: ... oh I get it - the backups are efter editing, whereas mine are all during normal operation...hmmm... why not put in some pause/brake/wait into your script and see if that helps? Also, for development, you could reverse the sequence: when I am going to do changes, I back up the current db, do changes, and backup the new one first after it turns out to work ok.

Question Too many forms cause corruption? (1 Viewer)

qqq

Registered User.

sensetech

An old, bold coder

Galaxiom

Super Moderator

gemma-the-husky

Super Moderator

qqq

Registered User.

sensetech

An old, bold coder

qqq

Registered User.

MarkK

bit cruncher

sensetech

An old, bold coder

gemma-the-husky

Super Moderator

qqq

Registered User.

spikepl

Eledittingent Beliped

qqq

Registered User.

spikepl

Eledittingent Beliped

Similar threads

Users who are viewing this thread