The following query " qryDup2Burn" yields each duplicate file name (having different pathnames):
SELECT DISTINCTROW tmpBurnList.Filename, tmpBurnList.FileSize, tmpBurnList.ModifiedTime, tmpBurnList.Pathname, tmpBurnList.DomainName, tmpBurnList.FileOwner, tmpBurnList.FileType, tmpBurnList.ModifiedDate, tmpBurnList.OK2archive, tmpBurnList.RequestRecovery, tmpBurnList.ArchiveDisc, tmpBurnList.Ok2Del
WHERE (((tmpBurnList.Filename) In (SELECT [Filename] FROM [tmpBurnList] As Tmp GROUP BY [Filename],[FileSize],[ModifiedTime] HAVING Count(*)>1 And [FileSize] = [tmpBurnList].[FileSize] And [ModifiedTime] = [tmpBurnList].[ModifiedTime])))
ORDER BY tmpBurnList.Filename, tmpBurnList.FileSize, tmpBurnList.ModifiedTime;
Where as statement below produces only one file name per duplicate:
DoCmd.CopyObject , "tmpDupNameListALL", acTable, "FileList-empty" ' set the fields in new table
"INSERT INTO tmpDupNameListALL " & _
"SELECT tmpBurnList.* FROM tmpBurnList " & _
"WHERE (((tmpBurnList.Filename) In " & _
"(SELECT [Filename] FROM [tmpBurnList] As Tmp GROUP BY [Filename],[FileSize],[ModifiedTime] " & _
"HAVING Count(*)>1 And [FileSize] = [tmpBurnList].[FileSize] And " & _
"[ModifiedTime] = [tmpBurnList].[ModifiedTime]))) " & _
"ORDER BY tmpBurnList.Filename, tmpBurnList.FileSize, tmpBurnList.ModifiedTime;"
Any Ideas what is missing in the dbs.Execute? My table comes up with unique file names and not the sought after duplicates. I did try the DISTINCTROW key word w/ out any luck. TIA
Have you tried to debug that exact query using tmpBurnList.* as opposed to being explicit? Also, can you figure out which records are NOT being dumped into the new table? The first problem is figuring out what's wrong with the result set. The syntax is right.
OK, two things. First, I confess that I forgot to order my data in the db.execute code by filename. So, the data didn't look the same. Second, after following your advice I cut and pasted my db.execute sql into a query and removed the into table syntax and spaces quotes etc. to make sure I had apples and apples in my compare. That's how I noticed my lack of order and a discrepency of nine files that were captured by the db.execute and not the pasted qry. I am checking out what the story is with these 9 files. Three of the 9 have no matching file name (but diff path name). Thanks for your help; it was a great suggestion. I'll let you know if I find out the mystery of the 9 differing files.
Wierd. I created a table, named tmp9mystery, of the mystery 9 files. Then did a query where I had the original tables with this code:
SELECT * (explicitly called) FROM tmp9mystery, tmpMadeFromqryTestdbexec
My db.execute source returned a list of 33 duplicates where the mystery 9 did appear. The qry method revealeed 22 duplicates with 5,483,532 bytes. Whereas the 33 files returned 7,642,777 bytes. This is a difference of 2,159,245 bytes and 11 files. The list of mystery 9 included 2,161,197 bytes.
Talk about things that make you go "hummphh"! I am glad that my preferred db.execute method got more hits, and in the grand scheme of things a file or two from master list of 12,000 is negligible and MYSTERIOUS.
The source of the mystery 9 is when the query was made into a make table; it lost the 9 records. I'm guessing because no primary key was set nor auto index. Yea, that must be it. Prior my db.execute I copy an empty FileList table with primary keys set up.