How to use a macro to copy and rename a table

you can use this command

Dim j, sTable
sTable = "tblScansVote"
For j = 1 To 2
DoCmd.CopyObject , sTable & j, acTable, sTable
Next


can you give more details

How about a Make Table Query and a Delete Query


SELECT tblScans.* INTO tblScansVote1
FROM tblScans;

Just change tblScansVote1 to tblScansVote2 and so on each time you run this query.

And delete data in existing tblScans
DELETE tblScans.*
FROM tblScans;

mx

DatabaseMX - that's what I'm looking at doing.  I was trying to automate it using macros, so that a monkey could do rather than me.

seems it would make sense to have a small table on the side that will hold the names of the tables that have already been created. I suppose this would act as a way to increment the "next" table name as well. Kind of like an index if you will. You could add a date stamp so you know when the archive was created.
Just a thought.
J

Well, when you said macros, not sure if you were talking Macros or VBA code?  So, I went the no code route ...

mx

I haven't done any VBA code.  But willing to give it a try.  I like Jeff's suggestion of having an additional table keep track of the incremental table names.

As Jeff suggested, you will need a to insure that you do not overwrite and existing Scan table.

What ever you do, don't say you want it to be so easy that even a Cave Man could do it :-)

mx

Bob,
What event would you like to use to trigger this automation? How is a vote entered?

Jeff, I may build a basic switchboard that has the reports as choices, then a "Prepare for next Ballot" button that would fire off the query to archive the data.

PS-your counting queries work great!  I've tweaked them a bit, but you got me going the right direction.

"I haven't done any VBA code."

That's why I went the straight query route.  With actual Macros, it might be a little tricky to prompt for and/or keep track of the previous tblScansVoteN number (N). Beyond the straight query approach, you really need a small little user interface to run this ... a combination of a form, table(s) and some vba code.

btw ... Jeff will code for $almon :-)

mx

I think the make table query is the way to go here (Cap) using a brief dlookup + 1 to get your "new" archive table name. You just want an empty table you can put stuff in right?

or we can bypass the Index table alltogether and loop through your list of tables using your TableDefs to get the last archive table...then incrementing that by 1.

So from a process perspective (my forte) can you kind of line out the steps you anticipate taking for an average voting cycle?

By the way, we ALL hate switchboards....but if you must...we can try.

Also, you mentioned reports...what reports?

i will leave it all to you guys...

no stay!! I hate switchboards!!

just one thing though.
i think copyobject is faster than make table

will copy object work if this is a .mde?

also...wouldn't you then have to delete all the records? I guess you could create a templete empty one and copy it giving it the new name.

I figured a switchboard was no-no around here :-), but I may not be the one cranking the macros and queries, so I thought I'd put a basic switchboard up to make it simple for others who have never seen an Access database.

Here’s some more description:
The reports are the results of the voting.  They include how many ballots cast, how many invalid and for what reasons, how many votes need to be elected, and the results of the valid ballots.  The validation is done based on some of the queries that Jeff gave me in another question.

Right now I have macros that are set up based on the number people that are trying to be elected.  So, if we are voting for 6 people, the monkey (or me) clicks the “Voting for 6” macro and it fires off the error checking queries, the summary queries and the two reports.  After we are satisfied that the reports are printed, we will prep the database for the next ballot.  We may have 15-20 times of this.  That’s why I wanted to archive each time ballots are collected and counted.  We are looking at 200-500 ballots each time, so it’s not very big.

ok...so once all the reports are done...you can safely do your archive...and clean out your main table for the next round?

This isn't going to be too bad.

How about we consider this a standalone process for now...later if you want, you can put it at the end of your report stuff.

So you want to go with the index table thing?
if so, create it. Call it what you want. Go ahead and give it an index, tablename and archivedate fields.

We'll write a small function to do a lookup for the max(tablename) field and increment it to get the "next" tablename.

we can then either run an insert into query to create it...which is probably more efficient than copy or create.

we update the index table

we purge the main table

sound about right?

Sounds good to me.  I've created the tblArchivesNames with ID, TableName, and ArchiveDateTime.

"small function to do a lookup for the max(tablename)"??  I'm trying to figure this one out.

start by

dim strTable as string
strTable = dlookup("TableName","tblArchivesNames","ID =" & dmax("ID","tblArchivesNames"))
strTable = "tblScansVote" & Right(strTable, Len(strTable) - 12) + 1

then the insert into query

currentdb.execute "SELECT tblScans.* INTO " & strTable & " FROM tblScans;"

looks like you're working with VB?  This may take me a little bit to figure out.

lol...not really...it's all copy and paste for you really

create a new module  In your VBA window, Insert/Module

save it with a name like mod_Archive

then create a function by typing this in that blank VBA window

Function MyArchive()    then hitting enter

it will put a

End function in for you


now you can paste things into it...we'll do all this in the function....then we'll create a macro that calls the function that you can use in your switchboard.

Easy peasy!!

"I figured a switchboard was no-no around here "

10-4 and copy that.  I have never used the sb since Access 1.0 ... always my own custom menus/toolbars,

mx

"did I leave anything out? "

HOPEFULLY ... the electronic voting machines !!!!

mx

Okay, I've added the module.  When I call the function it I get a "Run-time error '5': Invalid procedure call or argument"
When I click debug, this line is highlighted:
strTable = "tblScansVote" & Right(strTable, Len(strTable) - 12) + 1

if you hold the mouse over strTable...is there a value? I don't think there's anythng in the table yet...so it's not got anything to select the length of

Try putting in the first entry in your archive table to test.

make backups first...so you don't lose data
J

It trips on this line now:
currentdb.execute "insert into tblArchiveNames ( TableName, ArchiveDateTime ) values " & strTable & ", " & Date() & ";"

try

currentdb.execute "insert into tblArchiveNames ( TableName, ArchiveDateTime ) values ('" & strTable & "',  #" & Date & "#)"

currentdb.execute "insert into tblArchiveNames ( TableName, ArchiveDateTime ) values (" & strTable & ", #" & Date() & "#);"

give that a try

This should work:

CurrentDb.Execute "insert into tblArchiveNames ( TableName, ArchiveDateTime ) values (" & strTable & ", #" & Date() & "#);"

I don't know if you guys are trying to be funny (by all posting the same code).  I chuckled :)

But all in all, it works!! (after I added an "s" in the middle of tblArchivesNames

I'll review all the comments in the morning (when I'm not so exhausted) to divide up points.  I think Jeff will get the majority.

Thanks for all the help!  I think this project is just about done!!

Bob!! we at EE don't have a sense of humor that we're aware of.

Coincidence really...we all think alike here.

Can't wait for you to come join us helping others by sharing your brain!!

And by the way...SEE it wasn't that hard!!

Laterzzzzzzzz
J