Results 1 to 14 of 14
  1. #1
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19

    Can't Edit Data in View

    Hi All,



    I can only edit data that is already inputted when I try to edit a field that is 'NULL' i get an error "Another user has modified the contents of this view; the database row you are modifying no longer exists in the the database". Please help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,990
    is this an access tbl/query, or SQL 'view'?

    if access
    is the 'vew' a table or form?
    is the database split? does every user have their own copy of the frontend?

  3. #3
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    it is a view on sql and on access it is a form but the form is created from the view from the database

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,990
    is the view set to allow edits? some don't.
    do you have rights to edit it?
    whats the lock rules on it?

  5. #5
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    I think i do have rights to edit it as I can edit data that is already inserted like stuff that I added using an insert statement but want to edit a record that has a field which is NULL.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    Do you have any boolean fields with Allow Null=true? See these links:
    https://stackoverflow.com/questions/...-linked-tables
    https://dba.stackexchange.com/questi...s-boolean-type
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    No all fields are set to varchar data type

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    If you look at the view in design mode in Access does it show a primary key (did you set the unique index to allow it to be editable when you linked it)? Also, I know the newer versions of SQL Server /ODBC drivers (by the way what versions do you use) no longer need it but can you try to add a timestamp field to the SQL table (and view).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    You cant have a primary key in a view - i am trying to edit data pulled from many tables, I am using sql server 2000 it's very old.

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    Notice I didn't say to set a primary key to the view, but merely to look in design view of the linked view and see if there is one. That would be a "pseudo" primary key created by the Access wizard in the process of linking the ODBC table/view - you get prompted to select a field/group of fields to uniquely identify a record and gets saved wit the same primary key symbol in the link.

    Can you edit that same field in the view using SSMS? If yes than the problem might be with the SQL Server driver, try to update to the latest one compatible with SQL Server 2000.

    Otherwise you can run a pass-through query to update the field in the AfterUpdate event of the control.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    How do i run pass-through query to update the field in the afterupdate event of the control?

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    Here is what i use to run pass-through queries from VBA.
    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Sub vcPassThrough(sPassThroughSQL As String, sUseConnectionStringFrom As String)
    Dim db As DAO.Database
    Dim qdExtData As QueryDef
    Dim strSQL As String
    
    
    On Error Resume Next
    
    
    Set db = CurrentDb
    If ObjectExists("QUERY", "sqlPassThrough") Then DoCmd.DeleteObject acQuery, "sqlPassThrough"
    strSQL = sPassThroughSQL 
    Set qdExtData = db.CreateQueryDef("sqlPassThrough") 
    
    
    qdExtData.Connect = CurrentDb.TableDefs(sUseConnectionStringFrom).Connect
    qdExtData.ReturnsRecords = False
    qdExtData.SQL = strSQL
    
    
    CurrentDb.QueryDefs("sqlPassThrough").Execute
    
    
    
    
    qdExtData.Close
    db.Close
    Set db = Nothing
    
    
    End Sub
    
    
    ‘this function might already be present
    Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
    ' Pass the Object type: Table, Query, Form, Report, Macro, or Module
    ' Pass the Object Name
         
         Dim db As Database
         Dim tbl As TableDef
         Dim qry As QueryDef
         Dim i As Integer
         
         Set db = CurrentDb()
         ObjectExists = False
         
         If strObjectType = "Table" Then
              For Each tbl In db.TableDefs
                   If tbl.Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next tbl
         ElseIf strObjectType = "Query" Then
              For Each qry In db.QueryDefs
                   If qry.Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next qry
         ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
              For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
                   If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next i
         ElseIf strObjectType = "Macro" Then
              For i = 0 To db.Containers("Scripts").Documents.Count - 1
                   If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                        ObjectExists = True
                        Exit Function
                   End If
              Next i
         Else
              MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
         End If
         
    End Function
    To use it you would call the sub like this: Call vcPassThrough("UPDATE SQL_TABLE SET.....","yourLinkedSQLView") (the function uses the connect string of the linked view for the newly created pass-through query).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  13. #13
    Syla is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    19
    do i need the whole code which you posted for this to work?

  14. #14
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    1,138
    Yes, the first sub is the one executing the pass-through and it is calling the second function to check if the query already exists. And you need to build the UPDATE SQL statement using the SQL Server syntax and making sure you pass it the actual values that SQL server recognizes. Have you tried to edit the same field in SQL Server (SSMS)? Sometimes you need to add the primary keys of all the tables involved in the view.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 11-06-2017, 09:56 AM
  2. Unable to edit report in design view
    By gdenham in forum Reports
    Replies: 1
    Last Post: 07-03-2014, 01:05 AM
  3. Replies: 2
    Last Post: 01-28-2014, 10:13 PM
  4. Autofill On edit Datasheet View
    By satishchandrat in forum SQL Server
    Replies: 0
    Last Post: 03-27-2012, 08:43 PM
  5. Allow some users to edit, and other to only view?
    By Tommy1005 in forum Security
    Replies: 2
    Last Post: 11-09-2009, 09:33 AM

Posting Permissions

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