WEIRD - Query appends to both table specified and Backup table... 2

[cariengon]

This is way bizarre and I don't have any idea why it's happening. And I need it to stop!!!

In my VBA code, the first thing I do is make a backup copy of the table that I'm appending data to (so I can revert if anything goes wrong during the import process).

'Backup 1001 Consolidated table in case of error and need to restore. If user cancels
' at anytime after this, will need to restore.
DoCmd.CopyObject , "previous_1001 Consolidated Pooled Ins", acTable, "1001 Consolidated Pooled Ins"

I've stepped through my queries and the backup table (previous) is the correct data until I run the append query that appends data to the main query. It is specific in the query that it should append table '1001 Consolidated...'

DoCmd.OpenQuery "0001 Append Discoverer Extracts", acViewNormal, acEdit

When I run this step the append updates BOTH the Main table AND the backup table (previous)!

To test this even further, I ran another query to delete the data that was appended and this query deleted the data in both tables too!

Anyone know how to stop this? No point in creating a backup if it's not a true backup of the data!

Thanks,
Carie

 

[Junior1544]

just a thought... maybe it's making a linked table.. not a backup...

another thought... maybe it's doing the info mod's befor it makes the backup...

just thoughts... though my second thought there is unlikly...

look into it, you may need to put in a doevents to get it to run better... (I don't like doing it, bu some times i've found it was needed...)

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

Carie,

That is indeed strange. I've never used CopyObject, so I don't know much about it, and can't help there. Oh. Unless that table is a linked table. Then all you've done is make a copy of the link, not a copy of the table.

But I will offer this: making a backup by making another table in the same database seems like a shaky strategy to me. For one thing, it will lead to file bloat, which will slow your application significantly. For another, what happens if the file goes south? Then your backup goes along for the ride! Not so safe.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cariengon]

It is a link that I'm making a copy of, so that explains it! I was wondering why the process was going so quickly...

I was thinking that the backup would occur in the frontend database (therefore not slowing the main database process).

I guess what I should do instead is run a complete backup of the 'Backend' database and in case of error I would completely restore that.

OR

Would it work if I included the whole path of the table I was backing up? I'm not sure if this is what I want to do...

I think by going with the full Backend backup, I am taking care of an issue that I have to deal with when I go to compact (which is a step after the import). If I do the backup of the Backend Database as the first step, the user could revert to the original database if something went wrong... Which also saves me of having to try to figure out how the user can revert to the previous data. It's a one time deal!

Thanks for your help!
Carie

 

[Junior1544]

good job! Glad you got every thing worked out

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.