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

 

[Uncle Gizmo]

There's a bit about Error 3146 in Andrew Couch's book:-
"Microsoft® Access® 2010 VBA Programming Inside Out"
Chapter 5. Understanding the Data Access Object Model

You may be able to see it for free on Safari (if they are still offering a free trial period) Here:- https://www.safaribooksonline.com/library/view/microsoft-access-2010/9780735661639/ch05.html

I did Andrew's MS SQL course in Coventry UK a few years back. A very knowledgeable chap.

My understanding from his book is that the Access Err object will return only one error number, (the last error number). However in the errors reported from the MSSQL Errors Collection, there may well be several error numbers, the last one being the one reported through Microsoft Access. He explains how you can access the MSSQL errors collection. If you want to have a chat I could fire up Skype or my preferred one is Hangouts.

 

[Uncle Gizmo]

I have been clearing out my old browser links and putting them on my website here:-

MS Access Stuff

when I happened upon this website:-

J Street Technology offers these downloads as is

Extract:-
J Street Access Relinker 2 (BETA)
We took what we humbly think is the best Access table relinking code and made it even better. This beta version has all the features of the original J Street Access Relinker, plus it now supports 32-bit or 64-bit Access AND encrypted (password-protected) back-end Access databases! Give it a try and let us know if you run into any issues. Tested for Access 2010, but should also work in earlier versions. There’s a README table with detailed instructions.

They offer a free table relinking facility. If you happen to use it, I'd be interested if you could post back what you think of it. I don't know anything about it, I recalled you asking about it in this thread, that's why I'm posting it.

 

[Uncle Gizmo]

Oops ... Not this thread!

 

[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