Perform automatic backup... Please Help 1

[blarson0]

Hi! I need some advice on how to go about running an automatic backup of my database. The steps I will need to perform are:

1. Copy Oracle-linked table to local table
2. Refresh all relationships and queries
3. Compress the database (Optional)
4. Save and make into a .MDE file
5. Backup the MDE file on several computers on the network

I need to put the MDE file on other computers in case of network problems. I would like to delete the linked table after it is copied, to reduce file size and network traffic. If I do delete the linked table, I must re-link it automatically when the next backup is performed.

This would be no problem for me to do manually, but I am leaving in a few weeks and would like to put it all in one module for the secretaries to run 3 times a day, so all they had to do was click a button and be done. If anyone could give me advice on just one of the steps it would make things go much more smoothly.

Thanks in advance for any help!

-Brad

 

[derik]

Copying the linked database shouldn't be too hard.

1st, turn off access warnings:

DoCmd.SetWarnings False

Next, delete all of the records in the current copy:

DoCmd.RunSQL ("Delete * from TableCopy;&quot

After that, copy from the linked table:

DoCmd.RunSQL ("INSERT INTO TableCopy _
SELECT Table.* FROM Table;&quot


Finally, turn warnings back on:

DoCmd.SetWarnings True


I can't test that out from work, but it should be ok.

As long as you don't delete your copied table, you won't need to reset any relationships. I don't think that you can compress the table without closing down the database (and thus stopping your code), so you might have to nix that one.

I don't know enough VBA to tell you how to make MDE files or distribute them accross a network, but I am sure that somebody here does.

HTH!

-Derik

 

[blarson0]

Thanks Derik!

I think I have found a way to compact the database, using DoCmd.RunCommand(acCmdCompactDatabase), and make an MDE file using DoCmd.RunCommand(acCmdMakeMDEFile), so that takes care of #'s 3 and 4.

Now to save them across a network...

Thanks again!

-Brad

 

[pgk]

Hi Brad,
I tried using the DoCmd.RunCommand acCmdCompactDatabase. But i get an error message saying I cannot use this option by runnig a macro or VB code. How can it be done?

Thanks in advance. Hope it helps. Let me know what happens.
With regards,
PGK