Web Development

Ask something
Discussion Locked

Access 2000 Query--Very Important

0 helpful
5 replies

by sameer_koranne

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am developing a small package in Access 2000 with Front end and back end both as Access and I have a techical doubt in that which is explained as below
While using the DoCmd.OpenQuery statement in VBA of access, to supress the messages access asks we use the SetWarnings command. This command suppresses all messages given while using Append query in VBA.
But while appending any rows, it also suppresses Error messages. The senario is as under
I have append query which appends some records in a table having Primary key, to avoid duplicates. But my append query, is trying to update some records which may create duplicates in the table. Since, table is indexed, duplicate records will not be appened to the table. So far so good.
If am notusing DoCmd.SetWarning fascility to suppress the messages, it will ask me to update the records, say 100. When I click Yes, to allow the append, system will try to add 100 records to table. but because of duplication of code only 50 records ar updatable. Now system will flash another message saying it cannot add 50 records due to key violation errors. And will ask to click Tes/No button there. If we click Yes, error is supressed and no error is displayed. But if we Click NO then, for every record, it will display the type of error occured. This is fine.
NOW, if I supress all messages i have with DoCmd.SetWarning , then all messages are supressed and I am not able to catch error there and ultimately, I am not able to detect the number of records affected by the save for that instance by the particular user.
Is there any way we can have the number of rows appended OR Trap the error message while using DoCmd.SetWarnings??

I will be highly thankful to you if you can solve my problem as early as possible

5 replies

by hypersoniq mcse

DoCmd.SetWarning False before your RunSQL statement
DoCmd.SetWarning True after your RunSQL statement.

this type of problem is difficult to troubleshoot without seeing the full code, but hopefully the above helps.

by joe kowalski

I am not sure what your are doing from a business perspective however it sounds like you appending and/or batching records to the table. Hypersoniq MCSE does help to answer some of your question but if you are also looking at trapping and/or loggingyour errors here is the approach which is typically used in VB for data transfer.

Public Sub SomeSub()

On Error Resume Next 'Set error Handeling to

DoCmd.SetWarning = False

'Run SQL

If Err.Number = 0 Then 'Check for an error number -
'Contine Process
Else
'Error has occured
LogError
End If

End Sub

Private Function GetErrorString() As String
Dim strTemp As String

With mudtError
strTemp = strTemp & "Error Report - " & vbCrLf
strTemp = strTemp & Space$(4) & "Timestamp = " & Date & ":" & Time & vbCrLf
strTemp = strTemp & Space$(4) & "Code = " & Err.Number & vbCrLf
strTemp = strTemp & Space$(4) & ERR_SOURCE & Err.SOURCE & vbCrLf
strTemp = strTemp & Space$(4) & ERR_DESCRIPTION & Err.Description & vbCrLf
'Add any other usefull info
End With

GetErrorString = strTemp

End Function

Private Sub LogError()
Dim intFile As Integer
Dim strFilePath As String

On Error GoTo ErrorHandler

strFilePath = "'Error Long Loacation"

intFile = FreeFile

Open strFilePath For Append As #intFile

With mudtError
Print #intFile, GetErrorString
Print #intFile,
End With

Close #intFile

End Sub

This discussion is closed to new replies.