ODBC -- Insert on a linked table 'Name Of Table' failed. (1 Viewer)

[accessNator]

I have a linked table that comes from SQL Server. This table has a PK defined, Identity is True and Identity Increment is 1

It checks to see if a record exists, if it doesnt it suppose to insert a new record into my table.

The 4 columns in my table in SQL Server are
CTCC_RefIID (Primary Key, integer, not null)
FK_KCN_REFID (FK, integer, not null)
FK_TOC_REFID (FK, integer, not null)
LastModifiedDateTime (datetime,null)

There are no Referential Integrity set too.

When it attempts to INSERT, it gets hung up and eventually I get the error message:
ODBC--Insert on a linked table 'CompanyToCarrierCode' failed.

My code looks like this:

Private Sub Update_RemoteDatabase()

Dim sqlstr1 as String
Dim Table1 as String
Dim db As DAO.Database
Dim rstCarrierCode As DAO.Recordset
Dim getCompanyRefID As Long

getCompanyRefID = Forms!frmWorkSheetInput_Contributer!cboQuickSearch.Column(0)

Set db = CurrentDb 'Current Database

'============= Update OK_CompanyToCarrierCode Table
' Set Company Carrier Code

Table1 = "dbo_OK_CompanyToCarrierCode"
sqlstr1 = "SELECT * FROM " & Table1 & " WHERE FK_KCN_RefID =" & getCompanyRefID

Set rstCarrierCode = db.OpenRecordset(sqlstr1, dbOpenDynaset, dbSeeChanges)

    With rstCarrierCode
    
    If Not rstCarrierCode.EOF Then
        'Begin Edit
        .Edit
        'Changes
        !FK_TOC_RefId = Me.cmboCompanyCarrierType
        !LastModifiedDateTime = Now()
        .Update
    Else
        .AddNew
        'New
        !FK_KCN_RefId = getCompanyRefID
        !FK_TOC_RefId = Me.cmboCompanyCarrierType
        !LastModifiedDateTime = Now()
        .Update
        
    End If
        
End With
rstCarrierCode.Close
Set rstCarrierCode = Nothing

'============= END Update OK_CompanyToCarrierCode Table
Set db = Nothing

End Sub

 

[GinaWhipp]

The first thing I would check is if you can add or edit records via Access. If you could then put in some Break Lines and see where it stops.

 

[accessNator]

The first thing I would check is if you can add or edit records via Access. If you could then put in some Break Lines and see where it stops.

In my code, I can definitely Edit a record, but I cant Add one. When I put a break in the code and step through it, when it gets to the .update syntax, the process hangs a bit then I see the error message as stated.

I can also manually ADD or EDIT a record via SSMS interface.

 

[Galaxiom]

You would need to include the Primary Key in the recordset and populate it for a NewRecord.

 

[accessNator]

You would need to include the Primary Key in the recordset and populate it for a NewRecord.

How so? my CTCC_RefIID column is already set in SQL server as a Primary Key with Identity Seed and Increment as 1. Wouldnt any record inserted not need this defined in my code? It automatically populates the next highest number?

 

[GinaWhipp]

Don't try to EDIT via SSMS, do so via Access. Another thought are there any BIT fields in the table? And just fo good measure refresh the link and see what that does.

 

[accessNator]

I figured it out. I did have some columns configured improperly in my SQL Server table.

In my original post, I had FK_KCN_RefID and FK_TOC_RefID had actually assigned Foreign Key attributes and some type of constraint was preventing my code from inserting new records. I removed the Foreign Key attribute from those columns and my code works.

Thanks to all that replied to my OP.

 

[accessNator]

Don't try to EDIT via SSMS, do so via Access. Another thought are there any BIT fields in the table? And just fo good measure refresh the link and see what that does.

Hello Gina,
I dont have any BIT fields in the particular table in question but did run into that issue a couple of days ago. As you posted your reply, I figured out the problem on a post previous to this.

 

[GinaWhipp]

Thanks for posting back what worked bound to help somebody else!

Glad you figured it out...