Back up access database

F

FpwL1399

Hi, I'm looking for some code that I can execute on a regular basis
that will backup the tables in my database. Everything that I have has
a date associated to it, so I would like to back up every month or so
(a month isn't concrete yet, so it'd be good if the time span could be
variable) and get rid of the month before it. What I really want it to
do is export every table as a different sheet in Excel and then save it
with the month and year in the file name. Also, I would like the
tables to clear themselves of those dates entries. I would ultimately
want a months worth of data to be left in the database, and then
accumulating up to two months, and then clearing the earlier month and
backing up.

If I could be pointed in the right direction, I may be able to figure
it out by myself.

One other thing, is there anywhere on the internet that I can get a
list of all the functions that VB can do with descriptions or
something...kind of like a manual? Or maybe a book that I could order
to help out?

Thanks in advance.
 
J

John Vinson

Hi, I'm looking for some code that I can execute on a regular basis
that will backup the tables in my database. Everything that I have has
a date associated to it, so I would like to back up every month or so
(a month isn't concrete yet, so it'd be good if the time span could be
variable) and get rid of the month before it. What I really want it to
do is export every table as a different sheet in Excel and then save it
with the month and year in the file name. Also, I would like the
tables to clear themselves of those dates entries. I would ultimately
want a months worth of data to be left in the database, and then
accumulating up to two months, and then clearing the earlier month and
backing up.

If I could be pointed in the right direction, I may be able to figure
it out by myself.
Click to expand...

It would be simpler and safer to simply back up the entire MDB file.

Storing separate tables of monthly data is probably NOT a good idea,
unless you are adding millions of records every month. Access
databases can contain 2 GByte of data, and with proper indexing and
query design, multimillion row tables should be no problem. You're
really better off storing all the data and using Queries to subset it.
One other thing, is there anywhere on the internet that I can get a
list of all the functions that VB can do with descriptions or
something...kind of like a manual? Or maybe a book that I could order
to help out?
Click to expand...

Oodles and scads of them. Check the resources at
http://www.mvps.org/access and at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html


John W. Vinson[MVP]
 
F

FpwL1399

Okay, I had no idea that Access could handle so much data (Perhaps
we'll look at a monthly backup and annual removal of data). But the
main reason for doing this is to preform a monthly review where the
engineers in my company can review and plot all the data and make sure
that everything is still going as planned. Is there a way that I can
just do a monthly export to excel where it will take the last month/30
days worth of data from every table and put each table into a sheet?
 
J

John Vinson

Okay, I had no idea that Access could handle so much data (Perhaps
we'll look at a monthly backup and annual removal of data). But the
main reason for doing this is to preform a monthly review where the
engineers in my company can review and plot all the data and make sure
that everything is still going as planned. Is there a way that I can
just do a monthly export to excel where it will take the last month/30
days worth of data from every table and put each table into a sheet?
Click to expand...

Certainly. Easy in fact. Just export Queries using a date range like

BETWEEN DateAdd("m",-1,Date()) AND Date()

as a criterion. If you're assuming you must have a Table in order to
export to Excel... you don't!

John W. Vinson[MVP]
 
F

FpwL1399

Would I have to write a query for each table that I have or can I write
something that will do a query on each table and export that to a sheet
in excel?
 
J

John Vinson

Would I have to write a query for each table that I have or can I write
something that will do a query on each table and export that to a sheet
in excel?
Click to expand...

You could write some moderately complex VBA code to do so - but the
NEED to do so strongly suggests that your table design needs some
normalization! Do you have tables created "ad hoc" for different
projects? Do you have "lookup" tables (not lookup fields, but small
tables for entering and displaying common values) - and do you want to
export them? What IS the structure of your tables, and what do you
need to export?

John W. Vinson[MVP]
 
F

FpwL1399

Well, I work in a microprocess production facility....we have many many
different types of machines which all can several different processes
and we have to log different information for each machine and each
process. I have it set up so that every machine has it's own table.
We want the operators to have a user interface where they can enter
data into the database for each machine which is me setting up forms
individualized forms for each machine, and a way to navigate from each.


I don't HAVE to export, I just need a way to allow the engineers to
look at the data moth to month.
 
J

John Vinson

Well, I work in a microprocess production facility....we have many many
different types of machines which all can several different processes
and we have to log different information for each machine and each
process. I have it set up so that every machine has it's own table.
We want the operators to have a user interface where they can enter
data into the database for each machine which is me setting up forms
individualized forms for each machine, and a way to navigate from each.
Click to expand...

Is it different *content* of information - or is it different *kinds*
of information?

If all these tables have (or could have) the same structure, same
number of fields of the same datatypes, you would be much better off
using *one* table for this information with a machine ID. And if you
have a many at many relationship from processes to machines (each
machine can involve different processes, and there are processes which
can involve more than one machine) you should consider a three-table
setup: Machines, Processes, Machine_Processes.
I don't HAVE to export, I just need a way to allow the engineers to
look at the data moth to month.
Click to expand...
Do they refuse to look at Access, the data must be in Excel? (not
intended sarcastically, Excel may well be the right tool to look at
the data).

John W. Vinson[MVP]
 
F

FpwL1399

Nope, sorry. Every machine in our facility does a COMPLETELY different
process with different measurments on each step.

Unless access has plotting capabilities to rival access, no they won't
want to use access.
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
E

eos

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George
 
Top