Database Size

[JohnTyke]

I used to produce a monthly report where a txt file was converted from UniCode to ASCII using UltraEdit32 and then the ASCII file is imported to a Access db template file. Because this was a lengthy process I had an IT student automate the process in Access, unfortunately the guy is now back at university.

Some months the Access file is OK and other months the file opens as read only fand when a table is opened an error message of


"The Visual Basic for Applications project in the database is corrupt", the only way to close Access is via Task Manager. The final size of the database is around 200 MB.

We cannot find a reason for the occaisional failures. Is the size of the database the problem?

[dcrake]

It seams you have reached the limit of the db size have you tried running a compact and repair to remove all the bloated records? This should reduce the size greatly.

Have you got the mdb split between front end and back end?

David

[JohnTyke]

Hi David,

Thanks for the swift reply.

I've just tried repair and compact but because it's a read only file I get a message saying the file is already opened exclusively by another user and I need permission to view its data.

Would you elaborate on 'Have you got the mdb split between front end and back end?'

Thanks

John

[dcrake]

From the response you have made I take it that the database is not split. Essentially a working mdb should comprise of two parts, a front end and a back end. The front end mdb contains all the objects, such as froms, queries, reports, modules. etc. With the exeption of tables. The back end contains only the tables.

Then by using the link table manager you can effectively link the two together thus giving the front end access to the back end tables. The back end should be situated on a central server which all users have access to. A copy of the frnt end should be deployed to each user who will have their own copy on their local machine.

However, even if you adopt this approach do not place a single front end in a single location that everyone accesses. Its a bit like going to a football match. Everyone wants to see the game but if they all go in the staduim through the same entrance you will get aggravation and crowd trouble. However give every one a seperate entrance to go through and everyone will get to their seats on time and everyone can watch it together. The major issue is corruption, not only the user interface side of it but more importantly the data. Don't forget if this falls over the whole system is down. Also by having a development copy you make changes to the front end and after testing you can deploy it out again to all users without distruption to their daily activities.

There are several more issues that could be discussed here on this topic but this is the basic concept of having split applications.

You are corrct in that you cannot comact if any users are in the application, this is another downfall of not having it split. You will have to get all users off the system, then open the database exclusively then run a compact and repair. This would be a good time to make of copy the system for backup purposes.


David

[JohnTyke]

David,

Thanks for the reply you have confirmed my suspicions that the original process has reached the end of its useful life and we need to move on.

The recipient of the report is going to spec out his requirements and come back to me. The larger team will then look at getting the data from SAP reports.

John