Im experiencing very weird behavior from Access.
Im populating Access table using a stored procedure from SQL Server.
Here is the code that does the populating.
The problem area is the rst!id (highlighted in red). This is the Identity Column from SQL Server. When Access gets to 32768 it results in a blank in the Access Table. Ive Included the picture.
When I step through the loop and get to that 'id' I can see that the next value is there, by hovering over 'rst!id'. So I know that the value is not NULL but it does not record it in the table.
This continues for the remainder of the load, which is few more thousand rows.
Does anyone know how to fix this issue?
Thank you.
The results
Im populating Access table using a stored procedure from SQL Server.
Here is the code that does the populating.
The problem area is the rst!id (highlighted in red). This is the Identity Column from SQL Server. When Access gets to 32768 it results in a blank in the Access Table. Ive Included the picture.
When I step through the loop and get to that 'id' I can see that the next value is there, by hovering over 'rst!id'. So I know that the value is not NULL but it does not record it in the table.
This continues for the remainder of the load, which is few more thousand rows.
Does anyone know how to fix this issue?
Thank you.
Code:
Do Until rst.EOF
strSQL = "INSERT INTO tblStationPatronageEstimate (pax,transactions,time_band,day_type,entrance, " & _
" from_date,to_date,id,station_entrance_id,number_of_days,average_pax_per_day,average_tran_per_day,vr_used_name,vr_used,userid, " & _
" time_stamp, completed,comment) " & _
" VALUES( " & rst!pax & "," & rst!Transactions & ",'" & rst!time_band & "', '" & rst!day_type & "', " & _
" '" & rst!entrance & "', '" & rst!from_date & "','" & rst!to_date & "', " & _
[COLOR="Red"]" " & rst!id & ",[/COLOR] " & rst!station_entrance_id & ", " & rst!number_of_days & ", " & rst!average_pax_per_day & ", " & _
" " & rst!average_tran_per_day & ", '" & rst!vr_used_name & "'," & rst!vr_used & ", " & _
" '" & rst!userId & "', '" & rst!time_stamp & "', '" & rst!completed & "','" & rst!comment & "' ) "
CurrentDb.Execute strSQL
rst.MoveNext
Loop
The results