make backup of table before edit

[kami]

I have a table that I am updating it via a data entry form, but prior to
updating the table in the form I want to make a backup copy of the table.
When the form opens I want Access to ask for a new table name and copy the
content of current table into the new table as given by the input box prompt.
I am using input box but cannot use the users input for the new table name
since runsql doesn’t accept string variable (StrName) as the new table name
during insert into.

Private Sub Form_Open(Cancel As Integer)
Dim StrName As String
DoCmd.OpenTable table1, acViewPreview ‘Open current tableâ€
StrName = InputBox("Enter new table Name:")
docmd.RunSQL (SELECT Table1.name, Table1.[last name] INTO StrName FROM
Table1) ‘Name the new table using the input box
DoCmd.Close acDefault, table1, acSaveNo, ‘close current table prior to editing
End Sub

Can anyone help and suggest a solution. Thanks

 

[fredg]

I have a table that I am updating it via a data entry form, but prior to
updating the table in the form I want to make a backup copy of the table.
When the form opens I want Access to ask for a new table name and copy the
content of current table into the new table as given by the input box prompt.
I am using input box but cannot use the users input for the new table name
since runsql doesn¡¦t accept string variable (StrName) as the new table name
during insert into.

Private Sub Form_Open(Cancel As Integer)
Dim StrName As String
DoCmd.OpenTable table1, acViewPreview ¡¥Open current table¡¨
StrName = InputBox("Enter new table Name:")
docmd.RunSQL (SELECT Table1.name, Table1.[last name] INTO StrName FROM
Table1) ¡¥Name the new table using the input box
DoCmd.Close acDefault, table1, acSaveNo, ¡¥close current table prior to editing
End Sub

Can anyone help and suggest a solution. Thanks

1) No need to open the table before copying it. Just run the Make
table query.

2) You need to concatenate the variable string into the SQL statement:

strName = inputbox("blah, blah")
DoCmd.RunSQL "SELECT Table1.name, Table1.[last name] INTO " & StrName
& " FROM Table1"

3) Name is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[John Spencer]

How about using the CopyObject command?
Assuming the table you want to copy is named "Employees", you could use

Dim strName as String

strName = InputBox("Enter new table name")
If Len(strName) > 0 THEN
DoCmd.CopyObject , strName, acTable, "Employees"
'Yes the first comma is supposed to be there. The first argument is
'optional and leaving it blank means you want to use the current
'database as the destination for the copy.
End if


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..