How do I fix a database that will not close even though record sets and connections are closed?

Question by ValLin 88: How do I fix a database that will not close even though record sets and connections are closed?
Created an Intranet, web application using Classic ASP that’s currently connected to an Access database. Mysteriously, when some User’s accessed the application and tried adding, deleting, or updating data, they would receive: “Arguments are of the wrong type…800A0BB9…etc…” The error would always occur on the line where the recordset is open.

I then reviewed and updated the code to ensure that all record sets and database connections are closed upon use; cursor types were appropriate for the SQL action: i.e. add, update, delete; resolved any syntax issues; matched data types. The database is on a Windows 2003 Server.

Even after these changes and sucess on my local and test windows’ server, when data is added, updated, or deleted and I’ve closed the browser, the database remains open for hours; displaying a lock file. I don’t get the “Arguments error” but because it randomly occurred, it may still for someone else.

This database currently uses a .mdw file for security and was once a standalone application that used Access Forms to process the data that the web application is supposed to do now. There were many settings and querys used to manage data.

What do I need to check in the code and/or database in order to resolve this error?

Best answer:

Answer by scott
The first issue is that access was never designed to be multi-user. In some cases, you can maintain around five users successfully, but on the web side, that is a completely different story. The random locking is normal.

To resolve this, you must stop using access and switch over to Microsoft SQL Server 2005 Express Edition.

You can download it for free at the following address:

http://www.microsoft.com/DownLoads/details.aspx?familyid=220549B5-0B07-4448-8848-DCC397514B41&displaylang=en

You can download a program to convert and access file to SQL file using the following address:

http://support.microsoft.com/default.aspx/kb/237980

The only thing you need to be careful of is that the wildcard character changed from * to %

What do you think? Answer below!