VBScript Access Error Checking 1

[Babscoole]

Running into an issue with a script I’ve been working on for some end-users, the relavant portion of which is pasted below.

In a nutshell, the script calls a stored procedure (append query) in an access database. This query is moving a row from one table to another(source to destination). The script will be run by an end-user, not myself, so simplification of the user experience is important. The annoyance that I'd like to solve deals with error checking.

When the script is run and the entry already exists in the destination table, I've been getting the very user-unfriendly message of "[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." as a popup message. I'd like to capture this error and give an easier message to the user in the HTA GUI that's coming together for this script.

I added On Error Resume Next to the script and just after the call to the stored procedure I display Err.Number & " " & Err.Description. Here's where I'm having the issue. If the entry doesn't already exist in the dest table, then it is written from the src db and returns an Err.Number of 0. If the entry does exist in the destination then it still returns an Err.Number of 0, with the stuff in the paragraph above as the description. How can I trap this error in an If ... Then when the Err.Number is always 0?

I’ve been given a hint that this can be solved by using the extended error info in the connection object, but I’m really not sure how to go about doing this. Could anyone here who knows ADO well possibly provide some sample code to help me resolve this?

I suppose another way to take care of this would be to check if the record already exists in the destination before trying to run the stored procedure (append query). That would be in the same DB, in a table called Case_Tiff_Files, checking if the DBCaseFile variable exists in the column named CaseNumber. Thinking about it, this would probably be easier. Any help with sample code for doing this query and getting the resultant output (1 for exist and 0 for doesn’t exist I assume, but really don't know what it would return.) would be appreciated.

Thanks.


On Error Resume Next
Dim oConn, DBCaseFile, DBError
' Set File Number to UnPurge
DBCaseFile = Text1.value
DBError = err.Number
BlueMsg "Restoring Entry in CaseFile Database. This may take several minutes to complete."
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=W:\Case_Files.mdb"
BlueMsg "Now connected to the Casefiles Database"
' execute query. Must have space after Procedure Name
oConn.Execute "exec RetPurgProc " & DBCaseFile
RedMsg DBError & " " & Err.Description
WhiteMsg "The entry for case number " & DBCaseFile & " has now been restored to the CaseFiles Database. It will be usable in Caseweb after the next Day-End run."
' Clean up
oConn.Close
' oConn.Quit
Set oConn = Nothing

 

[PHV]

A starting point:
oConn.Execute "exec RetPurgProc " & DBCaseFile
If oConn.Errors.Count > 0 Then
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Babscoole]

Thanks you, I think that got it.

Just for knowledge sake, I did start working on figuring out how to query if the entry already existed method and kept coming up with errors with the query. It's got to be a syntax problem, but I can't seem to get it right. What do I have or not have in here that's causing the error:

Dim oConn, DBCaseFile, DBError
' Set File Number to UnPurge
DBCaseFile = "351454"
sQuery = "IF EXISTS(SELECT 'True' FROM Case_Tiff_Files.CaseNumber WHERE((Case_Tiff_Files.CaseNumber)=DBCaseFile)"
Set rs = oConn.Execute(SQuery)

Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=\\wlf-dc1\Transfer\testbed\Case_Files.mdb"

If rs(0) Then
WScript.Echo "This record already exists"
Else
WScript.Echo "Continue with Append"
End If

' execute query. Must have space after Procedure Name
' oConn.Execute "exec RetPurgProc " & DBCaseFile

' Clean up
oConn.Close
' oConn.Quit
Set oConn = Nothing

 

[Babscoole]

Still can't get the query formatted correctly. Here's what the original query looks like in access:

SELECT Case_TIFF_Files.CaseNumber
FROM Case_TIFF_Files
WHERE (((Case_TIFF_Files.CaseNumber)="351418"));

Something about the formatting and adding "IF EXISTS(SELECT 'True' to the beginning isn't going over well in VBScript.

 

[Babscoole]

At least I've now got the query to stop throwing errors, but now it tells me that the the record exists every time, even if it doesn't. Blah.

Current code:

On Error Resume Next
Dim oConn, DBCaseFile, DBError, rs
' Set File Number to UnPurge
DBCaseFile = "100000"
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=\\wlf-dc1\Transfer\testbed\Case_Files.mdb"
sSQL = "IF EXISTS(SELECT 'True' FROM Case_Tiff_Files WHERE Case_TIFF_Files.CaseNumber = '" & DBCaseFile & "' "
Set rs = oConn.Execute(sSQL)

If rs(0) Then
WScript.Echo "This record already exists"
Else
WScript.Echo "Continue with Append"
End If

' execute query. Must have space after Procedure Name
' oConn.Execute "exec RetPurgProc " & DBCaseFile

' Clean up
oConn.Close
' oConn.Quit
Set oConn = Nothing

 

[PHV]

You may try this:
...
sSQL = "SELECT CaseNumber FROM Case_Tiff_Files WHERE CaseNumber='" & DBCaseFile & "'"
Set rs = oConn.Execute(sSQL)
If Not (rs.BOF Or rs.EOF) Then
WScript.Echo "This record already exists"
Else
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Babscoole]

Very cool. Looks to be working nicely. Now I just need to integrate this back into the HTA it's to live in and can (hopefully) close the books on this one.