How to use a macro to copy and rename a table

Bobby Cullen
Bobby Cullen used Ask the Experts™
on
In Access I have a table that I want to copy data from, to a new table and then delete the data in the original table using a macro.  I’ll need to do this multiple times.  So I’ll need to copy to tblScansVote1, tblScansVote2, etc.

The original table is tblScans.  

The situation is that I’m counting ballots.  Each time we take a vote, we will scan the results into tblScans.  After I’ve run queries and reports based on the data in tblScans I want to save the raw data as tblScansVote1, then after the second vote, save the data as tblScansVote2, etc.  So that if anyone has a question on the results, we can go back to the original data to look.  Kinda like an archive of each of the times a vote is taken.

Thanks for your time.
--Bobby Cullen
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:

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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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
Bobby CullenExecutive Pastor

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2006

Commented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

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

mx
Bobby CullenExecutive Pastor

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
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
Top Expert 2006

Commented:
Bob,
What event would you like to use to trigger this automation? How is a vote entered?
Bobby CullenExecutive Pastor

Author

Commented:
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"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
Top Expert 2006

Commented:
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?
Top Expert 2016

Commented:
i will leave it all to you guys...
Top Expert 2006

Commented:
no stay!! I hate switchboards!!
Top Expert 2016

Commented:
just one thing though.
i think copyobject is faster than make table
Top Expert 2006

Commented:
will copy object work if this is a .mde?
Top Expert 2006

Commented:
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.
Bobby CullenExecutive Pastor

Author

Commented:
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.
Top Expert 2006

Commented:
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?
Bobby CullenExecutive Pastor

Author

Commented:
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.
Top Expert 2006

Commented:
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;"
Bobby CullenExecutive Pastor

Author

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

Commented:
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!!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"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
Top Expert 2006
Commented:
function MyArchive()

dim strTable as string

'Create the next archive table name here
strTable = dlookup("TableName","tblArchivesNames","ID =" & dmax("ID","tblArchivesNames"))
strTable = "tblScansVote" & Right(strTable, Len(strTable) - 12) + 1

'then using that archive table name...run the insert into query to populate it
currentdb.execute "SELECT tblScans.* INTO " & strTable & " FROM tblScans;"

'Add the new archive table name and date to the archivenames table
currentdb.execute "insert into tblArchiveNames ( TableName, ArchiveDateTime ) values " & strTable & ", " & Date() & ";"

'Clear out the scans table
currentdb.execute "delete * from tblScans;"

end function

did I leave anything out?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"did I leave anything out? "

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

mx
Bobby CullenExecutive Pastor

Author

Commented:
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
Top Expert 2006

Commented:
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.
Top Expert 2006

Commented:
make backups first...so you don't lose data
J
Bobby CullenExecutive Pastor

Author

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

Commented:

try

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

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

give that a try
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This should work:

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

Bobby CullenExecutive Pastor

Author

Commented:
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!!
Top Expert 2006

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial