Top ASP 101 Stories An Overview of ASP.NET Connections, Commands, And Procedures What is ASP? QUICK TIP: Using Response.CacheControl Show All Tips >>  ASP 101 Updates

Quick Tips

One of our visitors came up with a quick and easy way to handle the file in use errors you can get when updating an Access database. Instead of me butchering the idea... I'll let him explain:

I have a website with two Access DBs; one contains the site's various configuration tables while the other contains the tables used to store the visitors orders. For starters, I want to note the importance of keeping these databases separate, as it allows me to upload a modified configuration DB without losing any of the orders. If I had one DB containing both config & order tables, I would have to leave my site off-line while I downloaded it, made changes and then re-uploaded it. This fact is a perfect segue to my tip.

While I am in the process of uploading a modified (e.g. new fields) configuration DB to the server, ASP will not be able to open a connection but rather produce a "file in use" error. Instead of leaving a visitor with this ugly, unanticipated error, I monitor for that specific condition and redirect the visitor to a "sorry, we're offline due to scheduled maintenance" page. Although it's infrequent that I cause this condition, it's best to have some code in place to handle this error cleanly.

You'll notice that I "disable" the error-catch logic once I know the connection has been established without incidence. Should some other error occur with the connection attempt, that error will still be reported. This is because the error condition remains "raised" since I did not perform the Err.Clear method. Simply enabling On Error Resume Next does not clear the error condition.

' *************************** ' Create a connection object ' (monitors for file in use): ' *************************** Dim conn, strMyConnectionInfo Set conn = Server.CreateObject("ADODB.Connection") On Error Resume Next conn.Open strMyConnectionInfo If (Err.Number = -2147467259) Then Response.Redirect "offline.asp" End If On Error Goto 0

Regards,
Paul DeBrino

www.infinity-rd.com

Thanks Paul... I'm sure lots of people will find this quite helpful.

Paul got back to me with a new and improved version:

Hello

As an addendum to a tip I sent you recently, please validate my theory and append it to the current tip. Thanks!

' *************************** ' Create a recordset object ' (monitors for file in use): ' *************************** Dim rsRecordSet, strMyConnectionInfo Set rsRecordSet = Server.CreateObject("ADODB.Recordset") On Error Resume Next With rsRecordSet .ActiveConnection = strMyConnectionInfo .Source = "SELECT * FROM mytable" .CursorType = adOpenForwardOnly .CursorLocation = adUseServer .LockType = adLockReadOnly .Open() End With If (Err.Number = 3709) Then Response.Redirect "offline.asp" End If On Error Goto 0

If you have a tip you would like to submit, please send it to: webmaster@asp101.com.