Upate query trouble

[MikeR]

I'm using an Access 2000 db as the backend for an application. I read in a text file,
parse the data I'm interested in out of the text file, and add it to the database.
I'm using DAO. I was told that the fastest way to check for dupes was to do an
update, and check the count of RecordsAffected.

I can't quite get my head around the logic. The statement I'm using returns a false
count( >0 ) on some records and I can't see why. There are 5 fields that make a
record unique (Call, U_Call, Mode, LDate and freq), altho the db has no index or pk.
I need speed and accuracy.

tmp := mutils.DateToDBFormat(Inrec.lDate) + ' ' + inRec.lTime;
sql := 'UPDATE [LOG] Set Call = "' + inRec.call + '", ' +
' LDate = #' + tmp + '#,' +
' Freq = ' + inRec.freq +
', Mode = "' + inRec.mode + '" ' +
' where u_cALL = "' + inRec.u_call + '" and LDate = #' +
tmp + '# and Mode = "' + inRec.mode + '" and Freq = ' +
inRec.freq + '" and Call = inRec.Call + '";';
dbDAO.Execute(sql);
RCnt := dbDAO.RecordsAffected;

Thanks for looking,
Mike

 

[MikeR]

Is there a better place to ask this? This is the first time I've posted here and
gotten no response at all.
Mike

 

[John W. Vinson]

I'm using an Access 2000 db as the backend for an application. I read in a text file,
parse the data I'm interested in out of the text file, and add it to the database.
I'm using DAO. I was told that the fastest way to check for dupes was to do an
update, and check the count of RecordsAffected.

I can't quite get my head around the logic. The statement I'm using returns a false
count( >0 ) on some records and I can't see why. There are 5 fields that make a
record unique (Call, U_Call, Mode, LDate and freq), altho the db has no index or pk.
I need speed and accuracy.

tmp := mutils.DateToDBFormat(Inrec.lDate) + ' ' + inRec.lTime;
sql := 'UPDATE [LOG] Set Call = "' + inRec.call + '", ' +
' LDate = #' + tmp + '#,' +
' Freq = ' + inRec.freq +
', Mode = "' + inRec.mode + '" ' +
' where u_cALL = "' + inRec.u_call + '" and LDate = #' +
tmp + '# and Mode = "' + inRec.mode + '" and Freq = ' +
inRec.freq + '" and Call = inRec.Call + '";';
dbDAO.Execute(sql);
RCnt := dbDAO.RecordsAffected;

Thanks for looking,
Mike

I can't get my head around the logic either. If you want to check for dupes,
check for dupes using a "Find Duplicates" query. Running an Update to do so is
a VERY expensive extra step.

You can simply *prevent* duplicates in the first place by defining a unique
Index on the fields that constitute a duplicate record; the dups will be
dropped as you append from the file to the table.

It's not clear from the sample code even what *language* you're using - not
VBA clearly. What's the context?

John W. Vinson [MVP]

 

[MikeR]

John W. Vinson wrote:
Thanks for the response, John.

I can't get my head around the logic either. If you want to check for dupes,
check for dupes using a "Find Duplicates" query. Running an Update to do so is
a VERY expensive extra step.

I was given some bum info then. I started out using a DAO recordset FindFirst. For a
15,000 record text file, it took an hour to process, of which 9 minutes was spent in
file I/O and parsing.

You can simply *prevent* duplicates in the first place by defining a unique
Index on the fields that constitute a duplicate record; the dups will be
dropped as you append from the file to the table.

Is an insert the way to go here then? I'd need to know if a dupe occured, so I can
log it for user information.

It's not clear from the sample code even what *language* you're using - not
VBA clearly. What's the context?

Sorry, it didn't seem relevant. It's Delphi.
Mike

 

[John W. Vinson]

John W. Vinson wrote:
Thanks for the response, John.
I was given some bum info then. I started out using a DAO recordset FindFirst. For a
15,000 record text file, it took an hour to process, of which 9 minutes was spent in
file I/O and parsing.

Well, that wasn't what I suggested. What fields constitute a "duplicate" in
this context? You can create a Totals query

SELECT <whatever fields you want to see>
FROM <linked table>
GROUP BY field1, field2, field3, field4
HAVING Count(*) > 1

to see records duplicated on those four fields.

Is an insert the way to go here then? I'd need to know if a dupe occured, so I can
log it for user information.

Then you'll need the query - the index will just discard them with a
(suppressable) warning message "417 records were not added due to key
violations".

Sorry, it didn't seem relevant. It's Delphi.

Thanks - I could read the code, but then I can read German, too (sort of). <g>

John W. Vinson [MVP]

 

[Aaron Kempf]

yeah you should take your business elsewhere

MS is soo busy CENSORING PEOPLE FOR SPEAKIGN THE TRUTH that they can't
answer decent questions from real world users

sorry; MS left reality a decade ago

Is there a better place to ask this? This is the first time I've posted here and
gotten no response at all.
Mike

I'm using an Access 2000 db as the backend for an application. I read in
a text file, parse the data I'm interested in out of the text file, and
add it to the database. I'm using DAO. I was told that the fastest way
to check for dupes was to do an update, and check the count of
RecordsAffected.

