473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access 97 bogus duplicate records

I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao transaction
that executes a number of sql statements (about 30 in all) to append to
various tables, delete records, copy from table A to table B etc, ultimately
downloading to a series of text files that contain 'processed data'.

The issue is that on the odd occasion (this process is executed every
business day and this issue occurs roughly once every 2 to 3 months) the
process goes totally haywire and some of the tables end up with thousands
and thousands of duplicate records. Like today a table that should have had
a couple of thousand records ended up with over 800,000 records!!! When
this was de-duped we were back to a couple of thousand. This doesnt appear
to be an issue with the code. Firstly I have checked it and secondly this
only happens on the odd occasion. It is as if for some reason Access
repeats the same INSERT.. statement over and over and over again even though
there are no loops in the code that is being executed. Possibly this is to
do with recursive queries but even this doesnt seem to be the case based on
the logic the application uses.

Has anyone out there come across this issue or know of what could be causing
this?

Nov 13 '05 #1
4 1924
Hi Andrew.

Never seen anything like that, but there are a few things that might be
worth checking out.

The first thing that comes to mind is to check the error handling. Is there
any conceivable way that an error could be occurring WITHOUT a rollback? If
so, this leaves an uncommitted level of transaction. If the process were
then repeated (or continued/picked up by another routine), it might be
possible to end up with a situation where the original uncommitted
transaction that you thought was abandoned is actually being committed, and
so the effect appears to be that the process has been duplicated.

Presumably you are using dbFailOnError with all executes. There are some
weird things that happen if you don't. (For example, if you execute a
mal-formed append query statement without dbFailOnError, Access will report
zero RecordsAffected, even though records were appended.)

Because these rare/intermittent things can be very hard to trace, it may be
worth adding some logging to the procedures, logging the beginning of each
transaction, each SQL string being executed, the RecordsAffected for each
one, and the end result (CommitTrans or Rollback). The log could be
invaluable once it goes bad again. (Naturally you will need to ensure that
the logging is outside all transaction levels.)

There are other possible issues that might be worth considering. For
example, there can be strange side-effects from closing the default
workspace, or from using CurrentDb instead of ws(0).

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andrew Chanter" <he****@radsolutions.com.au> wrote in message
news:ds******************@news-server.bigpond.net.au...
I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao
transaction that executes a number of sql statements (about 30 in all) to
append to various tables, delete records, copy from table A to table B etc,
ultimately downloading to a series of text files that contain 'processed
data'.

The issue is that on the odd occasion (this process is executed every
business day and this issue occurs roughly once every 2 to 3 months) the
process goes totally haywire and some of the tables end up with thousands
and thousands of duplicate records. Like today a table that should have
had a couple of thousand records ended up with over 800,000 records!!!
When this was de-duped we were back to a couple of thousand. This doesnt
appear to be an issue with the code. Firstly I have checked it and
secondly this only happens on the odd occasion. It is as if for some
reason Access repeats the same INSERT.. statement over and over and over
again even though there are no loops in the code that is being executed.
Possibly this is to do with recursive queries but even this doesnt seem to
be the case based on the logic the application uses.

Has anyone out there come across this issue or know of what could be
causing this?

Nov 13 '05 #2
Hi Andrew.

Never seen anything like that, but there are a few things that might be
worth checking out.

The first thing that comes to mind is to check the error handling. Is there
any conceivable way that an error could be occurring WITHOUT a rollback? If
so, this leaves an uncommitted level of transaction. If the process were
then repeated (or continued/picked up by another routine), it might be
possible to end up with a situation where the original uncommitted
transaction that you thought was abandoned is actually being committed, and
so the effect appears to be that the process has been duplicated.

Presumably you are using dbFailOnError with all executes. There are some
weird things that happen if you don't. (For example, if you execute a
mal-formed append query statement without dbFailOnError, Access will report
zero RecordsAffected, even though records were appended.)

Because these rare/intermittent things can be very hard to trace, it may be
worth adding some logging to the procedures, logging the beginning of each
transaction, each SQL string being executed, the RecordsAffected for each
one, and the end result (CommitTrans or Rollback). The log could be
invaluable once it goes bad again. (Naturally you will need to ensure that
the logging is outside all transaction levels.)

There are other possible issues that might be worth considering. For
example, there can be strange side-effects from closing the default
workspace, or from using CurrentDb instead of ws(0).

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andrew Chanter" <he****@radsolutions.com.au> wrote in message
news:ds******************@news-server.bigpond.net.au...
I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao
transaction that executes a number of sql statements (about 30 in all) to
append to various tables, delete records, copy from table A to table B etc,
ultimately downloading to a series of text files that contain 'processed
data'.

