How to duplicate an entire table using code?

[Georgios Liakopoulos]

Hello,
I would like to make a routine that will duplicate a table
programatically (create an exact copy of a table and its data for safety
reasons). Lets say that I have the table's name in a variable like
TABLENAME given after the activation of an event procedure. What's next?

I would appreciate any help.

Giorgos

 

[John W. Vinson]

Hello,
I would like to make a routine that will duplicate a table
programatically (create an exact copy of a table and its data for safety
reasons). Lets say that I have the table's name in a variable like
TABLENAME given after the activation of an event procedure. What's next?

I would appreciate any help.

Giorgos

Well, to do it, you'ld run a MakeTable query

Dim strSQL As String
Dim tablename as String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
<define the variable tablename somehow>
strSQL = "SELECT * FROM [" & tablename & "] INTO [" & tablename & "_BAK];"
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
MsgBox "Backed up " & qd.RecordsAffected & " records to " & tablename & "_BAK"
Proc_Exit:
Exit Sub
Proc_Error:
<do your error handling>
End Sub

HOWEVER!!!

The biggest risk to losing a table is database corruption. Having two tables
in the same database (.mdb or .accdb file) gives you very little protection
against corruption, and a seriously corrupt database will lose BOTH tables.
"Putting all your eggs in one basket" perhaps?

To back up your data, make a copy of the entire database, using regular
Windows Explorer copy, or backup software. Keep the copy external to your
computer - on another computer on the network if you have one, on a "cloud"
backup system such as Carbonite, on a CD or DVD in your bank safe deposit box
or your cousin Joe's filing cabinet; but don't think that having two copies of
your table is doing much.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Georgios Liakopoulos]

John,
Thank you very much for the advices. Yes, you are right about the risk
of data corruption. I already have a RAID 1 array and backup software
through the net to another computer that runs based an automated script
every night.
However, duplicating a table after it has been created and before as
well as after any modifications provides a security point to be able to
restore things if e.g. someone deletes a table by mistake using the
database. So, he deletes the table but the table remains as an invisible
safety copy in case the user says 'oups! how can I undo this?". So, if
someone creates a table (lets say eight hours work) and the deletes the
table before the automated backup . . . well he losses the work!

Thanks again
Giorgos

Hello,
I would like to make a routine that will duplicate a table
programatically (create an exact copy of a table and its data for safety
reasons). Lets say that I have the table's name in a variable like
TABLENAME given after the activation of an event procedure. What's next?

I would appreciate any help.

Giorgos

Well, to do it, you'ld run a MakeTable query

Dim strSQL As String
Dim tablename as String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
<define the variable tablename somehow>
strSQL = "SELECT * FROM ["& tablename& "] INTO ["& tablename& "_BAK];"
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
MsgBox "Backed up "& qd.RecordsAffected& " records to "& tablename& "_BAK"
Proc_Exit:
Exit Sub
Proc_Error:
<do your error handling>
End Sub

HOWEVER!!!

The biggest risk to losing a table is database corruption. Having two tables
in the same database (.mdb or .accdb file) gives you very little protection
against corruption, and a seriously corrupt database will lose BOTH tables.
"Putting all your eggs in one basket" perhaps?

To back up your data, make a copy of the entire database, using regular
Windows Explorer copy, or backup software. Keep the copy external to your
computer - on another computer on the network if you have one, on a "cloud"
backup system such as Carbonite, on a CD or DVD in your bank safe deposit box
or your cousin Joe's filing cabinet; but don't think that having two copies of
your table is doing much.

 

[John Spencer]

The simplest way to do a complete copy of the table including indexes, etc. is
to use the TransferDatabase method. You can export the table to another
database if you wish. You just need to change some argument values.

Public Function fCopyTable(strTableName)
DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentDb().Name,
acTable, strTableName, strTableName, False

'change the second strTableName to strTableName & " Backup" if you want to
'name the copy of the table with "Backup" at the end of the name
End Function

That will make a complete copy of the table and its data in your database. It
will automatically add a number to the table name. So if your table is named
MyTable you will end up with MyTable1. If there is MyTable and MyTable1 then
the new table will be MyTable3

