I had created the Document Tracking database for my project use and its around 20 MB. After finishing the database I had used Analyze performance in Database tools and Implemented all the optimization.
After that It worked well for last week and yesterday Suddenly access was crashed yesterday by creating the backup file.
Eventhough I got the back up file I am worried about this crash. Could you explain me what will be reason for this crash, It is beacuse of poor database design, If it is how can I rectify the same.
My database is a very small one, I have only 12 Tables,70 Queries, 3 Forms and 6 macros only & userdefined functions.
I have the Primary key in four tables remaining tables I could not create the primary key bacause of my data structure.
Kindly help me to understand the reason for this crash and how to avoid the same in coming days
First thing first: I don't remeber whether I alreay asked that question before or not (sorry if I already did), is your application split? This means that all the tables are stored in an Access file (.mdb or .accdb) and this file is stored in a shared location on the network (usually on a server): this is called the Back-End (BE), while all the interfaces and working parts (Forms, Reports, Modules, etc.) are stored in another Access file named the Front-End (FE). A copy of the FE resides on each client machine and has access to the single BE file. If your application is not split yet, that would be the first thing to do.
Now, what follows are not criticisms but merely feelings I built up along the many questions you recently asked about your project. I did not analyzed your project and have no intention of doing so. I'm on this forum as a volunteer trying to help other people, not as a paid consultant.
1. You keep using Excel as the data entry interface and continuously import the data from Excel into Access in batches. Healdem and I warned you recently about this: it's messy and not the proper way to work with Access. This kind of process forces Access to constantly reorganize its internal structures and to constantly recompute the indexes of the tables. The more these processes occur, the highter the risk of errors leading to data corruption. Hence the importance of having split the application in two files: if something goes wrong, the FE should be left unaffected by the corruption.
2. Lack of analysis. I have the feeling that you don't have any global vision of the project. It's as if you began something without actually knowing where this would lead you. There is no master plan, no general blue print of the project. You keep changing it each time you encounter something new, a problem you did not foresee. You keep on adding bits and chunks to solve various unanticipated problems when they appear. This quickly leads to what I use to call "patchwork projects". This kind of project is hard to maintain and to debug and becomes quickly quite messy.
Sometimes you cannot do anything else because users or clients or both keep on asking for new or different features and you have no choice but to comply. In such cases, I use to cautiously keep increasing versions of the functional application. When I need to build a new version, I create a new Access project, import all objects from the working one and go on from there. This is an opportunity to clean up, reorganize the code if necessary, remove or comment unused features, etc. All the process must be clearly documented.
Moreover, you seem to have put into prodution an application that is unfinished and unstable. This is bad because when people begin to work with an application they acquire habits, habits that are sometimes difficult to get rid of when the application interface or the way it works changes dramatically. Another problem comes from that users rely on the program to do their job, enter data into the system, all things that make important changes more and more difficult to implement. You end up with unhappy users/customers and a malfunctioning program.
3. Lack of knowledge/skills. You sometimes post very basic, elementary questions that seems to indicate that you don't know Access, databases theory and programming in general very well. It's of course OK to ask questions: that's how we all learn and there are no stupid questions, only stupid answers, sometimes... However, I have the feeling that you piled up more food onto your plate than you can eat, if I may say so. You should consider learning more about those matters (Access, relational databases theory and programming in VBA and in general), not from scratch, trying to solve a specific problem when you encouter it, but in a more theoretical and organized way. Here at the forum we can try to help you solving a set of specific problems but not to provide such a training.
4. What you can do now:
a) Take a copy of the database(s) and keep it (them is the application is already splt) in a safe place (i.e. away from the folder in which you'll work).
b) If it's not alreay done, split the project. There is an assistant that can help you in doing so but, personally, I would create 2 new Access files (1 for the BE and 1 for the FE), then open them (one at a time) and import the various objects from the original file(s) into them (tables in the BE, everything else in the FE). If the project was not split, you'll also have to attach the tables from the BE to the FE at this step (there is also an assistant in Access that can help you for that).
c) From Access, make a backup of both (new) files and keep the copy in a safe place (see above).
d) Compact and repair the BE.
e) Open the FE with the Decompile option. The command from the command line or in a .bat or .cmd file is:
- for Access 2013
"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "path to your access file" /decompile
- For Access 2010
"C:\Program Files\Microsoft Office\OFFICE14\msaccess.exe" "path to your access file" /decompile
f) Open the VBA editor (Ctrl+G) then compile the project (in the Debug menu --> Compile). If the project cannot be compiled (compilation errors occur), correct the code until everything compiles withour errors (come here for help if there are things you do not understand and/or cannot amend by yourself).
g) Make a backup of the successfully compiled FE (see above).
h) Compact and repair the FE.
i) Make a copy of the FE file and keep it in a safe place (see above).
j) During all these steps, keep a written trace of everything you do.
Please don't take offence of what I wrote: my intentions are not to be harsh, contempting or belittling and I apologize in advance if it might appear so to you. My sole purpose while writing this is to try to provide an external and neutral point of view, with the hope that it will be able to help you improving in the development of your projects.
Thanks for your detailed reply and advice. Definitely I will not take your reply as a offense, because still I am access learning stage and I would like to learn more with help you and other members of this forum. So I will take your reply in a positive way.
To be more frank until last 2 months I was using Excel for my project use, but my boss was using Access and Crystal Report for our project and I was attracted by that. When I ask about that he refused to provide the database, so I was trying in the internet for getting the sample database for a document Tracking and Transmittal preparation. But however I could not find the same and I started preparing my own database.
At the First, my first objective is to prepare a simple database after started working on it I found more things can be done in access. That's why I was making lot of changes and asking too many queries for the further improvement and still my queries are not over and I hope this forum will help me further. Of course yes it is a wrong methodology, but I will try to correct the same from my next database.
And the another thing in my project other than myself/(Boss already left the company) nobody shows interest/Knows about access. But after I prepare the report from access then they want to make additional changes. This is also one of the problem I am facing.
For time being I will take your instructions and try to incorporate the same in my project and I am proudly saying with help of you and Healdem my database has taken good shape more than I expected.