overflow with append to linked mysql table 1

[hovercraft]

I'm out of ideas.

If my_defgroupcount = 1 And my_defcount = 1 Then
           my_ltrid = CLng(my_ltrid + 1)
    End If
    
    If my_defgroupcount <> 1 Then
        my_ltrid = CLng(my_ltrid + ((my_defgroupcount) - 1))
    End If
    
    If my_deftroupcount = 1 And my_defcount <> 1 Then
            my_ltrid = CLng(my_ltrid)
    End If
DoCmd.RunSQL "UPDATE tmp_tbl_ltrs SET tmp_tbl_ltrs.ltr_id = '" & my_ltrid & "' WHERE (((tmp_tbl_ltrs.scsid)='" & my_scsid & "'));"
 rs.MoveNext
 
 Loop

I'm counting groups of letters but when the number (my_ltrid) reaches 32767 I get an overflow error. The ltr_id field is a mediumint in a mysql table when shows up a text field if I view it in design view within Access. Which I (surprisingly) was able to change to a long numeric and save it (in Access). As you can see I have even tried changing the variable via clng() but still get the error.

Any thoughts?

Thanks a bunch!
Hovercraft

 

[PHV]

what about this ?

DoCmd.RunSQL "UPDATE tmp_tbl_ltrs SET ltr_id=" & my_ltrid & " WHERE scsid='" & my_scsid & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hovercraft]

*THWACK*!! - that's the sound of the palm of my hand hitting my forehead.

Thank you for the use of your eyes. I'm running it now to see if that's what it was. So even though it was being presented as a string, does Access store it as a small integer?

The process takes about an hour and a half to complete.

 

[hovercraft]

Thanks PHV.

As it turns out I had declared the Global variable my_ltrid as a string, so changing that to "as long" made everything work smoothly.

Hovercraft