Results 1 to 10 of 10
  1. #1
    Mike940 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    7

    Data Disappeared after Filtering. Help!

    I have a simple flat file of about 6000 records. There are 32 fields, each in simple text, although 4 are for hyperlinks. I have been using it for a year almost daily. About 3/4 of the records were imported as text from a .dbf a year ago.

    I often filter on a specific field to perform editing on just those records. I make moderately frequent use of sorting and Find, sometimes Find & Replace. I have a few simple queries to show records for a specific field match, sorted. I use these 2-3 times a month. I do nothing more complex. No relationships, no macros, no Vis. Basic.



    I get a simple text file a few times a month from a remote helper. The fields are tab separated.

    Yesterday, I had filtered down the table to records containing one specific field. There were about 420 such records. I was doing simple edits. A file arrived from the helper, about 20 records, using only about 20 of the fields. I made sure the fields I was showing matched the fields in the to-be imported file, then pasted it in. I have done that process before 50 times. Never a problem.

    I then sorted the filter file on its first field and the new imported records disappeared from sight, as they did not share the field I was filtering on. I made a few more edits and all of a sudden the filtered records disappeared. I undid the filter and searched for any instance of the field. Nothing. The record count was now about 420 less than it should be. I made a backup copy, opened that. Records still missing. I opened the original file -- the one I had been working on. The 420 records were still missing. I rebooted. Same results. I downloaded trial copies of 3 recovery programs. They each showed my file with the 420 records missing. No trace of them. The 20 new records just pasted in are missing, too.

    How can I find these missing records?

    Thanks!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    You do DB backups at least weekly don't you? Everyone advises regular backups. Use the latest good backup.
    You might also search the wastebasket. Long shot but worth a try.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    1. filtering does not delete records.
    2. when you say missing, do you mean 'missing' from the filter, or does the record not exist in an unfiltered table?

  4. #4
    Mike940 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    7
    Quote Originally Posted by ranman256 View Post
    1. filtering does not delete records.

    1. I do know about that. Use filters several times a week, sometimes several times a day.

    2. when you say missing, do you mean 'missing' from the filter, or does the record not exist in an unfiltered table?
    Missing from unfiltered table. Gone.

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You do all of this manually, every day? Unbelievable. That's what computers are for!

    Is it possible to have a filter saved on a table? Records might be deleted from one table, but you are saying they are gone from three different databases at the same time? Are you sure the filter is not still enabled somehow?

  6. #6
    Mike940 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    7
    Not sure why you think I'm not using a computer and doing this manually. Most of the entries are by drag and drop text initially and about 1/5 of the data fields are then edited for the normal reasons by normal keyboard editing methods. I'm saying that I made backups of the tables to see if the missing records might show up. They don't. Filter is not saved with file. I have an older backup and it shows a bigger file size. When I open it, it shows a bigger record count.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are you working directly with the table? or through a form?

    Also, does the table have an autonumber primary key and perhaps a default timestamp field? If so, this may help indicate what has happened

    And have your tried compact and repair?

  8. #8
    Mike940 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    7
    Quote Originally Posted by Ajax View Post
    are you working directly with the table? or through a form?

    Also, does the table have an autonumber primary key and perhaps a default timestamp field? If so, this may help indicate what has happened

    And have your tried compact and repair?
    Yes. No luck.

    Working mainly with a form but frequently use the table to see and sometimes edit something from the field above. Use Ctrl+" to copy a field when appropriate.

    The autonumber is not used as a primary key. No default timestamp field.

    Thnx

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I suspect you have lost the data and there is also the possibility the table have become corrupt. Think you will need to revert to a backup. Only other thing I can suggest is to export the data to a .csv file - do not use copy/paste, use the export facilities on the ribbon or a macro/vba code to use the transfertext function. You may recover the missing records that way.

    your comment Filter is not saved with file is unclear - do you mean with the table? because that is what ranman is referring to - go into table design>properties and look at the filter property and make sure it is blank

    Suggest for the future, either develop an import routine or copy the data into a temporary table then append the data in the temporary table to your main table. Also recommend you add an autonumber primary key.

  10. #10
    Mike940 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2016
    Posts
    7
    Thanks, Ajax.

    Tried the your export to .cvs. Worth a try but didn't help. Checked the filter property in table design and it is blank. I do have the autonumber as the primary key. I don't keep it visible because it is not used in daily operations. Sorry I meslead you on that.

    Fortunately, I have a number of backups in .cvs of relevant parts of the file and can reconstruct much of what has gone missing, and I have a .cvs file of about 3/4 of it, although the individual records are not as up-to-date as the missing ones.

    I temporarily forgot that the missing records went missing after I made a simple query that would show only those records but sorted on one of the hyperlink fields. And that query is missing.

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

Similar Threads

  1. Saved imports and exports disappeared from manage data tasks view
    By mmoria2312 in forum Import/Export Data
    Replies: 8
    Last Post: 11-07-2016, 05:07 PM
  2. My Developer Tab disappeared
    By Silvera in forum Access
    Replies: 2
    Last Post: 10-19-2016, 09:26 AM
  3. Replies: 1
    Last Post: 12-02-2015, 03:26 AM
  4. HELP! everything disappeared!
    By kgbo in forum Access
    Replies: 1
    Last Post: 08-19-2013, 02:07 PM
  5. Everything on form disappeared...
    By kaledev in forum Access
    Replies: 4
    Last Post: 04-13-2011, 08:09 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