Error 3155 on linked table

[opopanax666]

Hey guys & gals,

Been running an Access front-end on 3 computers with linked tables to a SQL-Server instance for about 4 years now (remember this!).

Once in a while, I would get a 3155 error for a couple of hours, and then it would magically go away (e.g. 09/11/14 from 12:38 to 15:33, 04/17/14 from 11:14 to 12:10).

But this time it started on tuesday (01/19/16) and is still going on.

I checked some threads on this problem, and did a couple of checks:
- There are no "memo" fields in the offending table
- there are no "text" fields in the offending table
- the offending table has an "id" field
- code doesn't use docmd.runsql
- my recordsets are opened AND closed
- db is CurrentDB, so doesn't need to be closed
- both are set to "nothing"

code of the offending routine (error happens when it's trying to update table "tblGEPRODUCEERD2" in the final part):

Private Sub btnPrint_Click()

Dim lngLaatstGesavedNr As Long
Dim lngHoogsteGeprod_ID As Long
Dim lngHuidigNr As Long

Dim strACODE As String
Dim strTABEL As String
Dim strRAPPORT As String
Dim dteDatum As Date

Dim flgAEEA As Integer

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

    If ((intPRODUCT = 7) Or (intPRODUCT = 8) Or (intPRODUCT = 10) Or (intPRODUCT = 127) Or (intPRODUCT = 128) Or (intPRODUCT = 129) Or (intPRODUCT = 130) Or (intPRODUCT = 131)) Then
        If Me.txtAcode & "" = "" Then
            MsgBox "Code toestel invoeren!"
            Me.txtAcode.SetFocus
            Exit Sub
        ElseIf Len(Me.txtAcode) > 9 Then
            MsgBox "Code te lang!"
            Me.txtAcode.SetFocus
            Exit Sub
        ElseIf Len(Me.txtAcode) < 9 Then
            MsgBox "Code te kort!"
            Me.txtAcode.SetFocus
            Exit Sub
        End If
        flgAEEA = 1
    End If

Insert_Geprod_ID:

'***********************************
'MsgBox "*** tblVolgendNr lezen ***"

    If flgTEST = 1 Then
        strTABEL = "tblVolgendNr"
    Else
        strTABEL = "dbo_tblVolgendNr"
    End If

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & " WHERE id = 1", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblVolgendNr (sic) ***

    rst.LockEdits = True
    rst.MoveFirst
    rst.Edit
    lngHuidigNr = rst!volgendnr
    rst!volgendnr = lngHuidigNr + 1

    rst.Update
    rst.Close

    Set rst = Nothing
    Set dbs = Nothing

'*****************************
'MsgBox "*** Wegschrijven ***"

    If flgTEST = 1 Then
        strTABEL = "tblGEPRODUCEERD2"
    Else
        strTABEL = "dbo_tblGEPRODUCEERD2"
    End If

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & "", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***
    
    dteDatum = Now()
    
        rst.AddNew
        rst!Geprod_ID = lngHuidigNr
        rst!Barcode = Format(lngHuidigNr, "000-000")
        If flgAEEA = 1 Then
            rst!Electro_code = Me.txtAcode.Value
        Else
            rst!Electro_code = Format(lngHuidigNr, "000-000")
        End If
        rst!Weging_datum = dteDatum
        rst!Product_id = intPRODUCT
        rst!Bestemming_id = intBESTEMMING
        rst!Gewicht_bruto = sngTMP_BRUTO
        rst!Gewicht_tarra = sngTMP_TRANS + sngTMP_ENKEL + sngTMP_PALLET + sngTMP_DOZEN
        rst!Gewicht_netto = sngTMP_BRUTO - (sngTMP_TRANS + sngTMP_ENKEL + sngTMP_PALLET + sngTMP_DOZEN)
        rst!Geprod_status_id = 1
        
        rst.Update
        rst.Close
        
    Set rst = Nothing
    Set dbs = Nothing

'******************************
    'MsgBox "*** bon afdrukken ***"
    
    If flgTEST = 1 Then
        strTABEL = "tblGEPRODUCEERD2"
        strRAPPORT = "rptGeproduceerd_test"
    
        DoCmd.OpenReport strRAPPORT, , , "Weging_datum = #" & dteDatum & "#"

        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & " WHERE Weging_datum = #" & Format(dteDatum, "MM/DD/YYYY HH:MM:SS") & "#", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***

            rst.MoveFirst
            rst.Edit
            rst!Print_x = 1

            rst.Update
            rst.Close

        Set rst = Nothing
        Set dbs = Nothing
    
    Else
        strTABEL = "dbo_tblGEPRODUCEERD2"
        strRAPPORT = "rptGeproduceerd"
    
        DoCmd.OpenReport strRAPPORT, , , "Weging_datum = #" & Format(dteDatum, "YYYY-MM-DD HH:MM:SS") & "#"

        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("SELECT * FROM " & strTABEL & " WHERE Weging_datum = #" & Format(dteDatum, "YYYY-MM-DD HH:MM:SS") & "#", dbOpenDynaset, dbSeeChanges) '*** (dbo_)tblGEPRODUCEERD2 ***

            rst.MoveFirst
            rst.Edit
            rst!Print_x = 1

            rst.Update
            rst.Close

        Set rst = Nothing
        Set dbs = Nothing
    
    End If

Call Form_Load

End Sub

I know, it doesn't look like much, it kinda grew "organically", but it works! Well, it used to until last Tuesday.

Is there anyone who has any idea what could be the trouble, because it's really disrupting our work for the moment?

P.s. any variables not declared in the sub itself, are global variables (remember it has been working for 4 years)...

TIA,
James

 

[Guus2005]

I can only speculate why your code ran without a glitch for four years. I don't know.

Error 3155 is a quite general error. It says "there is a problem with an attached table, don't know what..."

Like you i have browsed all possible solutions to this problem and it seems to me that you covered all the basics.

However looking at your code i believe there are some improvements to be made.

You are using recordsets to update and insert records.
With even less code you can create a faster solution.
You can :
1- use parameterized queries or
2- create dynamic pass-through queries

Both are faster and use less resources.

HTH

 

[JHB]

Only for error finding, (do it on at copy/backup database):
The first thing I would do, would be adding some values manually.
Next I would comment out all the code line where you add different values to the table. So it would be, add a new record and just after that update the table. Maybe the system complain that you're not inserting any new value, then only add one value.
If that goes okay, then uncomment the next code line and so on, until you find the problem line.
If that NOT goes okay, I would look at the table in question, maybe create a new one with the same structure, and then try adding values.
I do not know if it makes any difference but I can see you're locking the record/page ( rst.LockEdits = True ) but you can't see you're unlocking it.

 

[opopanax666]

However looking at your code i believe there are some improvements to be made.

You are using recordsets to update and insert records.
With even less code you can create a faster solution.
You can :
1- use parameterized queries or
2- create dynamic pass-through queries

Both are faster and use less resources.

HTH

I was planning on rewriting the whole thing this year, so I'll definitely have a look at your advice. Cheers!

Only for error finding, (do it on at copy/backup database):
The first thing I would do, would be adding some values manually.
Next I would comment out all the code line where you add different values to the table. So it would be, add a new record and just after that update the table. Maybe the system complain that you're not inserting any new value, then only add one value.
If that goes okay, then uncomment the next code line and so on, until you find the problem line.
If that NOT goes okay, I would look at the table in question, maybe create a new one with the same structure, and then try adding values.
I do not know if it makes any difference but I can see you're locking the record/page ( rst.LockEdits = True ) but you can't see you're unlocking it.

Problem is, that when running alone it works perfectly.
What I am going to do, following your idea, is to create a new table, and hope this stops the problem from occurring.
The lock (rst.LockEdits = True) is on an other table, but I'm going to comment it out and see whether this changes anything.

Thanks everybody for the replies, I'll be back with an update...