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-

 

[Pat Hartman]

I have a similar problem. No direct access to the source data so I can't link to it but must import a fresh copy whenever I need current data. My solution is to use a template database. I created a database with the necessary tables, indexes, and relationships. Deleted all the data and compacted the database so that it is empty. Then every time I need to load new data, I delete the old copy of the database and then use this code to replace it. The database never needs to be compacted because I simply throw away the old data and append the downloads to the new, empty database. Since the BE name doesn't change, the links don't even need to be refreshed.

Of course, as has already been recommended, is that you really must split the FE and BE. Using this technique, you might end up with multiple BE's because you might have permanent tables that are not replaced each time you need fresh data and you have the BE with the disposable data.

Here's the code. The file paths are hard-coded in this version because all the apps I built that need to do this live in the same server folder. The templates are one level below the databases that are currently linked and contain data.

This code needs a reference to the "Microsoft Scripting Runtime"

Public Function CopyTemplate(filename)
    Dim fso As New FileSystemObject
    Dim sTemplatePath As String
    Dim sBEPath As String
    Dim SourceFile As String
    Dim TargetFile As String
    
On Error GoTo ErrProc

    sTemplatePath = "S:\Verde_Common\AccessApplications\DatabaseBE\BE_Templates\"
    sBEPath = "S:\Verde_Common\AccessApplications\DatabaseBE\"
    SourceFile = sTemplatePath & "Template_" & filename
    TargetFile = sBEPath & filename
    Set fso = CreateObject("Scripting.FileSystemObject")
    fso.CopyFile SourceFile, TargetFile, True
    
ExitProc:
    Exit Function
ErrProc:
    Select Case Err.Number
        Case 53
            MsgBox Err.Number & "--" & Err.Description & "--"
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbOKOnly
            Resume ExitProc
            Resume
    End Select
    Resume ExitProc
    Resume
End Function

 

[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.

 

[Pat Hartman]

You're welcome. Glad to help.

It's a big world out there and pretty much ANYONE who has to interface with SAP (or other large centrally controlled application) has this problem. they NEVER let anyone link to THEIR data.

 

[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

 

[Pat Hartman]

You can use whatever you want.

FSO has a lot of features including being able to call up the windows browse dialog. Since I use FSO for its better file handling features, I tenn do use it all the time.