Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2021
    Posts
    5

    Docmd.transfertext creating a few duplicate records


    I've bumped into a odd situation. Not sure where to turn.

    The database code creates some tables specifically for the purpose of being in the correct layout format to be exported to CSV, for import into another application. the Transfertext command has been used for this for some years without fail.

    This week, it began duplicating just a few records. The source table has 6,362 records (and no dups - i checked). The resulting csv export file has 6,373. I found the 11 duplicates. They are in the upper middle of the export file, 11 contiguous records that are simply repeated as in A, B, C, D, D, E, E, F, F, G, G,... and then it continues with the rest of the file correctly after those 11 records.

    I copied/ pasted the dups to Word and the character count is exactly the same, so each set of two records is identical.

    The 11 records are not new to the database, although one or more of the fields within those records may have changed in the past week. Could some hidden character somehow trigger a transfertext command to duplicate the record?? I was thinking of shaving off characters from the dup'd records to see if the issue disappeared. Is that idea folly?

    Any ideas??? Thank you.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    The hidden / non-printing characters are a distinct possibility.
    Export the records to something like Notepad ++ to see exactly what is in there.

    I had issues with a weird "-" hyphen character cut and paste into a field from a pdf, it stopped a process dead in it's tracks and took ages to track down, because it 'looked ok'!
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Join Date
    Aug 2021
    Posts
    5
    Thanks Minty. I'll pursue that!

  4. #4
    Join Date
    Aug 2021
    Posts
    5

    Transfertext result solved with compact/ repair.

    This database is set up with a front-end on various clients and a single, shared backend with the tables. I removed some of the description to see if the problem went away by removing some weird, hidden characters. And the oddest thing happened: the export produced a record as I had just altered it, and a second record (with the same key field) still unaltered!

    This made me think that something was weird in the table itself and not the transfertext function.

    First step was to open the backend db and do a repair/ compact. That fixed it. I have the front-end automatically repair/ compact upon exiting, but I now know that it does not repair/ compact the backend. That must be opened/ closed specifically for that to occur.

    Guess I'll just have to remember to do that occasionally - unless someone knows a way to force that action from the front-end db.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You seem to have found a solution for the current issue. My question is how did the "bad characters or whatever" get into those records in the first place.

    I recall assisting a poster in this forum several years ago where he had document titles that looked to be duplicates. On more detailed examination there were some unprintable characters in some records. I think (not sure) there was a scanner involved that may have been the source of the problem.

  6. #6
    Join Date
    Aug 2021
    Posts
    5
    Quote Originally Posted by orange View Post
    You seem to have found a solution for the current issue. My question is how did the "bad characters or whatever" get into those records in the first place.
    There were no bad characters - just a messed up table that needed a repair/ compact processed. Minty had suggested that as a possible cause, which made sense because of the specific data that we're handling. But in the end, it was just a table that needed repaired.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    There are a few utilities to automate the compacting of the back-end, please feel free to use mine if you wish:
    http://forestbyte.com/ms-access-util...end-compacter/
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Aug 2021
    Posts
    5
    Quote Originally Posted by Gicu View Post
    There are a few utilities to automate the compacting of the back-end, please feel free to use mine if you wish:
    http://forestbyte.com/ms-access-util...end-compacter/
    Cheers,
    Vlad
    Thank you so much, Vlad! I often see warnings about auto-compact/ repair on exit, but I've never had an issue with it. On the contrary, in its absence we seem to bump into occasional table issues and the db also gets quite inflated.

    Best regards,
    Matt.

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Most problems with compact on close happen when you have the back-end on a network share (which obviously is the case for most applications to allow sharing) as any network interruption during compacting may\will result in a corrupted file. That is why most developers would copy the inflated file to a local drive, compact it there then move it back to its original location (that is what my utility does as well).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-26-2019, 05:21 PM
  2. DoCmd.TransferText
    By mp3909 in forum Access
    Replies: 3
    Last Post: 07-03-2019, 05:22 AM
  3. docmd.TransferText WITHOUT Import spec?
    By markcrobinson in forum Access
    Replies: 1
    Last Post: 08-07-2017, 03:37 PM
  4. DoCmd.TransferText Function issue
    By nick243 in forum Import/Export Data
    Replies: 7
    Last Post: 07-19-2016, 12:00 PM
  5. DoCmd.TransferText only if file is new
    By Coolpapabell in forum Programming
    Replies: 1
    Last Post: 10-14-2009, 01:57 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums