Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

rename tables/ mdbs

Status
Not open for further replies.

kimmole

Technical User
May 9, 2002
49
GB
i need to rename a table with the a date stamp so that each time the code (er sorry...macro) runs it renames a table with a date or number consecutive with previously renamed tables.

ie.
rename table xxx to zzz&(date())



thanks in anticipation.... kimbo:))))))) mr macro....sorry, not clever enough for code.






 
Real Programmers don't use Macros because they have no error-handling capability.

Let's see: In CODE, it would be
Code:
  DoCmd.Rename "Table1_" & Format$(Date, "MMDD"), acTable, "Table1"
A Macro would stupidly name it so the name of the table would look like code.
Therefore, my friend, you Must use code. It's not that scary. Make a macro like this:

Action: Rename
New Name: "Table1_" & Format$(Date, "MMDD")
Object Type: "Table"
Old Name: "Table1"

Save the macro, then run it. Notice the funky table name?!?
Now to fix this, click on the new Macro, then choose from the Access menu:
Tools | Macro | Convert Macros to Visual Basic
This creates a new hunk of code in your Modules called "Converted Macro- Macro1".
Double-click on the new code. Don't worry, it won't automatically run the code!
Then edit the code and take out the quote marks until it looks like the line of code above...
Code:
Option Compare Database
Option Explicit

'------------------------------------------------------------
' Macro1
'------------------------------------------------------------
Function Macro1()

  DoCmd.Rename
[red]""[/red]
Code:
"Table1_"
[red]"[/red]
Code:
 & Format$(Date,
[red]"[/red]
Code:
"MMDD"
[red]"[/red]
Code:
)
[red]"[/red]
Code:
, acTable, "Table1"

End Function

Finally, change your original macro to the following:

Action: RunCode
Function Name: Macro1()

Then, when you double-click your original macro, it will call the piece of code.

I just double-clicked it and my table was renamed to "Table1_0205".

Now, Have fun converting all your macros to code!!
--Shaun Merrill
 
P.S.
There is an alternative syntax that makes code look just like Macros. The Underbar character tells the computer that the next line of code is a continuation of this line of code. It is called, a "Line Continuation Character." Here's the code:

Code:
Function Macro1()

  On Error Resume Next
  DoCmd.Rename _
     NewName:="Table1_" & Format$(Date, "MMDD"), _
     ObjectType:=acTable, _
     OldName:="Table1"
     
End Function

In fact, as you convert more macros, you will realize that all of the macros translate directly to a
"DOCMD." ... something.

It's just not that difficult of a transition.

Welcome to the world of CODE.
--Shaun Merrill
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top