Results 1 to 5 of 5
  1. #1
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398

    Updating an existing record

    Hello,



    I have a problem I can't figure out what is the solution.

    I created a Access 2013 DB, both a front end and back end DB. I converted the back end DB to SQL Server DB.

    On a form, the user has the ability to create a new record, or edit an existing record. When the user selects to edit an existing record, after editing the record, and then selecting to save, I have a pass through query that updates the record. My problem is the application is creating a new record. I then receive a Windows message about asking to save the data to the clipboard.

    From what I can tell, when the record is selected to update, is their a lock being placed on this record? Could that be why it's creating a new record, rather than updating the record.

    The reason I mentioned that this operated previously with an Access back end DB, is that the Front End DB was not changed when switching from Access 2013 to SQL Server.

    Thanks in advance for your response.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't see the need for a pass thru query if you are updating 1 record. (many yes)
    create a local update query that uses the keyID as criteria. It should work normally.

  3. #3
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    Sounds good, let me try that. I'll update the ticket later.

  4. #4
    crowegreg is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Posts
    398
    I've taken your recommendations, and here are my results.


    I'm testing my coding by only trying to update 1 record. If I create a query and execute the query, everything works properly. If I execute the query as code behind the page, nothing happens. Here's my code:
    Dim dbs As DAO.Database

    Set dbs = CurrentDb

    dbs.Execute "Query3"

    Set dbs = Nothing

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    Regarding the locking topic: a simple select statement will not lock your records. The select for update is used with cursors and only works when wrapped in a transaction. Mind you, when you are locking a record, in fact you're locking a page in memory which can contain more than 1 record.

    Regarding the different behaviour : when using ADO it will call the stored procedure sp_cursoropen and use a cursor, which is a different behaviour than executing the query directly from access.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updating the Existing record with new data
    By rd.prasanna in forum Import/Export Data
    Replies: 4
    Last Post: 10-02-2013, 07:04 AM
  2. Replies: 11
    Last Post: 05-23-2012, 08:42 AM
  3. Replies: 4
    Last Post: 05-30-2011, 08:20 PM
  4. Replies: 0
    Last Post: 02-25-2011, 09:40 AM
  5. Updating an Existing Table
    By jo15765 in forum Access
    Replies: 2
    Last Post: 11-21-2010, 09:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums