hi Paul. i saw your earlier post on 10/20/05. frankly, i don't know what the
RunCommand acCmdBackup action does. i wasn't successful in running it from a
public module or a form module, and i've never had any luck in getting
information on RunCommand actions in Help, so....
personally, i wouldn't be making multiple backup tables in my database, of
one or many tables. working with them is problematic, unless you do such
work completely manually - and who has the time?
remember, "records are cheap". i'd suggest one of the following as a
possible solution: 1) don't delete the "old" records from your table(s);
mark them in some way, with a date stamp or Yes/No field, to indicate that
they're historical records; you can then use that field in queries to filter
out the "old" records from all future actions, or specifically pull them
when you do want them. 2) use a single audit table for each of the tables
that you want "backed up". before deleting unwanted records via a Delete
query, use an Append query to copy those records into the matching audit
table, with a date stamp on each appended record. you can easily set this up
by doing a copy/paste of each table from the database window, choosing
Structure Only in the Paste Table As dialog box. then open the new table in
design view and add a DateStamp field, setting the default value to Date(),
or to Now() if you need the records to be time-stamped as well as
date-stamped.
hth
*******
text from 10/20/05 11:16 AM post:
I have a table called Perf_table. It is updated manually from another "temp"
table using an Append query. This allows the table bound to the data entry
form to be empty at the beginning of each session.
The records appended are then QC'd. Once they pass this level, they are
appended to another final table for review by the decision makers. After
this
last append, I clear out the appended records with a delete query which uses
the checkbox used by the QC person to indicate approval as the creterion for
which records get deleted.
I delete them so that subsequent "appends" to the final table don't get
duplicate records. Is there any way to RunCommand Backup before the delete
query so i can keep "safety" copies of all records?
Thanks
*******
paulpenta said:
Hi Tina,
I had just about the same problem, and your solutiuon looks like it would
work great. But I do have a question. I need to peform this action on 12
different tables in the same .mdb
I was hoping somehow to just use RunCommand backup on the data entry table
before running the append query. Is that do-able?
thanks
tina said:
you don't need VBA to do this, a macro will work fine. but you don't want to
use the CopyObject action. you're not just copying an entire table, you're
using a query to first select the records you want in the new table. you
can't use the CopyObject on the query - it will just copy the query object,
not the query's dataset.
so you created your query to pull the correct records, and turned it into a
make-table query, calling the new table Backup_All_Labor. so far so good. to
use a macro to make the new table and then rename it to include the current
date, set the actions (and their arguments) as follows:
Action: SetWarnings
Warnings On: No
Action: OpenQuery
Query Name: [put the name of your make-table query here]
View: [leave the default setting as is]
Data Mode: [leave the default setting as is]
Action: Rename
New Name: ="Backup_All_Labor_" & Format(Date(),"mmddyyyy")
Object Type: Table
Old Name: Backup_All_Labor
note: i formatted the date, because otherwise your table name would be
Backup_All_Labor_4/8/2005
special characters like slash marks (/) should be avoided in object names,
so the date formatting will give you the following instead:
Backup_All_Labor_04082005
also note: if you run the macro more than once *on the same day*, it will
create the table again, and rename it - overwriting the existing table with
the current date in the name.
hth
Sam said:
I forgot to mention that I had set the macro Action to CopyObject. This
resulted in the New Name field being displayed. That did not work so
I
tried
changing the Action to RunCode. This left a field at the bottom left called
Function Name. I tried putting the VB code into that field and it did not
work either. I get an error saying I may have left out quotation
marks
or
misplaced a comma. Do I need to build something else that holds this code
and then I just call it by name in the macro? Wish I was better at VB.
Sorry. Thanks for your patience.
:
Thanks for the info.
So your original table name is PTOA and you are renaming it to PTOA Roll
(DATE), is that correct? Also, when you open your macro in design
view,
do
you put the "DoCmd.CopyOpject......, "PTOA" string directly into the
New
Name
field in the bottom left side of the macro form? I am leaving the
Destination Database blank because I want the renamed table to
remain
within
the current database. Am I on the right track?
:
hi,
here is code i use to make a year end table from our
vacation time tables
DoCmd.CopyObject , "PTOA" & "Roll" & DatePart("yyyy",
Date), acTable, "PTOA"
you might want just the date.
DoCmd.CopyObject , "PTOA" & Date, acTable, "PTOA"
Lookup the docmd.copyobject in help for more info.
the trick to adding the date to the table name is to use
the concatinator character ampersand (&).
-----Original Message-----
I have a monthly requirement to pull labor data from
linked Oracle tables and
make backup tables containing this data in Access 2003.
I found another
posting in this news group showing how to use the task
manager to kick off a
database/macro so I can automate the backup. I wrote the
query to pull the
data we need. I am now building a macro to run the make
table query. I am
calling the make table Backup_All_Labor. (This is where
I get stuck) Next,
I want to rename the Backup_All_Labor table to something
that includes the
date. How would I rename this backup table in a macro so
that it uses the
current date? I am not too good at VB. Thanks for any
advice you can give
me.
.
