Need to generate a Log File for success/failure of Import Excel file to MS Access

[June7]

Please post code between CODE tags to retain indentation and readability.

You have WriteLog function but it is not called from any of the posted code. Why did you post it if it is not used?



Try adding error handler code to the procedure and put the INSERT sql within the error handler. Review https://www.accessforums.net/showthread.php?t=66664

[subhasmita]

Part of the code:
#
If FileExist(Filepath) Then


DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", Filepath, True
If IsNull(DLookup("[Z_UPD]", "NewData")) Then
If Err.Number <> 0 Then

erNum = Err.Number
erDes = Err.Description
MsgBox erNum
MsgBox erDes
DoCmd.RunSQL "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', 'erNum' , 'erDes' )"


'MsgBox "No new data to add"
End If
Else
MsgBox User & " file " & strFileName & "is (re-)created"
DoCmd.OpenQuery "qryAppend", acViewNormal
DoCmd.RunSQL sqllog
DoCmd.SetWarnings off

End If

and this is the log file result:

[aytee111]

The syntax of the INSERT is incorrect. Post # 12 shows it correctly. You need to use string concatenation - the & between variables.

(The # is an icon at the top right of your reply.)

[subhasmita]

Tried your query too in #12 still the same

[aytee111]

Please show the code each time - I can't guess at what changes you made. Post # 12 was your query, not mine - you had it right there.

[subhasmita]

i tried the below query

sqllogfail = "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"

but its results shows this type.


Any idea why its not inserting the correct values.. i tried each possible way but it is not inserting the err.number and the err.description.

[aytee111]

I cannot see the problem with this. Immediately after this statement, add this line: Debug.Print sqllogfail and post the resulting string.

Altho I guess that will show what the results are showing. This is driving me crazy too! Replace the Err.Number with 123 and the Err.Description with "test" - see what the sqllogfail string contains after that.

[subhasmita]

It shows blank in the immediate window. no result displayed.

[aytee111]

That cannot be possible - if you create a string and display it, it will show. You can't be going thru that piece of code?

[aytee111]

Copy and paste this piece of code into a new module, then hit f5 to run it. This is to test your string.

Public Function TestStr()
    Dim rst As Recordset
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset("table1", dbOpenDynaset)
    Debug.Print "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"
    
End Function

[subhasmita]

ya i have created a string it is displaying but not the sqllogfile.

[aytee111]

Post the entire subroutine as it is at the moment (use the # icon).

Before you do that, add a line at the very top under Option Compare, add Option Explicit. Go to Tools>Options and set the Require variable declaration to Yes, then Debug>Compile and see if you get any errors.

[subhasmita]

Below is the entire code..

#Option Compare DatabaseOption Explicit
Private Sub Form_Current()
On Error Resume Next
Dim Filepath As String
Dim sqllog
Dim erNum As String
Dim erDes As String
Dim sqllogfail
Dim User
Dim strFileName
sqllog = "INSERT INTO tblLog (ImportDateTime, Result, Comments ) VALUES (Now(), 'Success', 'Data imported Successfully')"
Filepath = "C:\Users\Subhasmita\Desktop\Work\Project\ACH Reject Data.xlsx"




If FileExist(Filepath) Then


DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", Filepath, True
If IsNull(DLookup("[Z_UPD]", "NewData")) Then
If Err.Number <> 0 Then

DoCmd.RunSQL "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"
Debug.Print sqllogfail

'MsgBox "No new data to add"
End If
Else
MsgBox User & " file " & strFileName & "is (re-)created"
DoCmd.OpenQuery "qryAppend", acViewNormal
DoCmd.RunSQL sqllog
'DoCmd.SetWarnings off

End If


Else

MsgBox "File Not Found.Check File name or location."
End If
Dim SQLDelete As String
SQLDelete = "Delete * From TempFromExcel"
DoCmd.RunSQL SQLDelete
'DoCmd.SetWarnings off

End Sub
Function FileExist(sTestFile As String) As Boolean
Dim lSize As Long
On Error Resume Next
'Present lenght to -1 because file length can be zero
lSize = -1
'Get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
End If
End Function

[aytee111]

You are no longer using sqllogfail, as far as I can see it has been replaced with

    If IsNull(DLookup("[Z_UPD]", "NewData")) Then
        If Err.Number <> 0 Then
            DoCmd.RunSQL "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"
            Debug.Print sqllogfail
            'MsgBox "No new data to add"
        End If
    Else

This produces:
INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', 3078, 'The Microsoft Access database engine cannot find the input table or query 'NewData'. Make sure it exists and that its name is spelled correctly.' )

So this code is working properly for me.

[subhasmita]

yes, it was my mistake now i have checked again the Debug.Print sqllogfail is printing the correct result.

INSERT INTO tblLog (ImportDateTime, Result, Comments,Error number,Error description ) VALUES (Now(), 'Failed', 'Data import failed', 2471, 'The expression you entered as a query parameter produced this error: '[Z_UPD]'' )

But not inserting into the table..May be there is some problem in my table design- Let me check my table.

Thanks a lot for your help.