How to rename table when running CopyObject macro

[Harley Guy]

I have a process that I need to archive past audits of servers. Rather than archive them within the current database, I have created an archive database to copy the past audit tables to.



Issue is that I need to have the user run the CopyObject macro from a command from the main switchboard and have a message box come up to allow a user to name the table to archive. Right now, I have the "New Name" field in the macro empty. I don't want users to access this due to security and to make this process as streamline as possible.

Since I am not highly versed in VB scripting, I do not know how to create a script to do this.

Anybody have any ideas?

Thanks in advance!

[ajetrumpet]

you can't rename an object and copy it in one single line of code. but two could work. something like:

docmd.copyobject , "new name", actable, "source object name"
docmd.rename "new name", actable, "current name"

is that what you're looking for? for an input param, simply use an "inputbox()" function to grab the name from the user and assign a variable to the result.

[Harley Guy]

Thanks, that helped for part of the issue...but now I get a Type Mismatch error # 13 when I run the module. The input box comes up to enter in the new name to the table, I enter it in and then the Error #13 Box comes up.

Here is how I have it coded:

Public Function CopyTable_Click()
On Error GoTo Err_ErrorHandler
Dim strTable As String
strTable = InputBox("Please Enter The New Table Name", "Table Name")
If strTable = vbNullString Then
MsgBox "No table name entered.", vbExclamation
Else
DoCmd.CopyObject strTable, acTable, "Server List"
MsgBox "Table successfully renamed.", vbInformation
End If
Exit_ErrorHandler:
strTable = vbNullString
Exit Function
Err_ErrorHandler:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler
End Function

I'm not sure where the Error Handler is picking up the error. Any suggestions?

[ajetrumpet]

you didn't copy my code correctly. Notice the ", " portion of the copy line.

[Harley Guy]

Well, I am a novice at this part of Access....

But that did it, thanks!