Save As to copy DB object for monthly backup

[Guest]

I have a table that gets updated monthly. Currently I jsut create a copy and
then 'save as' in order to give it a new name, ie, DCPDS_Jan_05, in order to
create monthly backups.

I'm trying to use macros to create more of a program type database. Is there
a way to do this with macros where it will allow me to change the name
everymonth, or will I need to use VB (which I'm not knowlegeable in).

THanks
Laura

 

[Nikos Yannacopoulos]

Laura,

Not clear on whether you want to create a new table, or a whole new .mdb
every month. One way or the other, is there a good reason why you would
need to do this? Best practice is to use one .mdb, one table (with an
extra field for month or date), and keep all your data in one place,
which will save you big trouble when you try to work with data accross
months.
If you still insist on splitting, be a little more specific and you'll
get some specific help.

HTH,
Nikos

 

[Steve Schapel]

Laura,

I agree with Nikos, that what you are asking is extremely unusual and
highly suspicious.

Nevertheless, if I understand you correctly, the CopyObject macro action
would probably be applicable.

 

[Guest]

I need to make a copy of the table only, in the same database. The data
originally comes to us from another department on a monthly basis. So we
save the previous month's info for historical purposes only. Copyobject
forces me to enter the table name. Since the name changes each month to
include the month name, that doesn't work. What I'd like is for the little
'save as' dialog box to open that prompts you to enter a new name.

 

[Nikos Yannacopoulos]

Laura,

I'm still not convinced it's a good idea, but if you insist: it can't be
done through a macro, but it can very easily be done with two lines of
VBA code (run by a command button on a form, or called as a function
from a macro):

dest = "DCPDS_" & Format(Now-Day(Now),"mmm_yy")
docmd.CopyObject , dest ,acTable, "SourceTable"

This will always copy SourceTable (use the actual name here) and anme it
after the previous month to the current date.

HTH,
Nikos

 

[Guest]

Thanks, I will try this.

Laura

Laura,

I'm still not convinced it's a good idea, but if you insist: it can't be
done through a macro, but it can very easily be done with two lines of
VBA code (run by a command button on a form, or called as a function
from a macro):

dest = "DCPDS_" & Format(Now-Day(Now),"mmm_yy")
docmd.CopyObject , dest ,acTable, "SourceTable"

This will always copy SourceTable (use the actual name here) and anme it
after the previous month to the current date.

HTH,
Nikos

 

[Guest]

Nikos,

What does "dest" refer to? When I try to run this, I get invalid outside
procedure error.

Laura

 

[Nikos Yannacopoulos]

Laura,

It's a local variable to hold the destination table name. Maybe it's a
variable declaration requirement issue, try preceding those two lines with:

Dim dest As String

HTH,
Nikos

 

[Guest]

Nikos,

It's still giving me the same error. I'll relook it tomorrow, i'm sure it's
something simple I'm over looking, and my brain is tired now...but thanks for
all your help

Laura

 

[Steve Schapel]

Laura,

... Copyobject
forces me to enter the table name.

This is not true. You can use a CopyObject macro. Why don't you enter
an expression such as this in the New Name argument?...
="DCPDS_" & Format(Date(),"mmm\_yy")

 

[Guest]

Steve,

When I tried your recommendation for the copyobject macro, I was getting a
syntax error so I thought I needed brackets around the expression. However
when I did this it treats the expression as a name and returns an error "PIR
can't find the name '=["DCPDS_" & Format(Date(),"mmm\_yy")]' you entered in
the expression.
I put the brackets around the expression you provided but I still get an
error.

do you have any other advice?

Thanks
Laura

 

[Guest]

I don't know what I did, but I got the module to work...this is what it looks
like,
Dim dest As String
dest = "DCPDS" & Format(Now - Day(Now), "mmm_yy")
DoCmd.CopyObject , dest, acTable, "Current DCPDS"

which is exactly what Nikos advised. Thankyou both as between what I had to
look up in my book and you putting me on the path, it works. Probably fresh
morning coffee helped too

Laura

 

[Nikos Yannacopoulos]

Laura,

Good! I'm sure it's the coffee!

Nikos

I don't know what I did, but I got the module to work...this is what it looks
like,
Dim dest As String
dest = "DCPDS" & Format(Now - Day(Now), "mmm_yy")
DoCmd.CopyObject , dest, acTable, "Current DCPDS"

which is exactly what Nikos advised. Thankyou both as between what I had to
look up in my book and you putting me on the path, it works. Probably fresh
morning coffee helped too

Laura



:

 

[Nikos Yannacopoulos]

Steve,

Don't know if this changed in later versions (it should!), but A2K, at
least, doesn't recognize functions in macro arguments, it just treats
them as text.

Regards,
Nikos

 

[Guest]

Nikos,

I'm researching now on how to get the property description to change as well
when the database is copied. This is what I have (which doesn't work). As
I said, I'm a great copy and paster and awful programmer . Any advice?

Private Sub Command0_Click()
Dim dest As String
Dim dbs As Database, prop As Property
dest = "DCPDS Backup - EOM " & Format(Now - Day(Now), "mmm yy")
DoCmd.CopyObject , dest, acTable, "Current DCPDS"
Set dbs = CurrentDb
Set prop = dbs.CreateProperty
prop.Description = "Contacts" & Format(Now - Day(Now), "mmm yy")
End Sub

 

[Nikos Yannacopoulos]

Laura,

I'm not sure I understand what you are trying to achieve. To behin with,
so we sopeak the same language, you are not copying a database, you are
copying a table. In Access terms, a database is more or less equivalent
to (one or more) access file (mdb, mde etc.), comprising all objects in
it; a table is just one object of a database, that's why you are using
CopyObject to copy it, sprecifying object type acTable (Access VBA
constant for object type: table).

Your code below, attempts to create a property for the database file
(CurrentDb), not the new table; is this what you intended to do? In
either case, what are you trying to achieve, in plain english?
Also, your CreateProperty syntax is incorrect, missing parameters. The
syntax is (look it up in VBA help):
Set property = object.CreateProperty (name, type, value, DDL)
In your case, it would probably be:
Set prop = dbs.CreateProperty("Description", dbText, "Contacts" _
& Format(Now - Day(Now), "mmm yy"))
Still, I'm not sure what you are trying to do.


Nikos

Nikos,

I'm researching now on how to get the property description to change as well
when the database is copied. This is what I have (which doesn't work). As
I said, I'm a great copy and paster and awful programmer . Any advice?

Private Sub Command0_Click()
Dim dest As String
Dim dbs As Database, prop As Property
dest = "DCPDS Backup - EOM " & Format(Now - Day(Now), "mmm yy")
DoCmd.CopyObject , dest, acTable, "Current DCPDS"
Set dbs = CurrentDb
Set prop = dbs.CreateProperty
prop.Description = "Contacts" & Format(Now - Day(Now), "mmm yy")
End Sub


:

 

[Steve Schapel]

Laura,

I am happy to see that you have found a solution using a VBA procedure.
That's good. If you are still interested in pursuing the macro idea,
post back with the exact arguments you tried for the CopyObject action
in your macro. The suggestion I gave you for the New Name argument is
correct, so the problem must be elsewhere.

 

[Steve Schapel]

Nikos,

Sorry to disagree with you on this point. But what you stated is simply
not true. You can use functions, and references to form controls, and
all other valid expression syntax in the arguments of macros. I have
just tested this in Access 97 and Access 2000 and Access 2003. In each
case I was able to easily and successfully use the CopyObject macro to
create a table called DCPDS_Apr_05 based on entering the expression I
gave Laura, namely ="DCPDS_" & Format(Date(),"mmm\_yy") into the New
Name argument of the macro design. This is standard procedure. If it
didn't work for Laura, it must be because she made a mistake with
setting up the macro.

 

[Nikos Yannacopoulos]

Steve,

Thanks for bringing this to my attention and, most importantly,
prventing me from misleading others! You are absolutely right. My stupid
mistake, I had forgotten the = sign at the beginning when I tried it :-(

Sorry!
Nikos

 

[Steve Schapel]

Cheers, Nikos. Thanks.