Update to error 3146 ODBC--call failed. SQL Server Linked Tables

[Rx_]

Does anyone have additonal reasons for Error 3146 besides Required Field?

Suspected the problem was a Required Field on the SQL Server Linked Table. Sure enough it was. There seem to be other alternatives listed.

MSACCESS 2010 MSAccess Error 3146 using linked tables to SQL Server 2008 R2.
Didn't find any post that directly mention the Required Field. Perhaps this post will help others. The code below has one required field commented out. When the comment is removed, the code worked perfectlly and appends the new record.

This is an old thread as an example:
RE: http://www.access-programmers.co.uk/forums/showthread.php?t=155472
Another from the Microsoft Community that actually solved the problem in a different way:
http://answers.microsoft.com/en-us/...-only-on/27172fb8-958f-4222-be8e-9083541c8891

In addition, some of the Microsoft site solutions don't exactly point out the Required Field at all.
Code for an Append new record - works if uncommenting the ID_Area, a Required Field on the linked SQL Server Table.

Public Function AppendRecordNavDataToRegulatory() As String ' change to private after testing
Dim strSQLWells As String ' create Wells recordset
Dim rst As DAO.Recordset  '
On Error GoTo Err_WellWellAnotherFineMessYouGotUsIn
strSQLWells = " SELECT Wells.ID_Wells, Wells.Well_Name, Wells.WName, Wells.WNumber, Wells.WSection, Wells.WDesc, " & _
" Wells.ID_WellsStatus1, Wells.ID_Area, Wells.ID_County, Wells.ID_Prodg_Fmn, Wells.WellTypeID, Wells.ClassificationID, " & _
" Wells.ID_State, Wells.DtNavigatorHeadersCreated, Wells.API_No, Wells.Permit_File_No, Wells.UIC_No, Wells.FacilityNo " & _
" FROM Wells  "
   '
        Set rst = CurrentDb.OpenRecordset(strSQLWells, 2, dbAppendOnly + dbSeeChanges)
        rst.AddNew
            'rst![ID_Wells] = AUTONUMBER assigned by Primary Key REFERENCE ONLY
            rst![Well_Name] = "Testing"
            rst![ID_WellsStatus1] = 21
            'rst![ID_Area] = 7       ' A Required Field - Error 3146 if this is commented out
            rst![ID_County] = 3
            rst![WellTypeID] = 2
            rst![ClassificationID] = 1
        rst.Update
        rst.Close
      Debug.Print "function  AppendRecordNavDataToRegulatory"
      Set rst = Nothing
Exit Function
Err_WellWellAnotherFineMessYouGotUsIn:
    Debug.Print "function  AppendRecordNavDataToRegulatory   " & Err.Description & "  " & Err.Number
    rst.Close
    Set rst = Nothing
End Function

 

[Minty]

I've just spent about 2 hours solving a similar issue. Mine was caused by error in the underlying data causing a key violation - but the error returned was the dreaded 3146 ODBC failed. No help at all.

In the end I basically recreated the rs update in a new query and tried to run it to get to the real error.

 

[mjdemaris]

Happened to find this thread today, and happened to have the book that Gizmo mentioned...a quick read on how to look into the Errors object looks like what I need to do to solve the problem here:https://access-programmers.co.uk/forums/showthread.php?p=1627693