Error Display issue at database pull 1

[cdck]

I have an ASP page which queries an Access Database. Unfortunately, the database was set up in such a way that if any user has it open, it will not allow other users to access the data. Currently, when this conflict occurs, the page processes up to the point where it attempts to pull the data, and this error is then displayed:

[tt]
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Could not use '\\Ad1\ad13d\LabelApp\M2M Label Program2\ID_Labels_2_3_be.mdb'; file already in use.

/SNLookupExt.asp, line 75 [/tt]

This makes perfect sense to me, but confuses the heck out of my users. I have been trying to use error handling to replace this message with something more legible to my users, but seem to be having trouble finding the correct way or place to enact this handling.

My code is currently:
[tt]
(64)listSQL="SELECT Serial_Number, MA_Number, Seq_Number, UnitNo, Model_No, Config_Part_No, Part_Rev, Description, Contract_No, Module_SN, Scan_Date, fcompany FROM qryLABSerialInfo WHERE " & section1
(65)
(66)
(67)Set Con=Server.CreateObject("ADODB.Connection")
(68)
(69)Con.Open strConnect
(70)
(71)
(72)set rstemp = Con.Execute(ListSql)
(73)
(74)If Err = "80004005" Then Response.write("An Access user has the Label Application open at this time, blocking this search request. Please Try again Later.</TD></TR></TABLE><!--#include virtual='includes/docEnd.asp'-->")
(75)If Err = "80004005" Then Response.End
(76)
(77)If rstemp.eof = True Then Ender = 0 Else Ender = 1
[/tt]

Before I entered the If>Then statements attempting to capture the error, the line that the error referenced was line 72. I have tried it treating the error both as a number and as a string (with and without the quotes).

The general error handling method matches what I found in "VBScript Unleashed" - I wonder if I am placing it incorrectly, or what I have overlooked. What am I doing wrong?

Cheryl dc Kern

 

[guitarzan]

Error handling in VBScript is in this general format:

On Error Resume Next [COLOR=green]'suppresses run-time errors from being shown[/color]
[COLOR=blue]'Your code here...[/color]

If Err.Number <> 0 Then
   [COLOR=blue]'Error handling code here...[/color]
   response.write "Got Err#" & Err.Number & ": " & Err.Description

End If
On Error Goto 0 [COLOR=green]'undoes the "On Error Resume Next" above[/color]

The Number property of the Err object is a number, not a string. For details, see here:

http://www.devguru.com/technologies/vbscript/quickref/err.html

 

[gmmastros]

I have a couple questions for you...

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

Why are you using ODBC? You don't need it. It probably doesn't make much difference, but removing it will speed up your code a little (but it may be too little to notice).

Unfortunately, the database was set up in such a way that if any user has it open, it will not allow other users to access the data.

Why? Seriously... why? I don't know everything there is to know about Access, but I do know that it can handle concurrent connections. Usually, there is a very minor change to your connection string that allows it to operate it multi-user mode. This is very simple to accomplish.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[cdck]

Guitarzan: Thanks, I will attempt to incorporate the resume next and see if that fixes it for me.

George: Unfortunately, I did not build the database and I cannot change it in any manner at this time. The plan is to replace it down the road entirely, with the data stored in SQL server and the front end constructed in Visual Basic, but it's not the current hot item. Currently, I'm just trying to stop so many users from calling me in a panic during normal operation.

Cheryl dc Kern

 

[cdck]

The On Error Resume Next line did the trick! Thanks!

Cheryl dc Kern

 

[guitarzan]

cdck:
Don't forget to use "On Error Goto 0" immediately after, to undo the "On Error Resume Next". Otherwise you will not know about other runtime errors, and can get quite unexpected results.

 

[BigRed1212]

cdck:

George isn't suggesting you change the database.

He is suggesting that if you are using something like

Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Exclusive=1;Uid=admin;Pwd=;

to connect to it, that using something like

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

might fix your problem.

 

[cdck]

Sorry I wasn't more clear - that was in respsonse to his second comment, regarding the database not allowing multi-user access.

Regarding the first, I'm using a system DSN for the connection, so the connection string variable setting looks like
[tt]strconnect="DSN=SNs"[/tt]

If I am then using that connection inefficiently, I'll be quite glad to change it. Is the call for the connection to open simply calling the wrong type of db connection?

I know that this won't correct the multi-user issue, however, because it comes up with users accessing it through the original database on a regular basis. The basic settings do not preclude this working, but he built every form in an unbound state and used a lot of coding to populate and manage them, including his switchboard; I suspect there's something in that code causing the conflicts, but the time it would take to track anything like that down is one reason we've decided to replace the system rather than try to fix all the little glitches and issues we keep running into. For now, it works, and the data is consistent, but in the long run...



Cheryl dc Kern