Aug 31 2021 08:18 AM
Aug 31 2021 08:18 AM
I was trying to distribute my data into multiple named tables within Access. I made a copy of the table and renamed it. Then in the copied table I remove the data I did not need in the copied table. When I went back to the original table my data was gone. What happened?
Aug 31 2021 09:01 AM
We didn't see the process, so this is mostly an informed guess.
One possibility could be that you created copies of the STRUCTURE of the table, but didn't include the data when you made the copies.
Either that or you THOUGHT you were deleting data from one of the copied tables, but were actually deleting the data from the original.
Or a third possibility is that in copying tables and changing their names, you got the renaming out of synch.
There are probably other possibilities, but those three come to mind first.
Go get your most recent back up, the one you made just before starting this copy process. Create another test accdb and repeat the process of making copies, but carefully noting each table's name and the data in it before doing any deleting.
Aug 31 2021 09:48 AM
Aug 31 2021 10:04 AM
Aug 31 2021 11:47 AM
Again, It's impossible to guess what might have happened. Sorry, but data doesn't change in tables independently. If, perhaps you have some sort of relationships defined and enforced with Cascade Delete enabled, that could cause deletion of data in those child, or many side tables.
The fact that you have other copies of the accdb where this problem didn't appear is reassuring, though. Whatever you did in this one is apparently a one-off. Please understand, without a first hand look at the process you went through (which is, of course, impossible) or at the accdb itself, it's guesswork.
Therefore, if you can share a copy of this accdb, someone might be able to identify some potential reasons for the problem.
You will find other sites like UtterAccess and Microsoft Answers, as well as others. A little bingoogling will find them.
Aug 31 2021 11:58 AM
@George Hepworth I'd be glad to share, but the data base is 250,000+records. Which is why I wanted to split the data into separate tables. I do not have (that I know of) any of the apps you mention. A zipped copy of the data is 35 Mb. Let me know how to proceed.
Aug 31 2021 01:21 PM
The deeper we go, the more complex the story becomes, yet clearer a the same time. Your project involves splitting data into a series of essentially duplicate tables, and the reason is that your data has grown to a fairly large size. On the other hand, 250,000 records may or may not be a problem, depending on other factors,
Splitting data into essentially duplicate tables (e.g. "DataPartOne", "DataPartTwo", etc.) is a less than ideal design for a relational database application. Also, the more important factor is the size of the accdb. If it has grown to over ~1.2 or 1.3 GBs, it's too big to work with effectively. Splitting the data into separate tables in that same accdb does no good in relieving that problem anyway. If you move tables into separate accdbs, you incur additional problems. Plus you lose Referential Integrity on top of that. If the accdb hasn't grown that large (i.e. just over 1GB), it probably isn't crucial yet anyway. Regular compact and repair is usually a good idea one way or the other.
Moreover, I'm tempted to suggest that accidently losing data is just one additional downside to splitting tables this way.
But in any event, it seems like there must be a better way to resolve the problem of size. What difficulties are you experiencing that prompted doing this? If the volume of data and the number of users has grown beyond the comfort zone of MS Access, a more forward looking solution could well be migrating to a more robust database engine like SQL Server Express, which has a 10GB limit versus the 2GB limit for accdbs.
The maximum size for an attachment here is 71MB, so yours should be okay if you want to upload it.
And the references I gave were to internet forums, not apps. Search Google or Bing on those names.
Aug 31 2021 02:18 PM
Aug 31 2021 02:32 PM
You should see this at the bottom of the reply form.
I will have to see the data to fully understand the situation.
When you say that the data was removed from the backup files, I have been assuming that you are referring to other accdb files which are backups of the current production accdb. Correct?
Aug 31 2021 04:05 PM
Aug 31 2021 05:54 PM
Aug 31 2021 06:31 PM