I can't quite get my head around the logic. The statement I'm using
returns a false count( >0 ) on some records and I can't see why. There
are 5 fields that make a record unique (Call, U_Call, Mode, LDate and
freq), altho the db has no index or pk. I need speed and accuracy.

tmp := mutils.DateToDBFormat(Inrec.lDate) + ' ' + inRec.lTime;
sql := 'UPDATE [LOG] Set Call = "' + inRec.call + '", ' +
' LDate = #' + tmp + '#,' +
' Freq = ' + inRec.freq +
', Mode = "' + inRec.mode + '" ' +
' where u_cALL = "' + inRec.u_call + '" and LDate = #' +
tmp + '# and Mode = "' + inRec.mode + '" and Freq = ' +
inRec.freq + '" and Call = inRec.Call + '";';
dbDAO.Execute(sql);
RCnt := dbDAO.RecordsAffected;

Thanks for looking,
Mike

 

[Aaron Kempf]

I haven't had ANYTHING take longer than a minute or two in a really really
logn time

maybe you should just use SQL Server; I don't think that the MS Access
chipmunks can count above 10,000

 

[MikeR]

Well, that wasn't what I suggested. What fields constitute a "duplicate" in
this context? You can create a Totals query

No, it wasn't you who suggested it.

SELECT <whatever fields you want to see>
FROM <linked table>
GROUP BY field1, field2, field3, field4
HAVING Count(*) > 1

to see records duplicated on those four fields.

I don't want to add records if they would constitute a dupe of course, so I'd run
that query before inserting anything? Is a select faster than a FindFirst?

Then you'll need the query - the index will just discard them with a
(suppressable) warning message "417 records were not added due to key
violations".

I seem to have come full circle. Let me back up a bit and give you some history. I'm
always worried about making a post so long that no one will read it.
I started by doing a recordset.FindFirst using the 5 fields that uniquely ID a record.
If that came back with a NoMatch I did a recordset.AddNew, filled in the fields and
did a recordset.Update. That took the hour.

Then I was advised to check for dupes using the Update query, and if appropriate,
doing an Insert query. That knocked it down to 30 minutes, but that's still too long.

My need is speed, but I do need to be able to flag a text "record" as a dupe.
How about (pseudoSQL)
Insert into

(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values
(text1, text2, text3, text4, text5, text7, text7) where NOT (field1 = text1 and
field2 = text2 and field3 = text3 and field4 = text4 and field5 = field5) then check
the RecordsAfected.
Or does the where clause gonna evaluate to

where(field1 <> text1 and field2 <> text2 and field3 <> text3 and field4 <> text4 and
field5 <> field5)

which I don't think is gonna work.

Thanks - I could read the code, but then I can read German, too (sort of). <g>

Ich auch, ein venig. Vielen dank!

 

[John W. Vinson]

I seem to have come full circle. Let me back up a bit and give you some history. I'm
always worried about making a post so long that no one will read it.
I started by doing a recordset.FindFirst using the 5 fields that uniquely ID a record.
If that came back with a NoMatch I did a recordset.AddNew, filled in the fields and
did a recordset.Update. That took the hour.

Then I was advised to check for dupes using the Update query, and if appropriate,
doing an Insert query. That knocked it down to 30 minutes, but that's still too long.

My need is speed, but I do need to be able to flag a text "record" as a dupe.
How about (pseudoSQL)
Insert into

(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values
(text1, text2, text3, text4, text5, text7, text7) where NOT (field1 = text1 and
field2 = text2 and field3 = text3 and field4 = text4 and field5 = field5) then check
the RecordsAfected.
Or does the where clause gonna evaluate to

where(field1 <> text1 and field2 <> text2 and field3 <> text3 and field4 <> text4 and
field5 <> field5)

which I don't think is gonna work.

A FindFirst is fast - FOR ONE RECORD.

A Query does your whole recordset in one swell foop.

If you want *fast and no duplicates* then create a unique Index on the five
fields; run an append query from your linked text file; *you're done*.

Lines of code: 0.
Complexity of SQL: absolutely minimal.

If you need specific information about *which* records are duplicates, then
you do need to run a find-duplicates query (on your linked text file). That
could be done prior to the append query, or afterward.

If you're checking for duplicates only between existing records and new
records, a Select query to show the dups, and then an Append query using a
frustrated outer JOIN between the linked file and the internal table would
work:

qryShowDups:

SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7
FROM linkedtable
INNER JOIN table
ON table.field1 = linkedtable.field1
AND table.field2 = linkedtable.field2
AND table.field3 = linkedtable.field3
AND table.field4 = linkedtable.field4
AND table.field5 = linkedtable.field5;

INSERT INTO table(Field1, Field2, Field3, Field4, Field5, Field6, Field7)
SELECT Field1, Field2, Field3, Field4, Field5, Field6, Field7
FROM linkedtable
LEFT JOIN table
ON table.field1 = linkedtable.field1
AND table.field2 = linkedtable.field2
AND table.field3 = linkedtable.field3
AND table.field4 = linkedtable.field4
AND table.field5 = linkedtable.field5
WHERE table.field1 IS NULL;



John W. Vinson [MVP]