Coping Access tables

Copper Contributor

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?

31 Replies

@Phil_Tremper 

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.

@George Hepworth I wish what you said is correct, but I made differently named copies.  Went I went to the backup copies they were changed as well.  Iam not sure what is going on.  I have done this before several times and never had a problem.

Unfortunately, because we didn't witness or participate in the process, all we can do is offer suggestions of things for you to consider.

Ultimately, it's the backups which bail us out when things go wrong.
George, I did all the copying of the tables manually. I marked all the data in the tables manually. I don't know how the backup data changed at the same time I was looking and the live table. I only looked at one table at a time. All the tables in Access file are linked via the relationship arrow (I'm not sure how all of them became related, but I assume that has something to do with the problem). I do have other copies of the tables under completely different names in completely different locations and they are what they were when I copied them. Something is going on and I not sure what I need to do to fix this problem.
Is there a user group that discusses writing code for Access data bases other than this one?

@Phil_Tremper 

 

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.

Is there a user group that discusses writing code for Access data bases other than this one?

@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. 

@Phil_Tremper 

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.

 

 

 

 

 

George, The reason for the splitting is for speed of processing. The thing I do not understand is how does the data get removed from the backup files. It make no sense to me. It is only one table. Where do I put the file?

@Phil_Tremper 

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?

 

 

GeorgeHepworth_0-1630445450039.png

 

I'm getting further. I have the total data base zipped. I click the drag and drop/browse and it put my data at the bottom. I try to send the data and it just sits there doing nothing that I can tell. I must need a different interface. I would attach the file to your email, but when I try to reply to your email I get nothing familiar. I am using Goggle chrome. Help.
Please don't send it via email. I doubt an attachment that large would get through.

@Phil_Tremper 

 

If you have a OneDrive or DropBox, can you put the zipped file there and send me a link via email?

 

GHepworth at gpcdata dot com 

here is where if put the file:
\\DESKTOP-04SG9EU\Users\philt\OneDrive
I'm not sure if this is what you want. It is in the email attachment folder.

Phil
Unfortunately, that location is on your desktop (to which I can't connect, of course).

I was hoping you'd have a public OneDrive you can share. Let me think about this a little. There has to be a plan that will work.
Since I am retired I don't need to send data to others. Let me know what you find out.
My email is: tremperpg@comcast.net
Thanks,
Did you get a link to DropBox?