0

SETUP:

I am using a SQL Server database linked to a MS Access file so I can use Access forms.

I am using unbound forms with custom queries since I am dealing with several tables (If there is a more efficient way I am all for it).

In SQL Server I have setup a database role with permissions for the table, I have double checked that I have allowed the role to update the table.

ISSUE:

Whenever I use my update query in Access using a QueryDef (shown below) it runs successfully, but does not actually update the table.

DETAILS:

I was having an issue with my insert query as well, however that was only because I had not updated the query with new columns I had added in the database. I also made sure that I did the same for the update query.

Additionally I know that the update query works since I am able to update the entry directly from SSMS.

Since this seemed similar to another access/sql-server question I had, Found Here. I made sure to try the solution for that one, refreshing the table link. However, it made no difference.

CODE:

Query:

UPDATE con_people 
SET people_first_name = @firstName,
    people_last_name = @lastName,
    people_title = @title,
    people_group = @group,
    people_email = @email,
    people_shift = @shift,
    people_hiredate = @hireDate,
    people_location = @location,
    people_reportsTo = @reportsTo,
    people_versionCount = people_versionCount + 1,
    people_datelastupdated = @dateUpdated,
    people_isActive = @isActive
WHERE people_employeeID = @empID;

QueryDef:

Public Function UpdatePeople(firstName As String, _
                                lastName As String, _
                                title As Integer, _
                                group As Integer, _
                                Email As Variant, _
                                isActive As Boolean, _
                                Shift As Integer, _
                                Location As Integer, _
                                HireDate As Variant, _
                                ReportsTo As Variant, _
                                employeeID As Integer)

    OtherFunctions.Initialize

    Dim QDF As DAO.QueryDef

    If FindQuery("UpdatePeople") = True Then OtherFunctions.dbs.QueryDefs.Delete "UpdatePeople"

    Set QDF = OtherFunctions.dbs.CreateQueryDef("UpdatePeople", SQLUpdatePeople)

    QDF.Parameters("@firstName").Value = firstName
    QDF.Parameters("@lastName").Value = lastName
    QDF.Parameters("@title").Value = title
    QDF.Parameters("@group").Value = group
    QDF.Parameters("@email").Value = Email
    QDF.Parameters("@isActive").Value = isActive
    QDF.Parameters("@empID").Value = employeeID
    QDF.Parameters("@shift").Value = Shift
    QDF.Parameters("@hireDate").Value = HireDate
    QDF.Parameters("@location").Value = Location
    QDF.Parameters("@reportsTo").Value = ReportsTo
    QDF.Parameters("@dateUpdated").Value = ConvertTimeUnix.ConvertDateToUnix(Now())
    QDF.Execute

    If FindQuery("UpdatePeople") = True Then OtherFunctions.dbs.QueryDefs.Delete "UpdatePeople"

End Function

Any help is appreciated,

Thanks.

Erik A
  • 31,639
  • 12
  • 42
  • 67
EliSauder
  • 138
  • 1
  • 10
  • Do get more information if you include `dbFailOnError` with `QDF.Execute`? – HansUp Apr 29 '19 at 20:51
  • @HansUp I get 'Runtime Error 3146 - ODBC--call failed' Which seems to point to a network issue. However I do not know why that would be an issue since inserting info works. – EliSauder Apr 29 '19 at 21:10
  • Is `con_people` the Access link to a SQL Server table? If so, can you open that linked table directly from the Navigation pane in Datasheet View and update values in an existing record? If not, does Access give you any more details when the attempt fails? – HansUp Apr 29 '19 at 21:16
  • @HansUp Yes `con_people` is the linked table. I am able to open it directly in access and update the values. On the fail, those details `Runtime Error '3146': ODBC--call failed.` is all I can see. – EliSauder Apr 29 '19 at 21:19
  • Add a timestamp datatype field to the SQL Server table. Refresh the link in Access. Try your `UPDATE` again. What happens? – HansUp Apr 29 '19 at 21:22
  • @HansUp I am not familiar with that. Is it just as simple as `ALTER TABLE con_people ADD people_timestamp TIMESTAMP;`? – EliSauder Apr 29 '19 at 21:25
  • @HansUp Ok I think I got it working. Apparently all you need to do is add the timestamp data type (If it works). I did test an update from the SSMS and the value changed so I think it does work. It does not change when I update from my update function. But it does when I update directly from access. – EliSauder Apr 29 '19 at 21:40
  • Check this to get the actual error message: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/error-object-dao -- or more concise here: https://stackoverflow.com/questions/730414/determine-real-cause-of-odbc-failure-error-3146-with-ms-access – Andre Apr 29 '19 at 22:05
  • That helps a lot, thank you @Andre. Turns out I was miss, setting one of the columns as a BOOLEAN (SQL BIT) in my code when it should have been a foreign key INT. – EliSauder Apr 29 '19 at 22:25

1 Answers1

1

Thanks to a comment by @Andre I was able to find the source of the issue.

I was using the wrong data type when I was updating the entry. The SQL-Server was expecting an INT (for a foreign key) when I was providing it a boolean (SQL-Server BIT).

Details About the solution:

Links provided by Andre: Error Object - Data Access Object and Determine real cause of ODBC failure (error 3146) with ms-access?. For more information on the DAO.Error object please reference those.

Here is an example of how it is used:

    Dim myerror As DAO.Error
    For Each myerror In DBEngine.Errors
        With myerror
            If .Number <> 3146 Then 'prevents the system from returning the basic error.
                MsgBox "Error #:" & .Number & ", Description: " & .Description & ", Source: " & .Source
            End If
        End With
    Next

Once I ran it, this was returned, allowing me to find the root cause:

Error #:547,

Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_people_isActive". The conflict occurred in database "continuousimprovement", table "dbo.con_isactive", column 'isActive_id'.,

Source: ODBC.QueryDef

Again, Thank you Andre.

EliSauder
  • 138
  • 1
  • 10