By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Error trapping ignored on second pass (DAO recordset duplicates error 3022)

P: n/a
Hi there,

I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.

Basically, it works as follows:
- adds records from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
on the second record and halts the code.

I have tried clearing the errors to no avail.

------------------
QUESTION:
Does anyone have any idea why access ignores my error trapping on the
second duplicate record?

Are there any alternative solutions?
------------------

Keep in mind that this code is designed to import over 40 tables which
all have varying field names, each table to be imported is stored in a
recordset called rs.

Private Sub btnImpTable_Click()

Dim TolasSourceTable As String
Dim TolasDestTable As String

Dim rs As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim MyFieldCount As Integer
Dim intcount As Integer

Dim rsDestString As String

Set rs = CurrentDb.OpenRecordset("tblTolasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors.Refresh
TolasSourceTable = "v" & rs!TolasFilename.Value
TolasDestTable = "tbl" & rs!TolasFilename.Value

Set rsSource = CurrentDb.OpenRecordset(TolasSourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenRecordset(TolasDestTable,
dbOpenTable)

MyFieldCount = rsSource.Fields.Count

If rs!TolasFileUpdate = -1 Then

rsSource.MoveLast
Me.intTotalRecords = rsSource.RecordCount
rsSource.MoveFirst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsDest.Delete

ImportStart:

Dim TheFieldName As String
rsDest.AddNew

'MyFieldCount less 1 to take into account field "0"
For intcount = 0 To MyFieldCount - 1

TheFieldName = rsSource(intcount).Name
rsDest(TheFieldName) = rsSource(TheFieldName)
Next intcount

'--------------ERROR TRIGGERS HERE -------
'This is where the error 3022 occurs on the second record!!!

rsDest.Update

'-----------------------------------------

DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsSource.MoveNext
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You are telling it where to go with the On Error statement. When the first
error occurs, you go there, clear the problem, and Refresh the Errors
Collection. I suspect what you are wanting to do is Err.Clear to clear the
error. I also suspect that the Refresh is canceling your On Error statement.

The code is written in what I would consider to be a non conventional way. I
would consider it to be more conventional to put the error handler at the
end with a Resume Next statement at the end of an If statement that you use
to determine the error number and make your deletion if the error number is
that of a duplicate entry. This would leave error handling in effect for a
general message if the error was something else. At the moment, you're
assuming that the error was a duplicate entry.

Also, there may be a better and faster way to do this. Stepping through a
recordset using DAO is slow. Queries are much quicker. You could use an
Update query to update any records that already exist with the new values in
your source table. Next, run an Append query to append any unmatched records
in the source table. To do this, you would use an Unmatched query as the
source of the Append query.

Example:
Dim strSQL As String, db As DAO.Database
Set db = CurrentDb
'Update Query
strSQL = "UPDATE DestTable INNER JOIN SourceTable ON DestTable.Field1 =
SourceTable.Field1 SET DestTable.Field2 = SourceTable.Field2,
DestTable.Field3 = SourceTable.Field3;"
'Add more fields as needed. The link will be on the ID field that causes the
duplicate.
db.Execute strSQL, dbFailOnError
'Unmatched query
strSQL = "SELECT SourceTable.Field1, SourceTable.Field2, SourceTable.Field3
FROM SourceTable LEFT JOIN DestTable ON SourceTable.Field1= DestTable.Field1
WHERE DestTable.Field1 Is Null;"
'Append Query
strSQL = "INSERT INTO DestTable(Field1, Field2, Field3) " & strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing

If an error does occur, the dbFailOnError switch will cause the error to be
returned. This error can be trapped in the form's Error event.

The code above is untested, but should be close.

--
Wayne Morgan
MS Access MVP
"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
Hi there,

I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.

Basically, it works as follows:
- adds records from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
on the second record and halts the code.

I have tried clearing the errors to no avail.

------------------
QUESTION:
Does anyone have any idea why access ignores my error trapping on the
second duplicate record?

Are there any alternative solutions?
------------------

Keep in mind that this code is designed to import over 40 tables which
all have varying field names, each table to be imported is stored in a
recordset called rs.

Private Sub btnImpTable_Click()

Dim TolasSourceTable As String
Dim TolasDestTable As String

Dim rs As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim MyFieldCount As Integer
Dim intcount As Integer

Dim rsDestString As String

Set rs = CurrentDb.OpenRecordset("tblTolasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors.Refresh
TolasSourceTable = "v" & rs!TolasFilename.Value
TolasDestTable = "tbl" & rs!TolasFilename.Value

Set rsSource = CurrentDb.OpenRecordset(TolasSourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenRecordset(TolasDestTable,
dbOpenTable)

MyFieldCount = rsSource.Fields.Count

If rs!TolasFileUpdate = -1 Then

rsSource.MoveLast
Me.intTotalRecords = rsSource.RecordCount
rsSource.MoveFirst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsDest.Delete

ImportStart:

Dim TheFieldName As String
rsDest.AddNew

'MyFieldCount less 1 to take into account field "0"
For intcount = 0 To MyFieldCount - 1

TheFieldName = rsSource(intcount).Name
rsDest(TheFieldName) = rsSource(TheFieldName)
Next intcount

'--------------ERROR TRIGGERS HERE -------
'This is where the error 3022 occurs on the second record!!!

rsDest.Update

'-----------------------------------------

DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsSource.MoveNext
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #2

P: n/a
Hi Wayne,

Thankyou very much for your advice.

I realise that the code is non-conventional, and it did not start out
that way. Basically, I tried every combination I could think of to
try and achieve the desired result.

Err.Clear has no more effect than DBEngine.Errors.Refresh or
Recordset.Errors.Refresh - I also tried placing these in varying
locations with no luck.

What really stumped me is that it all works for the first error but
does ignores the "on error" statement for the next error encountered.

On Error Resume Next works fine, but it does not delete the duplicate
record from the destination therefore just skips the record all
together and moves onto the next one.

I have since implemented an alternative method by using queries which
list all records where the primary key in the "source" rs matches the
primary key in the "destination" rs, it then deletes these records
from the destination recordset prior to adding the records.

The reason I have chosen the approach below to import the records is
to save maintenance time when file structures change, by simply
importing the new table structure and not having to worry about
modifying append and delete queries for 40+ tables.

Majority of these files contain less than 2000 records, so the
efficiency is quite good. I am concerned about processing time when
the code will occasionally have to process over 200k records, but this
is a rare occurance.

Can a comparison be made in terms of the difference in efficiency
between Append queries and DAO?

Also, is ADO any more efficient for this type of application?

Once again thankyou, your time is much appreciated.

Regards,
Nathan
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Z7*****************@newssvr11.news.prodigy.c om>...
You are telling it where to go with the On Error statement. When the first
error occurs, you go there, clear the problem, and Refresh the Errors
Collection. I suspect what you are wanting to do is Err.Clear to clear the
error. I also suspect that the Refresh is canceling your On Error statement.

The code is written in what I would consider to be a non conventional way. I
would consider it to be more conventional to put the error handler at the
end with a Resume Next statement at the end of an If statement that you use
to determine the error number and make your deletion if the error number is
that of a duplicate entry. This would leave error handling in effect for a
general message if the error was something else. At the moment, you're
assuming that the error was a duplicate entry.

Also, there may be a better and faster way to do this. Stepping through a
recordset using DAO is slow. Queries are much quicker. You could use an
Update query to update any records that already exist with the new values in
your source table. Next, run an Append query to append any unmatched records
in the source table. To do this, you would use an Unmatched query as the
source of the Append query.

Example:
Dim strSQL As String, db As DAO.Database
Set db = CurrentDb
'Update Query
strSQL = "UPDATE DestTable INNER JOIN SourceTable ON DestTable.Field1 =
SourceTable.Field1 SET DestTable.Field2 = SourceTable.Field2,
DestTable.Field3 = SourceTable.Field3;"
'Add more fields as needed. The link will be on the ID field that causes the
duplicate.
db.Execute strSQL, dbFailOnError
'Unmatched query
strSQL = "SELECT SourceTable.Field1, SourceTable.Field2, SourceTable.Field3
FROM SourceTable LEFT JOIN DestTable ON SourceTable.Field1= DestTable.Field1
WHERE DestTable.Field1 Is Null;"
'Append Query
strSQL = "INSERT INTO DestTable(Field1, Field2, Field3) " & strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing

If an error does occur, the dbFailOnError switch will cause the error to be
returned. This error can be trapped in the form's Error event.

The code above is untested, but should be close.

--
Wayne Morgan
MS Access MVP
"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
Hi there,

I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.

Basically, it works as follows:
- adds records from rsSource into rsDest
- if it finds a key violation then it deletes the current record from
rsDest and adds the new record from rsSource. This works perfectly -
but only for the first found duplicate record, it brings up the error
on the second record and halts the code.

I have tried clearing the errors to no avail.

------------------
QUESTION:
Does anyone have any idea why access ignores my error trapping on the
second duplicate record?

Are there any alternative solutions?
------------------

Keep in mind that this code is designed to import over 40 tables which
all have varying field names, each table to be imported is stored in a
recordset called rs.

Private Sub btnImpTable_Click()

Dim TolasSourceTable As String
Dim TolasDestTable As String

Dim rs As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim rsDest As DAO.Recordset
Dim MyFieldCount As Integer
Dim intcount As Integer

Dim rsDestString As String

Set rs = CurrentDb.OpenRecordset("tblTolasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors.Refresh
TolasSourceTable = "v" & rs!TolasFilename.Value
TolasDestTable = "tbl" & rs!TolasFilename.Value

Set rsSource = CurrentDb.OpenRecordset(TolasSourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenRecordset(TolasDestTable,
dbOpenTable)

MyFieldCount = rsSource.Fields.Count

If rs!TolasFileUpdate = -1 Then

rsSource.MoveLast
Me.intTotalRecords = rsSource.RecordCount
rsSource.MoveFirst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsDest.Delete

ImportStart:

Dim TheFieldName As String
rsDest.AddNew

'MyFieldCount less 1 to take into account field "0"
For intcount = 0 To MyFieldCount - 1

TheFieldName = rsSource(intcount).Name
rsDest(TheFieldName) = rsSource(TheFieldName)
Next intcount

'--------------ERROR TRIGGERS HERE -------
'This is where the error 3022 occurs on the second record!!!

rsDest.Update

'-----------------------------------------

DBEngine.Errors.Refresh
DAO.Errors.Refresh
rsSource.MoveNext
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #3

P: n/a
I don't know if ADO is any more efficient than DAO. I would be surprised if
there is a significant difference. As far is the difference in speed between
using DAO and using queries, the main thing is whether or not it appears
slow when you're using it. If you have enough records to make a significant
difference, you could put Debug.Print statements in at the start and end of
the routine to print out the current time and check the difference. The
problem is that if you don't have enough records to slow the process down
enough because the Now() function returns time down to the second and you
would really need down to the millisecond. I believe that if you used one of
the Windows API functions instead of Now() that you can get the time more
accurately.

As far as the Resume Next causing a problem, it will depend on where it was
put. You would go to the error handler, delete the duplicate, then do the
Resume Next. The Resume statement automatically clears the error. It is
possible that you were actually receiving two errors, one for the code and
one for the data. The data errors are trapped in the form's Error event. It
may be interesting to put a message box in that event just to see if you're
getting there.

--
Wayne Morgan
MS Access MVP
"Nathan Bloomfield" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
Hi Wayne,

Thankyou very much for your advice.

I realise that the code is non-conventional, and it did not start out
that way. Basically, I tried every combination I could think of to
try and achieve the desired result.

Err.Clear has no more effect than DBEngine.Errors.Refresh or
Recordset.Errors.Refresh - I also tried placing these in varying
locations with no luck.

What really stumped me is that it all works for the first error but
does ignores the "on error" statement for the next error encountered.

On Error Resume Next works fine, but it does not delete the duplicate
record from the destination therefore just skips the record all
together and moves onto the next one.

I have since implemented an alternative method by using queries which
list all records where the primary key in the "source" rs matches the
primary key in the "destination" rs, it then deletes these records
from the destination recordset prior to adding the records.

The reason I have chosen the approach below to import the records is
to save maintenance time when file structures change, by simply
importing the new table structure and not having to worry about
modifying append and delete queries for 40+ tables.

Majority of these files contain less than 2000 records, so the
efficiency is quite good. I am concerned about processing time when
the code will occasionally have to process over 200k records, but this
is a rare occurance.

Can a comparison be made in terms of the difference in efficiency
between Append queries and DAO?

Also, is ADO any more efficient for this type of application?

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.