make backup of table before edit - ADO DAO RDO RDS

On Tue, 2 Oct 2007 17:11:00 -0700, kami wrote:

> 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
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

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
..

"kami" <kami@discussions.microsoft.com> wrote in message
news:AA43E7F1-208F-4667-8055-7203AD185FAE@microsoft.com...
>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
>