none
Access 2013 - copy then paste causes a delete record RRS feed

  • Question

  • Using Access 2013 linked via ODBC to Oracle 11 database.

    Open a table in datasheet view.

    Select record to copy

    Right click and copy record

    Insert record

    Save record then results in a deleted recorded display

    Refresh then displays properly inserted record

    Why is deleted recorded being displayed?

    Monday, October 24, 2016 4:56 PM

All replies

  • Hi SA Developer,

    so overall , it looks like you are copying the row from one table and paste that to another table in the same Access Database manually.

    generally when you copy and paste the record then it directly displays the record pasted in the destination table. it does not show any message for record deletion.

    I want to confirm with you is there any macro there in your database that runs when you perform some operation on table?

    if not, then try to Compact and Repair Database.

    after repairing the database please try to copy paste a record and test whether the issue still exist or not.

    if your issue not solve using above mentioned suggestion then try to post the picture of that deletion message.

    so that we can try to know about it and can suggest you further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 25, 2016 7:42 AM
    Moderator
  • Thanks for getting back to me.

    I created a separate front end database to ensure that no code is running. I linked to a single table.

    I tried to upload the image of the sequence but I get a message saying that my account needs to be verified before uploading images. Not sure what is required here.

    Tuesday, October 25, 2016 4:38 PM
  • Hi SADeveloper,

    you had mentioned that you create an separate front end db so is that solve your issue? what result did you get by doing that?

    if you don't have permission to upload picture then you can upload the picture on any free image hosting site and then paste the link here. so that we can access that image and try to look in to that.

    I want to know the approach how you link the Access table to sql with help of ODBC. by code or by manual approach.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 27, 2016 1:14 AM
    Moderator
  • This behavior is often seen when the table in question does not have a timestamp (row version) column.

    As you note, the sheet “displays” deleted, but you well note that after a refresh all the data is well and correctly saved.

    I assume you are right clicking on the “*” and choosing paste.

    The above is a “guess”. Another reason would be the table does not have a primary key defined. The cut + paste in Access is smart to know that the PK column is to not be re-pasted, since it likely changes for each row.

    And, does this occur for other linked tables, or just this one?

    So, I would add a timestamp column to the oracle table (but, you may well not be able to makes changes. If you can’t, the you may well be out of luck, or just have to be “careful” and hit refresh each time you do this. Hopefully you not “often” editing the data directly in table view, and eventually build some kind of form or user interface (you could then place a button called duplicate row or some such, and use code to copy (append) the selected row again.

    So “general” editing of data in table view tends to have issues and it often a good idea to avoid “general” editing of data directly in table view.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Thursday, October 27, 2016 2:37 AM
  • You are correct in the description of the behavior.

    It is important to note that this behavior did not exist in Access 2010 and the Oracle tables have not changed.

    The table does have a primary key in Oracle and Access correctly displays that it has a primary key for the linked table.

    The behavior exists for all linked tables that I have tested.

    The primary key in Oracle is defined as Number with size 11. Access is interpreting this as a decimal rather than the expected  long integer data type. I'm not sure if this could be causing the problem?

    I will try and test this using Access 2010 to see if the problem exists now.

    Thanks for all your help.

    Thursday, October 27, 2016 3:21 AM
  • I have tested the situation with Access 2010 and the delete record behavior does not occur. Access 2010 interprets the primary key as decimal as well. So the data type is not causing the behavior at least not in Access 2010.

     
    Friday, October 28, 2016 5:33 AM
  • Hi SADeveloper,

    please try to upload screen shots as I mentioned earlier in my post using free image hosting websites.

    so that we can look in to that actually what's going on and try to reproduce the issue on our side.

    It is possible that there is something change between these two version of Access and because of that you are facing this issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 28, 2016 8:54 AM
    Moderator
  • I tried adding a Refresh in the After Update and After Insert events but that only works if 1 record is copied. Copying multiple records results in Record Deleted message. I created an On Timer event and added the Refresh there and that corrects the screen. It has to be a Refresh. A Requery does not fix the issue.

    Here is an example of the issue. 

    Line 397382 is copied and then pasted into the next row. Saving the record causes the deleted line to appear.

    TIMECARDID PROJECTTASKCODEID WORKDESCRIPTION CREW FIELDHOURS TRAVELHOURS OFFICEHOURS NONCHARGEABLEHOURS WORKCODEID
    397382 99324 6 plots   9.5 1 0 0 2
    #Deleted #Deleted #Deleted #Deleted #Deleted #Deleted #Deleted #Deleted #Deleted
    After Refresh
    TIMECARDID PROJECTTASKCODEID WORKDESCRIPTION CREW FIELDHOURS TRAVELHOURS OFFICEHOURS NONCHARGEABLEHOURS WORKCODEID
    397382 99324 6 plots   9.5 1 0 0 2
    397386 99324 6 plots   9.5 1 0 0 2

    Thanks for your help


    • Edited by SADeveloper Saturday, October 29, 2016 5:45 PM
    Saturday, October 29, 2016 5:35 PM
  • Are you linking to oracle with the same driver? (there are several versions).

    Also, given that you speak of after update event(s), then I have to assume this is a form, but a continues one, or more specific a datasheet form. I not aware of any change - but if you talking about the same application without changes no any re-linking of tables, but the same application works fine in 2010, and then without change nor re-linking it does not work I 2013, then something is different, or it points to a problem with 2013..

    If you talking about a different computer, then I would consider checking if the oracle driver(s) are the same.  The "deleted" issue is also often seen for "bit" columns that don't have a default of 0 set in the given table - but you not that this works for one version of Access and not another - is this on the same computer with the same application, or running on a different computer? (if yes, then this point to a possible different oracle ODBC driver).

    You could also just try typing a few chars into the * (new row) and see if the display error occurs if you move the cursor off that row (to cause a save - so this error might occur even without a cut + paste).

    Regards,

    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Saturday, October 29, 2016 10:16 PM
  • Here is a recap:

    WE have a complete MS Access application consisting of forms and VB code that has been running for 17 years. Our latest upgrade to MS Access 2013 created the copy and paste issue. 

    To confirm whether this is a code problem, we created a new Access 2013 database on the same computer with no code , forms etc only the ODBC link to a single table. The copy paste issue was present when working on the table. We linked to another table and the same issue existed on that table as well. Our conclusion is that this is not a coding issue.

    We created an Access 2010 database and linked to the same tables through the same ODBC driver and the problem does not exist.

    If you directly enter values in the field without copy and paste and then save the record by moving off the record the problem does not occur. The problem only occurs when using copy and paste in Access 2013.

    In trying to find a solution we modified code in the original application. Refresh code in the On Timer event removes the delete descriptions and displays the record as it was saved. Our theory is that Access 2013 does not respond to the ODBC confirmation send after the data is saved in Oracle.

    Our theory is the ODBC driver that works for Access 2010 does not work for Access 2013. We are confirming that we are using the most recent ODBC driver for Oracle 11.

    Thanks for your help.



    Monday, October 31, 2016 5:49 PM
  • You quite much exhausted the “simple” answers and this does point to a 2013 issue. This is especially the case that you tested on the same computer and with the same oracle drivers.

    (so that’s a RATHER definitive test on your part).

    And a “timer” solution is less than ideal (and I sure you willing to grasp any straws at this point in time). So you are faced with cooking up a fix until such time this issue is dealt with (and not knowing how long this could take if at all – you are faced with cooking up something – even a kluge).

    Given this is a form (continues I think), I wonder if a me.refresh in the after update event would fix this? And if no then a me.requery certainly would be a hammer whacking to fix this.

    If the timer idea works, then fine, but I really don’t like “timer” events that fire to fix some display issue. (as noted, you have to do what you have to do!). I would at least try me.refresh, and if no go, then a me.Refresh in the after update event would be a better choice than some timer event.

    Unfortunately, there seems you have few choices here and I wish I could offer something better then above.

    Regards,
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada

    Tuesday, November 1, 2016 9:46 PM