Make a copy of a table and rename

L

lovett10

Registered User.
Local time
Today, 00:35
Joined
Dec 1, 2011
Messages
150
I have a button that is supposed to Archive the tables as they get changed every year.

the button is called "Command7" i have a combobox "Combo16" which selects which table to archive. I also have "Text18" which the user will put the date into.

The archived table will be the orignal table name + the date supplied by Text18

Can anyone help?

Thanks in advanced
 
You can use some basic vba code like this

Sub copyAndName()
DoCmd.CopyObject "Database Name", "NewName", acTable, "TableName"
DoCmd.Rename "Old Employees Table", acTable, "Employees"

End Sub
Click to expand...
 
If I may suggest some error handling:

Code: 
Private Sub Command7_Click()
    Dim sTbl As String
    sTbl = Nz(Me.Combo16,"")
    If sTbl <> "" Then
        If CopyTable(sTbl, sTbl & "_" & Me.Text18) Then
            MsgBox sTbl & " copied successfully", vbInformation
        Else
            MsgBox "Failed to copy " & sTbl, vbCritical
        End If
    End If
End Sub


Public Function CopyTable(ByVal strTable As String, ByVal strNewName As String) As Boolean
    On Error GoTo CopyTable_End
    If TableExists(strTable) Then
        If Not TableExists(strNewName) Then
            DoCmd.CopyObject , strNewName, acTable, strTable
            CopyTable = True
        End If
    End If
CopyTable_End:
End Function

Public Function TableExists(ByVal strTable As String) As Boolean
    On Error Resume Next
    Dim tdef As TableDef
    CurrentDb.TableDefs.Refresh
    For Each tdef In CurrentDb.TableDefs
        If tdef.Name = strTable Then
            TableExists = True
            Exit For
        End If
    Next tdef
End Function

You could use a similar For Each tdef In CurrentDb.TableDefs loop to populate the combobox (as a value list):

Code: 
Private Sub SetTableList()
    On Error Resume Next
    Dim sList As String
    Dim tdef As TableDef
    CurrentDb.TableDefs.Refresh
    For Each tdef In CurrentDb.TableDefs
        If Len(sList) > 0 Then sList = sList & ";"
        sList = sList & tdef.Name
    Next tdef
    Combo16.RowSource = sList
End Sub
 
Last edited:
Sorry i think i was unclear..

when i say i want to rename a table i ment a database e.g.

CP001 when they press Command7 i want it to copy CP001 database and rename it with the contents of Text18 added.. e.g.

CP001/2012

Thanks again for the help
 
And if I may suggest an improvement to your naming system:

Code: 
Private Sub Command7_Click()
    Dim sTbl As String
    sTbl = Nz(Me.Combo16,"")
    If sTbl <> "" Then
        If CopyTable(sTbl, sTbl & "_" & Format(Now(),"yyyy_MM_dd_hh_mm")) Then
            MsgBox sTbl & " copied successfully", vbInformation
        Else
            MsgBox "Failed to copy " & sTbl, vbCritical
        End If
    End If
End Sub

Would remove the need for the user to enter the date.
 
You want to export the selected to tables to a new database?