The issue is that on the odd occasion (this process is executed every
business day and this issue occurs roughly once every 2 to 3 months) the
process goes totally haywire and some of the tables end up with thousands
and thousands of duplicate records. Like today a table that should have
had a couple of thousand records ended up with over 800,000 records!!!
When this was de-duped we were back to a couple of thousand. This doesnt
appear to be an issue with the code. Firstly I have checked it and
secondly this only happens on the odd occasion. It is as if for some
reason Access repeats the same INSERT.. statement over and over and over
again even though there are no loops in the code that is being executed.
Possibly this is to do with recursive queries but even this doesnt seem to
be the case based on the logic the application uses.

Has anyone out there come across this issue or know of what could be
causing this?

Nov 13 '05 #3
On Wed, 03 Aug 2005 12:07:37 GMT, "Andrew Chanter"
<he****@radsolutions.com.au> wrote:

A well-placed unique index would make this impossible.
-Tom.

I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao transaction
that executes a number of sql statements (about 30 in all) to append to
various tables, delete records, copy from table A to table B etc, ultimately
downloading to a series of text files that contain 'processed data'.

The issue is that on the odd occasion (this process is executed every
business day and this issue occurs roughly once every 2 to 3 months) the
process goes totally haywire and some of the tables end up with thousands
and thousands of duplicate records. Like today a table that should have had
a couple of thousand records ended up with over 800,000 records!!! When
this was de-duped we were back to a couple of thousand. This doesnt appear
to be an issue with the code. Firstly I have checked it and secondly this
only happens on the odd occasion. It is as if for some reason Access
repeats the same INSERT.. statement over and over and over again even though
there are no loops in the code that is being executed. Possibly this is to
do with recursive queries but even this doesnt seem to be the case based on
the logic the application uses.

Has anyone out there come across this issue or know of what could be causing
this?


Nov 13 '05 #4
On Wed, 03 Aug 2005 12:07:37 GMT, "Andrew Chanter"
<he****@radsolutions.com.au> wrote:

A well-placed unique index would make this impossible.
-Tom.

I have been working with an A97 database that performs a data processing
function. It imports data from a flat text file then uses a dao transaction
that executes a number of sql statements (about 30 in all) to append to
various tables, delete records, copy from table A to table B etc, ultimately
downloading to a series of text files that contain 'processed data'.

The issue is that on the odd occasion (this process is executed every
business day and this issue occurs roughly once every 2 to 3 months) the
process goes totally haywire and some of the tables end up with thousands
and thousands of duplicate records. Like today a table that should have had
a couple of thousand records ended up with over 800,000 records!!! When
this was de-duped we were back to a couple of thousand. This doesnt appear
to be an issue with the code. Firstly I have checked it and secondly this
only happens on the odd occasion. It is as if for some reason Access
repeats the same INSERT.. statement over and over and over again even though
there are no loops in the code that is being executed. Possibly this is to
do with recursive queries but even this doesnt seem to be the case based on
the logic the application uses.

Has anyone out there come across this issue or know of what could be causing
this?


Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is...
49
by: Yannick Turgeon | last post by:
Hello, We are in the process of examining our current main application. We have to do some major changes and, in the process, are questionning/validating the use of MS Access as front-end. The...
0
by: Andrew Chanter | last post by:
I have been working with an A97 database that performs a data processing function. It imports data from a flat text file then uses a dao transaction that executes a number of sql statements (about...
46
by: Adam Turner via AccessMonster.com | last post by:
If I had a field called "Name" in an Access table "Contact Info", and the field contained VBScript... Function Main(rstFields) Main = rstFields.Item("FirstName").Value End Function 1. How do...
24
by: cassetti | last post by:
Here's the issue: I have roughly 20 MS excel spreadsheets, each row contains a record. These records were hand entered by people in call centers. The problem is, there can and are duplicate...
15
by: Cheryl Langdon | last post by:
Hello everyone, This is my first attempt at getting help in this manner. Please forgive me if this is an inappropriate request. I suddenly find myself in urgent need of instruction on how to...
16
by: RichardP | last post by:
Hi there everyone - I'm new to this forum. I am having an issue when running an application from an instance of Access which has been started through automation (early or late bound, makes no...
5
by: Kip | last post by:
I have an office with approx 8 people. I have used Access with a Form on my personal PC for client records. I was wondering if I could put the Access table on a server and put shortcuts on each...
6
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.