473,695 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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_Cli ck()

Dim TolasSourceTabl e 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.OpenR ecordset("tblTo lasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors .Refresh
TolasSourceTabl e = "v" & rs!TolasFilenam e.Value
TolasDestTable = "tbl" & rs!TolasFilenam e.Value

Set rsSource = CurrentDb.OpenR ecordset(TolasS ourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenR ecordset(TolasD estTable,
dbOpenTable)

MyFieldCount = rsSource.Fields .Count

If rs!TolasFileUpd ate = -1 Then

rsSource.MoveLa st
Me.intTotalReco rds = rsSource.Record Count
rsSource.MoveFi rst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors .Refresh
DAO.Errors.Refr esh
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(intcou nt).Name
rsDest(TheField Name) = rsSource(TheFie ldName)
Next intcount

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

rsDest.Update

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

DBEngine.Errors .Refresh
DAO.Errors.Refr esh
rsSource.MoveNe xt
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub
Nov 13 '05 #1
3 6892
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.Field 1 =
SourceTable.Fie ld1 SET DestTable.Field 2 = SourceTable.Fie ld2,
DestTable.Field 3 = SourceTable.Fie ld3;"
'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.Fie ld1, SourceTable.Fie ld2, SourceTable.Fie ld3
FROM SourceTable LEFT JOIN DestTable ON SourceTable.Fie ld1= DestTable.Field 1
WHERE DestTable.Field 1 Is Null;"
'Append Query
strSQL = "INSERT INTO DestTable(Field 1, 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.goo gle.com...
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_Cli ck()

Dim TolasSourceTabl e 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.OpenR ecordset("tblTo lasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors .Refresh
TolasSourceTabl e = "v" & rs!TolasFilenam e.Value
TolasDestTable = "tbl" & rs!TolasFilenam e.Value

Set rsSource = CurrentDb.OpenR ecordset(TolasS ourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenR ecordset(TolasD estTable,
dbOpenTable)

MyFieldCount = rsSource.Fields .Count

If rs!TolasFileUpd ate = -1 Then

rsSource.MoveLa st
Me.intTotalReco rds = rsSource.Record Count
rsSource.MoveFi rst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors .Refresh
DAO.Errors.Refr esh
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(intcou nt).Name
rsDest(TheField Name) = rsSource(TheFie ldName)
Next intcount

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

rsDest.Update

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

DBEngine.Errors .Refresh
DAO.Errors.Refr esh
rsSource.MoveNe xt
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #2
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.Error s.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 "destinatio n" 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******* **********@news svr11.news.prod igy.com>...
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.Field 1 =
SourceTable.Fie ld1 SET DestTable.Field 2 = SourceTable.Fie ld2,
DestTable.Field 3 = SourceTable.Fie ld3;"
'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.Fie ld1, SourceTable.Fie ld2, SourceTable.Fie ld3
FROM SourceTable LEFT JOIN DestTable ON SourceTable.Fie ld1= DestTable.Field 1
WHERE DestTable.Field 1 Is Null;"
'Append Query
strSQL = "INSERT INTO DestTable(Field 1, 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.goo gle.com...
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_Cli ck()

Dim TolasSourceTabl e 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.OpenR ecordset("tblTo lasFiles", dbOpenTable)

Do While Not rs.EOF

DBEngine.Errors .Refresh
TolasSourceTabl e = "v" & rs!TolasFilenam e.Value
TolasDestTable = "tbl" & rs!TolasFilenam e.Value

Set rsSource = CurrentDb.OpenR ecordset(TolasS ourceTable,
dbOpenDynaset)
Set rsDest = CurrentDb.OpenR ecordset(TolasD estTable,
dbOpenTable)

MyFieldCount = rsSource.Fields .Count

If rs!TolasFileUpd ate = -1 Then

rsSource.MoveLa st
Me.intTotalReco rds = rsSource.Record Count
rsSource.MoveFi rst

Do While Not rsSource.EOF

On Error GoTo Errhandler
GoTo ImportStart
Errhandler:
DBEngine.Errors .Refresh
DAO.Errors.Refr esh
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(intcou nt).Name
rsDest(TheField Name) = rsSource(TheFie ldName)
Next intcount

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

rsDest.Update

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

DBEngine.Errors .Refresh
DAO.Errors.Refr esh
rsSource.MoveNe xt
Loop

rsSource.Close
rsDest.Close
End If

rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #3
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.goo gle.com...
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.Error s.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 "destinatio n" 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
3376
by: Pete | last post by:
I'm trying to improve my code so that when I open a recordset object, I can absolutely guarantee it is closed and is set = Nothing. I have read some old threads and they all say to use the functional equivalent of the following code: Public Sub CloseRecordset() On Error GoTo Sub_Error Dim rs As Recordset rs.FindFirst "This Will Error"
8
13618
by: Richard Hollenbeck | last post by:
I have two functions (one using the other) where I want to pass into it the character A, B, C, D, or F and have it return a Double indicating the minimum score it takes to get that grade. For example, here's a stipped-down version of the first function: private function getComment() Select Case txtTotal ' the value in the text box txtTotal in the report Case Is >= minimumScore(B) getComment = "Good work!" end select.
2
3012
by: Steve Jorgensen | last post by:
When writing VB or VBA code that works with databases or other external libraries that cannot be trusted to automatically do the right thing when references to their objects are arbitrarily released, some thought must be put into how to make sure the objects will all be closed and released in the correct order, even in the result of an error. This requirement can make our code really ugly, even following the best of commonly known best...
4
9446
by: Keith | last post by:
I have the following code in the On No Data event of a report: **** On Error GoTo err_trap MsgBox "No items matching criteria.", vbInformation, gcApplication Cancel = True err_trap: If Err.Number = 2501 Then Exit Sub
6
8192
by: sara | last post by:
I have a procedure to automate bringing several Excel files into our Access tables, on a daily basis. The problem is that if the user has a problem, and tries to run the import again (maybe 3 files imported then there was a data problem and they want to re-import after fixing the problem), I can't get the Error handling to fire if the user is attempting to import duplicate key records. Message when I try to import records already on...
8
13097
by: g_man | last post by:
I am trying trap Runtime error 3022 (duplicates) in the click event of a command button that closes the form. I have code in the Form_Error event that does a good job of providing a more meaningful error message than the default. It works in every situation except when the user clicks the close button. I am using Me.Dirty=False to force a save but if there are duplicates I just get the standard Runtime 3022 error message. I am wondering...
7
7787
by: Jan | last post by:
Hi: When I searched the newsgroup for this problem, I saw two or three instances of the question being asked, but it was never answered. Not too promising, but here goes: I have a form with four subforms, and bit of code that cycles through the data in the subform (bound to a local temp table) and writes it to a table on the server. This code has run at the client with no problems for over a year.
2
19473
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
2897
hyperpau
by: hyperpau | last post by:
Before anything else, I am not a very technical expert when it comes to VBA coding. I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com). Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge. I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that...
0
8565
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8977
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8822
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7658
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5838
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4339
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2997
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2269
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1971
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.