Run-Time Error 3157 - Link SQL Table ODBC- Failed

[TheNovice]

Good Morning,

I have an Access Database that is linked to our SQL Server. In my MSA I
have a Form with Code (enclosed) that opens the table, goes to each row and
assigns a stop number and calculates the mileage. where I am hitting a wall
is when I Open the form it runs Great if the table is a Access Table, but as
soon as I change it to the SQL Table I get this error.... I have tried using
Views and The table itself. Can Some one PLEASE help me...

Public Sub main()

Dim LID As String
Dim strStop As Integer
Dim strLstStop As Integer
Dim strLstCall As String
Dim dblLstLID As Double
Dim dblLstMiles As Double
Dim dtDelDay As Date
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("dbo_tmpPR_Stop_Det")

With rs
strStop = 0


Do Until .EOF

' Check for change of Key

If LIN <> !ACTV_EXTRAC_ACTV_LID Or dtdeldate <> !DelDay Then
strStop = 1
'If !pr_ms = "M" Then strStop = 1
strLstStop = 0
strLstCall = vbNullString
dblLstMiles = 0
dblLstLID = !ACTV_LID
LIN = !ACTV_EXTRAC_ACTV_LID
dtdeldate = !DelDay

End If

' Add stop numbers

.Edit

If !StopNum = 0 Then
'If !pr_ms = "S" Then strStop = strStop + 1
'If !pr_ms = "M" And strLstCall = "S" Then strStop = strStop + 1
If !ACTV_LID <> dblLstLID Then
strStop = strStop + 1
!MilesTo = !ACTV_ODO_VALUE - dblLstMiles
dblLstMiles = !ACTV_ODO_VALUE
Else
!StopNum = strStop
!StemMiles = (!POSTMILES - !PREMILES)
!MilesTo = !ACTV_ODO_VALUE - !PREMILES
End If
End If
strLstCall = !PR_MS
dblLstLID = !ACTV_LID
.Update


.MoveNext

Loop

End With

rs.Close
db.Close

End Sub

Private Sub Form_Open(Cancel As Integer)
main
End Sub

 

[TheNovice]

Just to add to the question, I am updating a field and it seems to stop
exactly on 6588th row, with the 3157 error.

Plus I cleared up the Code and here it is.

Public Sub main()

Dim LID As String
Dim strStop As Integer
Dim dblLstLID As Double
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("dbo_vwPR_Stop_Det")

With rs
strStop = 0


Do Until .EOF

' Check for change of Key

If LIN <> !ACTV_EXTRAC_ACTV_LID Then
strStop = 1
dblLstLID = !ACTV_LID
LIN = !ACTV_EXTRAC_ACTV_LID

End If

' Add stop numbers

.Edit

If !StopNum = 0 Then
If !ACTV_LID <> dblLstLID Then
strStop = strStop + 1
End If
!StopNum = strStop
End If
dblLstLID = !ACTV_LID
.Update


.MoveNext

Loop

End With

rs.Close
db.Close

End Sub

Private Sub Form_Open(Cancel As Integer)
main
End Sub

 

[Klatuu]

On which line of code does the error occur?

 

[TheNovice]

Klatuu,

Thanks for the response. When the error appears the ".UPDATE" line is
highlighted and if I stop it and Re-Run it it works fine.

 

[Klatuu]

I don't see anything really obvious, but I do have a couple of points you
should look at and verify. It looks as if you may be putting a Null value in
some of your fields and if that field doesn't allow Nulls, you can get that
sort of response.
First, I see you are using a variable named LIN, but I don't see it Dimmed
or initialized on the first time through your code.

I would verify that all the variable in this snippit are correctly
initialized:

If LIN <> !ACTV_EXTRAC_ACTV_LID Or dtdeldate <> !DelDay Then
strStop = 1
'If !pr_ms = "M" Then strStop = 1
strLstStop = 0
strLstCall = vbNullString
dblLstMiles = 0
dblLstLID = !ACTV_LID
LIN = !ACTV_EXTRAC_ACTV_LID
dtdeldate = !DelDay

End If