ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

 Top ASP 101 Stories Top ASP 101 Stories
An Overview of ASP.NET
Connections, Commands, And Procedures
What is ASP?

Using Response.CacheControl
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates

Quick Tips

Handling "File In Use" Errors when Updating an Access DB

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

Paul DeBrino

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

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


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
End With
If (Err.Number = 3709) Then
   Response.Redirect "offline.asp"
End If
On Error Goto 0

Previous      Show All Tips      Next

If you have a tip you would like to submit, please send it to:

Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback
The Network for Technology Professionals



Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers