473,837 Members | 1,493 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Copy, Paste and Delete records from one table to another

52 New Member
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.  '***Is there an easier way to do this part ***
  4.  
  5.   '***Copy and Paste All Records to new table
  6.   DoCmd.OpenTable "Data Entry1", acNormal, acEdit
  7.   DoCmd.RunCommand acCmdSelectAllRecords
  8.   DoCmd.RunCommand acCmdCopy
  9.   '*************
  10.   DoCmd.OpenTable "Data Entry", acNormal, acEdit
  11.   DoCmd.RunCommand acCmdSelectAllRecords
  12.   DoCmd.RunCommand acCmdDelete
  13.   DoCmd.RunCommand acCmdPaste
  14.  
  15.   DoCmd.OpenTable "Branch #'s1", acNormal, acEdit
  16.   DoCmd.RunCommand acCmdSelectAllRecords
  17.   DoCmd.RunCommand acCmdCopy
  18.   '*************
  19.   DoCmd.OpenTable "Branch #'s", acNormal, acEdit
  20.   DoCmd.RunCommand acCmdSelectAllRecords
  21.   DoCmd.RunCommand acCmdDelete
  22.   DoCmd.RunCommand acCmdPaste
  23.  
  24.   DoCmd.OpenTable "Setup1", acNormal, acEdit
  25.   DoCmd.RunCommand acCmdSelectAllRecords
  26.   DoCmd.RunCommand acCmdCopy
  27.   '*************
  28.   DoCmd.OpenTable "Setup", acNormal, acEdit
  29.   DoCmd.RunCommand acCmdSelectAllRecords
  30.   DoCmd.RunCommand acCmdDelete
  31.   DoCmd.RunCommand acCmdPaste
  32.  
  33.  
  34.   '***Close Tables***
  35.   DoCmd.Close acTable, "Data Entry1", acSaveNo
  36.   DoCmd.Close acTable, "Data Entry", acSaveYes
  37.   DoCmd.Close acTable, "Branch #'s1", acSaveNo
  38.   DoCmd.Close acTable, "Branch #'s", acSaveYes
  39.   DoCmd.Close acTable, "Setup1", acSaveNo
  40.   DoCmd.Close acTable, "Setup", acSaveYes
  41.  
  42.   '***Deletes Table After Imported***
  43.   DoCmd.DeleteObject acTable, "Data Entry1"
  44.   DoCmd.DeleteObject acTable, "Branch #'s1"
  45.   DoCmd.DeleteObject acTable, "Setup1"
  46.  
  47.  
Jun 6 '10 #1
7 7720
ADezii
8,834 Recognized Expert Expert
@timleonard
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. CurrentDb.TableDefs.Delete "Data Entry"
  3.  
  4. DoCmd.CopyObject , "Data Entry", acTable, "Data Entry1"
Jun 7 '10 #2
timleonard
52 New Member
@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?
Jun 7 '10 #3
ADezii
8,834 Recognized Expert Expert
@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?
Jun 7 '10 #4
timleonard
52 New Member
@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
Jun 7 '10 #5
ADezii
8,834 Recognized Expert Expert
@timleonard
Expand|Select|Wrap|Line Numbers
  1. 'DELETE all the Records from the Data Entry Table
  2. CurrentDb.Execute "DELETE * FROM [Data Entry]", dbFailOnError
  3.  
  4. 'Execute an APPEND Query that will dump the Records from [Data Entry1]
  5. 'into the [Data Entry] Table
  6. DoCmd.SetWarnings False
  7.   DoCmd.OpenQuery "qApdDataEntry1ToDataEntry"
  8. DoCmd.SetWarnings True
Jun 7 '10 #6
timleonard
52 New Member
@ADezii
This seems to work...Thanks again for your help
Jun 8 '10 #7
ADezii
8,834 Recognized Expert Expert
@timleonard
You are quite welcome.
Jun 8 '10 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2574
by: Sean Howard | last post by:
Dear All, As is my want I need to do something in Access that seems simple but cannot fathom out. I have main form with two subforms, both datasheets with an almost identical table structure. I want to add the functionality to copy records from subformA to subformB WITHOUT USING COPY/PASTE. The reasonfor this is that I must
6
8168
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join" in another table but I have not found what the field criteria should look like? 2: And if/when I succeed I should further like to build a new record (with
4
21647
by: Legendary Pansy | last post by:
I was checking out the 101 C# Samples, specifically Windows Forms - Use the Clipboard. I took a look at the code for a while, and I understand what the program is doing with the cut, copy, pasting via the file menu events using the clipboard. However, what I don't get is how is the program able to produce a context menu in the textbox area. When right clicking, the context pops up and allows to cut/copy/paste/delete, etc.. But the thing is, I...
1
1120
by: Kuups | last post by:
Hi! I have a question is there any way of disabling the Copy, Paste, Delete ? of a Windows Control Tex Box Right Click Property during runtime ? Thanks!
7
11650
by: lgbjr | last post by:
Hello All, I¡¯m using a context menu associated with some pictureboxes to provide copy/paste functionality. Copying the image to the clipboard was easy. But pasting an image from the clipboard is proving to be more difficult. These pictureboxes are bound to an AccessDB. If the user wants to add an image, they select an image using an OpenFileDialog: Dim result As DialogResult = Pic_Sel.ShowDialog() If (result = DialogResult.OK) Then
0
1535
by: Brad | last post by:
Thanks for taking the time to read my question. I have a split DB and I want to be able to delete the old table, copy an existing table that is in the back end, rename it, and then make sure all my links are still in tact. Right now I seem to be just doing this to my table links in my front end. Is this possible? Here is my code:
5
13999
by: Kaur | last post by:
Hi, I have been successful copying a vba code from one of your posts on how to copy and paste a record by declaring the desired fields that needs to be copied in form's declaration and creating two button "copy" and "paste". Works like magic. My problem is how can I copy multiple records and paste them at the same time. My data entry form has main form that has a Questionlist box of Questions. The second list box on the same form displays...
9
5666
by: jbrumbau | last post by:
Hi all, I'm having difficulty catching error 8511, which states: "Records that Microsoft Office Access was unable to paste have been inserted into a new table called 'Paste Errors' In the Database window, open the new table to see the unpasted records. After you fix the problems that resulted in the paste errors, copy and paste the records from the new table." What generally happens is people copy data from Excel and paste it over data in...
0
1251
by: Ruth Barn | last post by:
When I use a standard append query; 50 of the 10 'pass', 40 do not append due to validation rules. But the 10 are still not actually added to the TASK table after the append is run and they do not already exist in the table. If I copy and paste the records from the datasheet view into the TASKS table they all paste without any errors. The pasted records and the append data sheet view of the records are the same. MS Access 2007. I've removed...
0
9843
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10881
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10628
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10275
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9406
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7807
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4475
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3126
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.