Backup Tables

[DavRob]

My objective is to create a backup and restore of all the tables in my DB, I have viewed many ways of doing this, most of the threads in the forums relate to exporting from Access into an Excel workbook, I would like to:

Backup - Export the Tables into a specific folder as separate workbooks after over writing the existing table

Restore – Import the tables to the DB (I know how to do this bit)

1. Delete the records in the existing tables (this retains the relationships of the tables)
2. Then append the restored table records into the existing tables

The backup is what I need help with

The reasoning behind this is, if the Customer requests an alteration of the DB, I can make the adjustments and send them the new DB, then they can do a restore of the data

I have tried several methods but cannot achieve my objective

I have successfully used “When Access Closes, VBA Backs up All Tables” by Dr. Gerard Verschuuren, to export and save the tables by creating an Access DB with just the saved tables, but cannot figure out how to import the tables back to my DB



DavRob

 

[Pat Hartman]

This is exactly the reason that we always split the FE (forms/reports/queries/modules) from the BE (tables only). That way, unless you are making a schema change, all you do is make the change to the FE and send that to the customer. You will need to include a relink routine so that the FE can find the BE.

If you are also changing the BE, that becomes more complex. I have a couple of applications that I sell to other companies and so I don't have direct access to distribute updates. For these clients, assuming they are paying for annual maintenance, I create a database for each version upgrade. This adds the columns or tables needed by the new FE. One decision I made at the beginning was to never rename anything or delete anything. A couple of times, a field or table has become obsolete but so far, I've never had a problem with having to split a table because I was wrong about the relationships. So, changes are confined to additions. Occasionally, new tables need to be populated so I either include the necessary starting data or if it comes from some other table, I create update queries.

 

[theDBguy]

Exporting to Excel and importing back into Access could also lead into type conversion errors. I believe it's better to save the tables as Access objects, so you can easily restore them back into Access, which I think you said you already know how to do. As far as porting the data back into the empty tables, you should be able to use an APPEND/INSERT query for that.

 

[Pat Hartman]

@DavRob Even though theDBguy is trying to help you to load your gun, this is a seriously bad idea for lots of reasons. Plus it assumes that you never make schema changes.

 

[sonic8]

most of the threads in the forums relate to exporting from Access into an Excel workbook

no, No, NO, NOOO!! - Stop! Don't do this!
Excel is not suitable as a file format for database backups. It's not even suitable as a general data exchange format, even though many people use it as such (due to the omnipresence of Excel).
Excel will change some data, particularly text that could be interpreted as dates, automatically and without any option of undoing this or reconstructing the original data.
A concrete example of this issue are plot identifiers from the German land registry office, which are treated as dates by Excel and are mangled unrecoverably when exported to Excel.

 

[ebs17]

but cannot figure out how to import the tables back to my DB

Technically:
TransferSpreadsheet acImport or ink tables in Excel spreadsheets and apply an append query. Above all, however, one must observe the order of the tables, which results from the set referential integrity. First, primary keys and thus the records of the corresponding tables must be available before you can insert derived foreign keys.

But like Pat said, this is utter nonsense and very error prone. If you don't master it, the user will be overwhelmed with the smallest problem.
Separate the application into frontend and backend, so the user only needs to exchange the revised frontend. The user's data remains untouched and does not require toggling back and forth.
A lot of work goes into the data of the user. Beware of disturbing or destroying anything.

 

[DavRob]

Thankyou for all the replies

OK I got it
What I was trying to do is not the way to go

I have now split the DB as suggested and setup Linked Table Manager I used the code that I found at https://www.microsoft.com/en-us/mic...automatically-relink-microsoft-access-tables/.

This seems to work but I get error message "There were errors refreshing the table links: Falsein Procedure Refresh Table Links"

The tables refresh ok, I can't work out what is causing the error

Thanks again for all the replies

DavRob

 

[Gasman]

So set a breakpoint and walk through the code.
How many tables do you have?

The procedure appears to show you the error if one occurs?

 

[DavRob]

Hi Gasman
Thanks for the reply I have 8 tables

I have set breakpoints as suggested it breaks at "tdf.RefreshLink and now get Run-time error '3024' could not find file "C:\Users\Dave\Documents\Access_Projectb"

The code is adding the letter "b" at the end of the folder name "C:\Users\Dave\Documents\Access_Projectb"
The correct path is "C:\Users\Dave\Documents\Access_Project"

If I change the folder name it still adds the "b" i.e. "C:\Users\Dave\Documents\Access_Projb"

I have attached the code for review

DavRob

 

[Gasman]

That code does not even compile?
The errant incorrect " from the website stops that?

This line creates the b ?

strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "") - 1)))

Change it to

strBackEnd = Right$(strCon, (Len(strCon) - (InStrRev(strCon, "\") - 1)))

 

[DavRob]

Hi Gasman
Thanks for the reply

The file I down loaded was straight from the web page, I had corrected the " in my module

Thankyou again all is working now

DavRob