As John said this is not really giving you much in terms of safety other than
allowing you to revert to an earlier version of the data.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hello,
I would like to make a routine that will duplicate a table
programatically (create an exact copy of a table and its data for safety
reasons). Lets say that I have the table's name in a variable like
TABLENAME given after the activation of an event procedure. What's next?

I would appreciate any help.

Giorgos

Well, to do it, you'ld run a MakeTable query

Dim strSQL As String
Dim tablename as String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
<define the variable tablename somehow>
strSQL = "SELECT * FROM ["& tablename& "] INTO ["& tablename& "_BAK];"
Set qd = db.CreateQuerydef("", strSQL) ' create an unnamed query
qd.Execute dbFailOnError
MsgBox "Backed up "& qd.RecordsAffected& " records to "& tablename& "_BAK"
Proc_Exit:
Exit Sub
Proc_Error:
<do your error handling>
End Sub

HOWEVER!!!

The biggest risk to losing a table is database corruption. Having two tables
in the same database (.mdb or .accdb file) gives you very little protection
against corruption, and a seriously corrupt database will lose BOTH tables.
"Putting all your eggs in one basket" perhaps?

To back up your data, make a copy of the entire database, using regular
Windows Explorer copy, or backup software. Keep the copy external to your
computer - on another computer on the network if you have one, on a "cloud"
backup system such as Carbonite, on a CD or DVD in your bank safe deposit box
or your cousin Joe's filing cabinet; but don't think that having two copies of
your table is doing much.

 

[John W. Vinson]

The simplest way to do a complete copy of the table including indexes, etc. is
to use the TransferDatabase method.

aha...!!!

Thanks, John; I didn't realize you could use this method within a single
database.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[David-W-Fenton]

The simplest way to do a complete copy of the table including
indexes, etc. is to use the TransferDatabase method. You can
export the table to another database if you wish. You just need
to change some argument values.

That won't copy relationships, though.

A way to do that is to use the undocumented Application.SaveAsText 6
(thanks, Lyle!). Code to use that is found after my signature.

I have this in production use, but it's not used often (it's used to
make a backup before an elaborate import routine is run), and I
intend to alter it to create the backup in the local PC's %temp%
folder and then move it to the location of the original database.
The reason is that in production use, I found that the first time
you backup the database across the network it's very slow (since it
has to pull everything across to the local workstation and then
write it back). It seems safer and simpler to do the backup on the
local machine, instead of writing it across the wire.

But I haven't gotten back to this yet to make that adjustment.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

Public Function CreateBackup(strMDBName As String, _
strBackupPath As String, _
Optional ysnCompact As Boolean = False) As Boolean
On Error GoTo errHandler
Dim objAccess As Object
Dim strBackupMDB As String
Dim strCompactMDB As String

If Len(Dir(strBackupPath & "\*.*")) = 0 Then
'If Not FSO.FolderExists(strBackupPath) Then
MkDir strBackupPath
End If

Set objAccess = New Access.Application
objAccess.Application.OpenCurrentDatabase strMDBName
strBackupMDB = "Backup" & Format(Now(), "YYYYMMDDhhnnss") & ".mdb"
Debug.Print strBackupPath & "\" & strBackupMDB
' does not work on anything other than Application.CurrentDB()
objAccess.Application.SaveAsText 6, vbNullString, strBackupPath _
& "\" & strBackupMDB
objAccess.Application.Quit
Set objAccess = Nothing

If ysnCompact Then
strCompactMDB = strBackupPath & "\" & "c_" & strBackupMDB
Name strBackupPath & "\" & strBackupMDB As strCompactMDB
DBEngine.CompactDatabase strCompactMDB, strBackupPath _
& "\" & strBackupMDB
Kill strCompactMDB
End If

CreateBackup = (Len(Dir(strBackupPath & "\" & strBackupMDB)) > 0)

exitRoutine:
If Not (objAccess Is Nothing) Then
On Error Resume Next
objAccess.Application.Quit
On Error GoTo 0
Set objAccess = Nothing
End If
Exit Function

errHandler:
Select Case Err.Number
' Path/File access error -- MkDir on a folder that already
exists Case 75
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description, vbExclamation, _
"Error in CreateBackup()"
Resume exitRoutine
End Select
End Function