Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

    Angry Unanswered: Trouble with INSERT INTO command

    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
    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;


    Where as statement below produces only one file name per duplicate:

    DoCmd.CopyObject , "tmpDupNameListALL", acTable, "FileList-empty" ' set the fields in new table
    dbs.Execute _
    "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

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Mystery 9 findings:

    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
    WHERE (((tmpMadeFromqryTestdbexec.Filename)=[tmp9mystery]![Filename]));

    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.

  5. #5
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250
    Myster resolved:

    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.

    "Ahha!"

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    WOOT!

    Glad you got that hammered out. That one sounded odd from across the web here..


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •