Solved Runtime 3155 with SQL database

[craigachan]

I have a front end Access 365 and backend sql server database. I get a runtime 3155 when trying to insert a record into the sql table. I haven't found any suitable solution yet. It errors out at rsR.Update. Any help is appreciated.

Private Sub cmdAddSelect_Click()
On Error GoTo cmdAddSelectErr
    Dim db As DAO.Database, rsR As DAO.Recordset, strRID As String, strRName As String, strCity, stroForm As String, msql As String

    '======Validate info
            If IsNull(Me.RLast) Then
                MsgBox "Enter Contributor Lastname"
                Exit Sub
            End If
            If IsNull(Me.RFirst) Then
                MsgBox "Enter Contributor Firstname"
                Exit Sub
            End If
            If IsNull(Me.RAddr) Then
                MsgBox "Enter Contributor Street"
                Exit Sub
            End If
            If IsNull(Me.RCity) Then
                MsgBox "Enter Contributor City"
                Exit Sub
            End If
            If IsNull(Me.RState) Then
                MsgBox "Enter Contributor State"
                Exit Sub
            End If
            If IsNull(Me.RZip) Then
                MsgBox "Enter Contributor Zip Code"
                Exit Sub
            End If
            If IsNull(Me.RTel) Then
                MsgBox "Enter Contributor Telephone"
                Exit Sub
            End If
            If IsNull(Me.Degree) Then
                MsgBox "Enter Contributor Degree(s)"
                Exit Sub
            End If
            If IsNull(Me.REmail) Then
                MsgBox "Enter Contributor Email"
                Exit Sub
            End If
            If IsNull(Me.cboSpecialty) Then
                MsgBox "Enter Contributor Specialty"
                Exit Sub
            End If
  
    '=======Add to referral table

    DoEvents
    DoEvents
  
    strRName = Me.RLast & ", " & Me.RFirst
    strCity = Me.RCity

    Set db = OpenDatabase("", False, False, CurrentDb.TableDefs("referral").Connect)
    Set rsR = Db.OpenRecordset("Referral")

        With rsR
            .AddNew
                !typ = "9"
                !Last = Me.RLast
                !First = Me.RFirst
                strRName = Me.RLast & ", " & Me.RFirst
                !Degree = Me.Degree
                !email = Me.REmail
                !Street = Me.RAddr
                !City = Me.RCity
                strCity = Me.RCity
                !State = Me.RState
                !Zip = Me.RZip
                !Work = Me.RTel
                !Npi = Me.RNPI
            .Update
            .MoveLast
            strRID = !ID            'Get RID just made
         End With
    rsR.Close
    '=======Return referral info to oForm
    stroForm = Me.oForm
    Select Case oForm
        Case "PatientAdd"
            Forms(stroForm).RName = strRName
            Forms(stroForm).RefCity = strrcity
            Forms(stroForm).Referral = strRID
    End Select
  
cmdAddSelectExit:
    Set rsR = Nothing
    Exit Sub
  
cmdAddSelectErr:
    MsgBox "Forms!ContributorAddMain-cmdAddSelectClick: " & Err.Number & " - " & Err.Description
    Resume cmdAddSelectExit

End sub

<

 

[theDBguy]

Hi. What is error 3155?

 

[craigachan]

Sorry, Runtime 3155 - "ODBC - - Insert on linked table ???? failed " is what I'm getting

 

[theDBguy]

Sorry, Runtime 3155 - "ODBC - - Insert on linked table ???? failed " is what I'm getting

Hi. Thanks. Well, that's not a very helpful error message. My first impression would be to suspect the ODBC driver. Which version are you using?

 

[Uncle Gizmo]

I reckon this is wrong:-

stroForm = Me.oForm

Please explain...

 

[arnelgp]

why are you using this:

Set db = OpenDatabase("", False, False, CurrentDb.TableDefs("referral").Connect)

if "referral" is a Linked table you can just use:

Set db = Currentdb

also see post #5.

stroForm is not Defined?
if you are assigning the Form object to this variable
you need to use Set:

Set stroForm = Me.oForm

but since you are using it as String in the Select Case, then you need to retrieve the Form name (i guess):

stroForm = Me.oForm.Name

 

[sonic8]

"ODBC - - Insert on linked table ???? failed "

Usually the reason for that is that the data you try to insert is invalid. This might be due to key violations, constraint violations or incompatible data.
But, actually, I'm rather surprised that you even get that far.

Set db = OpenDatabase("", False, False, CurrentDb.TableDefs("referral").Connect)
Set rsR = Db.OpenRecordset("Referral")

This looks very much like ODBC-Direct, which is not supported anymore for quite some time (since Access 2007).
If you already got your table Referral linked into the Access frontend, why don't you insert into the linked local table?
You might still see the same error message, but then you can be sure it's because of problems with the data and not due to deprecated features.

 

[craigachan]

Thank you for all of your replies and questions. I only tried the ODBC direct because my regular link connections were failing and it was suggested somewhere on the internet. But in spite of it, it still failed. I could take it out and us the following line but it will still fail.

set rsR = currentdb.openrecordset("Referral") and it will still fail.

To answer the other questions:

me.oForm is just a text field that I use to keep track of the form that is opening the current form. I may have many forms that open this current form and each are requesting information to be returned in different ways. So me.oForm (or original form) is my way of keeping track. It is used later in my code as a string (stroForm) . I don't this this is my problem as my code errors out before it gets to it. It errors out when I'm trying to update the record that I'm trying to insert into the referral table.

 

[bastanu]

Can you manually add a record in the linked "Referral" table?

 

[craigachan]

Hi bastanu, Yes I can manually add a record. But here is what I found. What I thought was the primary key ('RID') in the referral table was in fact an indexed field but not an autonumber. So it was not generating a new record id at all. This created a violation. The fix, was to open the referral table in RID sorted order, move to the last record, get the last RID value and then add 1. Then use this value as the new RID in the new record. Problem Solved. Thanks for everyone's input.

 

[bastanu]

Glad to hear you got it working; be aware that your fix has the potential to create conflicts in busy multi-user environments (and I would suggest instead of all those steps just create a totals query to give you the new RID number: "Select Max(RID)+1 As NewRID From Referral;" then use a dLookup in this new totals query to get your value).

Probably a better approach if you can edit the SQL table is to set the Identity property of the RID field to True so it acts as an Access autonumber.

SQL Server identity column - Simple Talk

The SQL Server identity column is used to populate a column with incrementing numbers on insert. In this article, Greg Larsen explains how it works.

www.red-gate.com

Cheers,

 

[craigachan]

Great suggestion. I'll recode to the SelectMax(). The sql database that i'm linking to is part of another application that I can' change at this time. So I'm stuck with doing it this way. Thanks again.

 

[bastanu]

Cheers,