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

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
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?

QUICK TIP:
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

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

Previous      Show All Tips      Next

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


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

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