Copy, Paste and Delete records from one table to another

**ADezii** · Jun 7 '10, 01:45 AM

Originally posted by 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.

Code:

    
 '***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"

Code:

On Error Resume Next
CurrentDb.TableDefs.Delete "Data Entry"

DoCmd.CopyObject , "Data Entry", acTable, "Data Entry1"

**timleonard** · Jun 7 '10, 03:38 AM

Originally posted by ADezii

Code:

On Error Resume Next
CurrentDb.TableDefs.Delete "Data Entry"

DoCmd.CopyObject , "Data Entry", acTable, "Data Entry1"

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** · Jun 7 '10, 01:16 PM

Originally posted by timleonard

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?

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

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

**timleonard** · Jun 7 '10, 02:06 PM

Originally posted by ADezii

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

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

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** · Jun 7 '10, 04:28 PM

Originally posted by timleonard

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

Code:

'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** · Jun 8 '10, 01:53 PM

Originally posted by ADezii

Code:

'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

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

**ADezii** · Jun 8 '10, 07:32 PM

Originally posted by timleonard

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

You are quite welcome.

Copy, Paste and Delete records from one table to another

Copy, Paste and Delete records from one table to another

Comment

Comment

Comment

Comment

Comment

Comment

Comment