Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25

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

    Hi

    I am using a vba code to import excel file to ms access. As per the requirement i need to generate a log file for the successful/failure of the import. I am not sure how to do this any help will be appreciated.

    I am doing the following steps.

    below is my code:

    Option Compare Database






    Private Sub Form_Current()
    Dim Filepath As String


    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_UPDT]", "NewData")) Then
    MsgBox "No new data to add"

    Else
    MsgBox User & " file " & strFileName & "is (re-)created"
    DoCmd.OpenQuery "qryAppend", acViewNormal
    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

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    1) Create a log table with:
    ID
    ImportDateTime (date/time)
    Result(text)
    Comments (text)

    2) Create an Append query which appends a record to this table using Now() for the ImportDateTime, "Success" as the Result, and "test" as the Comments
    3) Go to SQL view of the query and copy it
    4) Every time you have a MsgBox line add:
    DoCmd.RunSQL " copy the SQL from (3) here
    5) Change the SQL as required - Success or Fail in Result, the Msgbox in the Comments

    Change as you see fit.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Noticed that this is being done on the form's OnCurrent event. This will run multiple times which I am sure you don't want. Create a command button to do this instead.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    First, need a table to hold records. What data do you want to retain - who, when, what?

    Then somewhere in your code something like:

    CurrentDb.Execute "INSERT INTO tablename(User, LogDate, FileName) VALUES '" & Environ("USERNAME") & "', Now(), '" & strFileName & "')"

    EDIT: Didn't see aytee111's reply, good stuff.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Quote Originally Posted by aytee111 View Post
    1) Create a log table with:
    ID
    ImportDateTime (date/time)
    Result(text)
    Comments (text)

    2) Create an Append query which appends a record to this table using Now() for the ImportDateTime, "Success" as the Result, and "test" as the Comments
    3) Go to SQL view of the query and copy it
    4) Every time you have a MsgBox line add:
    DoCmd.RunSQL " copy the SQL from (3) here
    5) Change the SQL as required - Success or Fail in Result, the Msgbox in the Comments

    Change as you see fit.


    Thanks so much this is working fine for me, But when the import will fail how can i log this means i want to know in which of the scenario import can be failed. do i need to use error handling.

    one scenario can be if data type mismatch then the data can not be imported ( This is my assumption) what other things can be possible and how to log this failure inside the script.

    I am sorry may be these are silly questions to ask but i am new to this and i am learning.

  6. #6
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Quote Originally Posted by June7 View Post
    First, need a table to hold records. What data do you want to retain - who, when, what?

    Then somewhere in your code something like:

    CurrentDb.Execute "INSERT INTO tablename(User, LogDate, FileName) VALUES '" & Environ("USERNAME") & "', Now(), '" & strFileName & "')"

    EDIT: Didn't see aytee111's reply, good stuff.

    Thanks for your help.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    At the beginning of the routine, add
    On Error Resume Next

    Then after each step check the error
    If Err <> 0 Then
    ...add a record to the log table using Err.Number and Err.Description

    Post your code if you have questions - but please add code tags around it - use the # icon.

  8. #8
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    I have created a sql query to insert error into the table
    Dim ern As String
    Dim erdesc As String
    ern = Err.Number
    erdesc = Err.Description


    sqllogfail = "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed','ern','erdesc' )"

    And used as If Err <> 0 Then
    DoCmd.RunSQL sqllogfail

    and created a run time error just to check but it is not writing into the log file.
    Option Compare Database


    Private Sub Form_Current()
    Dim Filepath As String
    Dim sqllogpass
    Dim ern As String
    Dim erdesc As String
    ern = Err.Number
    erdesc = Err.Description


    sqllogpass = "INSERT INTO tblLog (ImportDateTime, Result, Comments ) VALUES (Now(), 'Success', 'Data imported Successfully')"


    sqllogfail = "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed','ern','erdesc' )"




    Filepath = "C:\Users\Subhasmita\Desktop\Work\Project\ACH Reject Data.xlsx"


    On Error Resume Next


    If FileExist(Filepath) Then


    DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", Filepath, True


    If IsNull(DLookup("[Z_UPD]", "NewData")) Then

    If Err <> 0 Then
    'MsgBox Err.Description
    DoCmd.RunSQL sqllogfail
    End If

    Else
    'MsgBox User & " file " & strFileName & "is (re-)created"
    DoCmd.OpenQuery "qryAppend", acViewNormal
    DoCmd.RunSQL sqllogpass
    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

    Function WriteLog(strEvent As String, strProcess As String)
    Dim sSQL As String


    sSQL = "INSERT INTO tblLog ( LogEvent, LogProcess ) " _
    & "VALUES ('" & strEvent & "', '" & strProcess & "')"


    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True

    End Function

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    sqllogfail = "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"

    Change Err_Type on your table to be datatype Number/Long Integer

    Copy this into SQL view of a new query - remove the quotes and put real information into the error fields. Run it and see if it adds a record to the table.

    PLEASE use the # to post code.

  10. #10
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    I have changed the data type to Number now it is inserting a row in the log table but for Err_type it is inserting 0 always and description as blank but when i put msgbox then the error number is coming 2471.

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post the SQL string that you are using, as well as the Msgbox string.

  12. #12
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    sqllogfail = "INSERT INTO tblLog (ImportDateTime, Result, Comments,Err_Type,Err_Description ) VALUES (Now(), 'Failed', 'Data import failed', " & Err.Number & ", '" & Err.Description & "' )"

    This is the sql string i m using.

    For this it is inserting as
    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	22.6 KB 
ID:	29805


    in the table.

    But when i am using msgbox err.number its coming 2471.

  13. #13
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Oh! It is the timing that is causing this. When you create the sqllogfail string the error is zero, that is why. It was a good thought, to create the string and keep using it, but unfortunately in this case it won't work. You will need to recreate the SQL after every failure in order the get the latest values of error number and description.

  14. #14
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    i also thought the same and tried creating the string after the error but its giving the same result.

  15. #15
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post your code, the part where you have this statement.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Excel file to MS Access
    By subhasmita in forum Import/Export Data
    Replies: 3
    Last Post: 07-27-2017, 03:24 PM
  2. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  3. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  4. Import Excel file to Access
    By emmett in forum Import/Export Data
    Replies: 3
    Last Post: 04-06-2012, 05:27 AM
  5. Link Excel file to MS Access and generate rep
    By Priceless in forum Import/Export Data
    Replies: 14
    Last Post: 02-21-2011, 02:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums