Copy, Paste and Delete records from one table to another

[timleonard]

I am new to access and VBA so please bare with me on this...I have a table I am upgrading by adding two new fields and renaming three others. Since I have several users using this database I am trying to create an update button that will copy the table from the original database into the new one and then update or copy the records from the old table to the new. I have managed to get it to work, but was wondering if there is an easier way to do it then what is listed below.

 
 
 '***Is there an easier way to do this part ***
 
  '***Copy and Paste All Records to new table
  DoCmd.OpenTable "Data Entry1", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdCopy
  '*************
  DoCmd.OpenTable "Data Entry", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdDelete
  DoCmd.RunCommand acCmdPaste
 
  DoCmd.OpenTable "Branch #'s1", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdCopy
  '*************
  DoCmd.OpenTable "Branch #'s", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdDelete
  DoCmd.RunCommand acCmdPaste
 
  DoCmd.OpenTable "Setup1", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdCopy
  '*************
  DoCmd.OpenTable "Setup", acNormal, acEdit
  DoCmd.RunCommand acCmdSelectAllRecords
  DoCmd.RunCommand acCmdDelete
  DoCmd.RunCommand acCmdPaste
 
 
  '***Close Tables***
  DoCmd.Close acTable, "Data Entry1", acSaveNo
  DoCmd.Close acTable, "Data Entry", acSaveYes
  DoCmd.Close acTable, "Branch #'s1", acSaveNo
  DoCmd.Close acTable, "Branch #'s", acSaveYes
  DoCmd.Close acTable, "Setup1", acSaveNo
  DoCmd.Close acTable, "Setup", acSaveYes
 
  '***Deletes Table After Imported***
  DoCmd.DeleteObject acTable, "Data Entry1"
  DoCmd.DeleteObject acTable, "Branch #'s1"
  DoCmd.DeleteObject acTable, "Setup1"
 
 

[ADezii]

@timleonard

On Error Resume Next
CurrentDb.TableDefs.Delete "Data Entry"
 
DoCmd.CopyObject , "Data Entry", acTable, "Data Entry1"

[timleonard]

@ADezii
Thanks for the fast reply...

I tried the code above, line 2 states the database engine could not lock table because it is in use and line 4 states that "Data Entry" has relationships and cannot be deleted. I should have mentioned that the table "Data Entry" has relationships. Is there a possibility to do this without deleting the table?

[ADezii]

@timleonard
Under these conditions, must more information must be known concerning tyhe Data Entry Table, as in:

1. What Relationships are this Table involved in?
2. Is Referential Integrity enforced on the above Relationships?
3. Are cascading Deletes enforced on the above Relationships?

[timleonard]

@ADezii
Answers to above.
1. The "Data Entry" table shares relationships with two other tables, "Setup" and "Branch #'s"

2. I don't believe I have any Referential Integrity enforced.

3. There is a delete and an update query tied to the table


Hope this is what you were asking, and thank you for the help

[ADezii]

@timleonard

'DELETE all the Records from the Data Entry Table
CurrentDb.Execute "DELETE * FROM [Data Entry]", dbFailOnError
 
'Execute an APPEND Query that will dump the Records from [Data Entry1]
'into the [Data Entry] Table
DoCmd.SetWarnings False
  DoCmd.OpenQuery "qApdDataEntry1ToDataEntry"
DoCmd.SetWarnings True

[timleonard]

@ADezii
This seems to work...Thanks again for your help

[ADezii]

@timleonard
You are quite welcome.