Close Database connection to prevent Database locking issue? 1

[evergreean43]

I have an Access 2000 read only form that gets locked alot after someone closes the form. It ends up being locked for about 2 hours sometimes after someone closes the read only form.

Please advise what I can add such as database closing functions or something to prevent the database from getting locked after a user closes the form.

The form is a search only form which was made using the Access form creation wizard.

 

[kjv1611]

Well, during the use of the form, are you opening any database objects?

Also, when closing the form, how is it closed? With the ole X button, or a custom close/exit button?

 

[evergreean43]

It is reading database field values from a query so I assume it is opening database objects?

The form is closed using the x button and sometimes the exit button.

Please advise.

 

[kjv1611]

So, is the database it is reading from another Access Database, or a SQL server?

If it is just a query from another database, you are not necessarily "opening" a database, and therefore needing to close it.

But the question also still remains - how is the form closed?

 

[evergreean43]

Sorry I was not clear.

This is reading using a query from the Access Database, the same one.

The form is closde by hitting the X button on the top right corner of the form and sometimes using the exit in the file menu.

 

[kjv1611]

Maybe you should try using a custom Close button as apposed to the other methods. It should work a little cleaner, I would think, and it would give you the place to work with in making sure that everything is closed up/cleaned up as necessary. However, you shouldn't have any problems with the query, so long as you are not opening a recordset of that query.

Another thing to keep in mind is how many users are using this database at the same time. It is highly probable (though I do not know off the top of my head what the limit is) that you will reach a limit in the number of simultaneous users that Access can support. I've heard of one we had where I work that they finally had to move on from, b/c there were just too many people using it for it to be stable, and it had multiple crashes for that very reason.

 

[evergreean43]

I can use a Custom Close button but not sure how to put closing code in there because I just created a rectangle box and used Macro event called "Quit" to close and exit.

Is there any code I should add to the OnClick that uses the Macro? Or is there a better way or code to use for closing and exiting the database?

 

[kjv1611]

Well, if you are just wanting close the form, you should be ablet to just use:

DoCmd.Close

If you are wanting to exit the whole application, then just use:

DoCmd.Close
DoCmd.Quit

In the OnClick event. Everything else should be cleaned up just fine.

Also, how often do you do a compact and repair on the database? It's possible that would fix your crashes as well, as the size of the database may be getting rather large after much use. When you do a compact and repair after much changes, it will greatly reduce the size of the database.

It's kind of like if you eat too much, you get bigger, and even if you just cut back on your eating, you won't get smaller very quickly. So, some folks might would choose lyposuction. Just think of the Compact and Repair as a lyposuction for your database without the negative side effects. [grin]

It can have some negative effects on occasion, but 99% of the time you should be safe. It wouldn't be a bad idea to create a backup copy of the database before running the compact and repair.

 

[evergreean43]

Thanks you have helped me alot on this.