crashed access

[nivalju]

I have several access databases that I use daily. One in particular is used to create, modify and delete tables and queries on the fly. There are 3 of us with a similar database and we compact and repair a few times during the day, but each one has crashed at least once and we had to use a backup database and update it.
What can we do to stop this or at least lessen the effects of the crash.

The database is not split--and we each have our own copy. We each upload flat files created from an Oracle database of student information. Since we do not have a direct link to the main database we upload student information several times during the day in order to be as accurate as possible. We have no IT support.

Judy

 

[isladogs]

Hello again

One in particular is used to create, modify and delete tables and queries on the fly. There are 3 of us with a similar database and we compact and repair a few times during the day, but each one has crashed at least once and we had to use a backup database and update it.
What can we do to stop this or at least lessen the effects of the crash.

The first sentence is the main cause of your problems
Repeatedly making & deleting tables not only causes database bloat but also leads to instability & crashes.
Compacting will reduce the bloat but won't solve the instability issues.

The ideal solution would be to link to the Oracle database tables.
Failing that, if you can keep the same tables but APPEND & DELETE records, almost all your problems would disappear completely

With your current setup, I recommend you DECOMPILE your database, then RECOMPILE and finally COMPACT. This link explains how & why you should do so:
http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

For info, I have one test database used for importing & analysing JSON files.
Because each file has a different structure, I have to make multiple tables much as you describe.
Decompiling is done every week or so depending on how much it is used. As a result it never crashes

Also, I have for many years supplied Access databases for schools. These update student, staff & school data every night from external sources using linked CSV files.The process involves doing a structured update as a scheduled task. If it would help you to know how this process works, let me know & I'll send you a PDF summary

 

[JHB]

.. One in particular is used to create, modify and delete tables and queries on the fly.
..

What is the purpose of create, modify and delete tables (and queries) all the time, so my question is, why do you do that?

 

[nivalju]

The reason for constantly updating the files is to have up to date information on students. We use this information to request refunds for students and to review student accounts.
We are trying to have IT produce some of these reports, but they either do not have all the information needed or it does not add up to what we need. We use data from outside sources/vendors and from different modules from our modules to create one complete list.

I am hoping to create a structure only database that can be used when the original crashes- so that the work can continue. I will be retiring soon and there is no one to support these databases.

Judy

 

[isladogs]

Hi Judy
Did you see my earlier reply in post 2?

 

[nivalju]

Thank you very much- please send me pdf file.

 

[isladogs]

I mainly meant this part from my first post:

The ideal solution would be to link to the Oracle database tables.
Failing that, if you can keep the same tables but APPEND & DELETE records, almost all your problems would disappear completely

With your current setup, I recommend you DECOMPILE your database, then RECOMPILE and finally COMPACT. This link explains how & why you should do so:
http://www.fmsinc.com/MicrosoftAcces.../Decompile.asp

Zipped PDF attached. The main reason for suggesting it was the outline of the process on page 1. Some of the later detail won't mean anything without the software itself

 

[nivalju]

Thank you very much-

 

[isladogs]

Thanks Pat.
That's a neat alternative which I'm going to make use of ....

 

[nivalju]

Thank you Pat---I did not think anyone else had a problem similar to mine.

 

[sxschech]

Was wondering if there was a reason using filesystemobject for copying a file? Couldn't one use FileCopy instead, then no reference is needed.

FileCopy Source, Destination