ACCESS FEED: [MS_AccessPros] Problem inserting records

Senin, 18 Februari 2013

[MS_AccessPros] Problem inserting records

I've been puzzling over this for the last couple of hours.
The reason is probably staring me in the face but I just can't see it.
Anybody got any advice please?

Code follows: (excuse the wrapping!)

' Create a tblFTPHistoryHeader record
'
strSqlString = "INSERT INTO tblFTPHistoryHeader (FTPHistoryQueueID" & _
") VALUES (" & _
FTPID & _
")"
CurrentDb.Execute (strSqlString)
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")
If Not rs.EOF Then
lngtblHistoryHeader = rs.Fields(0)
Else
' ERROR MsgBox "No AutoNumber generated."
End If
rs.Close
Set rs = Nothing
Note that this is working OK
'
' Now examine the log file to see if the transfer was successful
'
strLogArray = Split(LogFile, vbCrLf)
boolSuccess = False
intLineCount = 0

Do
Code snipped for brevity
'
' write the text line to the FTPDetail table
'
strLogArray(intLineCount) = Replace(strLogArray(intLineCount), strQ, strQQ)
strSqlString = "INSERT INTO tblFTPHistoryDetail (FTPHistoryHeaderID," & _
"FTPDetailLineNo," & _
"FTPDetailText)" & _
" VALUES (" & _
FTPID & "," & _
intLineCount + 1 & "," & _
strQ & strLogArray(intLineCount) & strQ & _
")"
CurrentDb.Execute (strSqlString)
intLineCount = intLineCount + 1
Loop Until intLineCount > UBound(strLogArray)

'
' Now update the FTPHistoryHeader record with the Timestamp and result
'
tblFTPQueueRecordSet("FTPQXferTimeStamp").value = strRunDate
tblFTPQueueRecordSet("FTPQStatus").value = boolSuccess
tblFTPQueueRecordSet.Update

strSqlString = "UPDATE tblFTPHistoryHeader " & _
"SET " & _
"FTPHistoryXferTimeStamp=" & strHash & strRunDate & strHash & _
"," & _
"FTPHistoryStatus=" & boolSuccess & _
" WHERE " & _
"FTPHistoryID=" & lngtblHistoryHeader
CurrentDb.Execute (strSqlString)

Code ends

Their are no records found in table tblFTPHistoryDetail.
The input log has 67 lines in it.
I manually created a row in the table and was assigned a primary key value of 1502.
I ran the routine again, no additional rows.
I then manually added another row and recieved PK field value of 1571. Do the math, 1502 + 67, next new record would be 1571!

Regards,
David Rees