(In a way that's good because copying tables into the same database can quickly get out of hand.)

Either way you'd use something like the Private Sub SetTableList() above to populate the combobox.

But the rest of the code will be quite different.
 
VilaRestal said:
You want to export the selected to tables to a new database?

(In a way that's good because copying tables into the same database can quickly get out of hand.)

Either way you'd use something like the Private Sub SetTableList() above to populate the combobox.

But the rest of the code will be quite different.
Click to expand...


I have a selection of databases that the combobox chooses from. the user will need to select the database using the combobox then simply press the command7 button (labelled Archive) and it will make a copy of that database and rename it with the date so that the original database can be re-used again in 6 months time. (which is how often this will need to be done)

hope this clears it up and thanks again
 
Then just copying files?

If so, reference Microsoft Scripting Runtime and this code:

Code: 
    On Error Resume Next
    Err.Clear
    Dim strSourcePath As String, strSourceName As String, strCopyPath As String, strCopyName As String
    strSourcePath = "C:"
    strSourceName = Nz(Me.Combo16,"")
    strCopyPath = "D:"
    strCopyName = Replace(strSourceName, ".accdb","") & "_" & Format(Now(),"yyyy-MM-dd") & ".accdb"
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    If Right(strCopyPath, 1) <> "\" Then strCopyPath = strCopyPath & "\"
    If Err Then
        MsgBox "Error encountered trying to copy database", vbCritical
    Else    
        Dim fso As New FileSystemObject
        With fso
            If .FolderExists(strSourcePath) Then
                If .FolderExists(strCopyPath) Then
                    If .FileExists(strSourcePath & strSourceName) Then
                        If Not .FileExists(strCopyPath & strCopyName) Then
                            .CopyFile strSourcePath & strSourceName, strCopyPath & strCopyName
                            If Err Then
                                MsgBox "Error encountered trying to copy database", vbCritical
                            Else
                                MsgBox "Database copied successfully", vbInformation
                            End If
                        Else
                            MsgBox "Could not copy database: destination file exists", vbCritical
                        End If
                    Else
                        MsgBox "Could not copy database: destination path does not exist", vbCritical
                    End If
                Else
                    MsgBox "Could not copy database: Source file does not exist", vbCritical
                End If
            Else
                MsgBox "Could not copy database: Source path does not exist", vbCritical
            End If
        End With
    End If

and replace the two paths as appropriate.
 
Last edited:
VilaRestal said:
Then just copying files?

If so, reference Microsoft Scripting Runtime and this code:

Code: 
    On Error Resume Next
    Err.Clear
    Dim strSourcePath As String, strSourceName As String, strCopyPath As String, strCopyName As String
    strSourcePath = "C:"
    strSourceName = Nz(Me.Combo16,"")
    strCopyPath = "D:"
    strCopyName = Replace(strSourceName, ".accdb","") & "_" & Format(Now(),"yyyy-MM-dd") & ".accdb"
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
    If Right(strCopyPath, 1) <> "\" Then strCopyPath = strCopyPath & "\"
    If Err Then
        MsgBox "Error encountered trying to copy database", vbCritical
    Else    
        Dim fso As New FileSystemObject
        With fso
            If .FolderExists(strSourcePath) Then
                If .FolderExists(strCopyPath) Then
                    If .FileExists(strSourcePath & strSourceName) Then
                        If Not .FileExists(strCopyPath & strCopyName) Then
                            .CopyFile strSourcePath & strSourceName, strCopyPath & strCopyName
                            If Err Then
                                MsgBox "Error encountered trying to copy database", vbCritical
                            Else
                                MsgBox "Database copied successfully", vbInformation
                            End If
                        Else
                            MsgBox "Could not copy database: destination file exists", vbCritical
                        End If
                    Else
                        MsgBox "Could not copy database: destination path does not exist", vbCritical
                    End If
                Else
                    MsgBox "Could not copy database: Source file does not exist", vbCritical
                End If
            Else
                MsgBox "Could not copy database: Source path does not exist", vbCritical
            End If
        End With
    End If

and replace the two paths as appropriate.
Click to expand...



Hey, Thanks for the help...

im not sure what to put as the paths.. all the databases are in one .mdb file if that helps sorry if i was being unclear.
 
A database is an mdb file (or an mde file, or accdb, or accde)

Do you mean tables in an mdb file, or databases (mdb files) in a path (a folder on your hard drive)?
 
VilaRestal said:
A database is an mdb file (or an mde file, or accdb, or accde)

Do you mean tables in an mdb file, or databases (mdb files) in a path (a folder on your hard drive)?
Click to expand...


I mean multiple tables in one mdf file.

sorry for the confusion

thanks
 
And where do they need to go? The same database (the same mdb file) or a different one?
 
SetTableList?

That would populate the combobox with the names of all the tables in the database.
 
You must log in or register to reply here.

Similar threads

LarryE
Solved Archiving records instead of deleting
Replies
12
Views
711
Pat Hartman
P
A
Rename Tables Programmatically in MsAccess 2003
Replies
5
Views
255
Mark_
M
A
Combining multiple reports into a PDF export
Replies
2
Views
374
KitaYama
K
D
Solved How to quickly duplicate Tables & associated subforms within a main form
2 3 4
Replies
71
Views
1,652
dibblermail
D
E
Copying records
Replies
2
Views
164
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom