Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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
    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.

  2. #17
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    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:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	8 
Size:	38.3 KB 
ID:	29806

  3. #18
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.)

  4. #19
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    Tried your query too in #12 still the same

  5. #20
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.

  6. #21
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    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.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	7 
Size:	15.6 KB 
ID:	29837
    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.

  7. #22
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.

  8. #23
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    It shows blank in the immediate window. no result displayed.

  9. #24
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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?

  10. #25
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Copy and paste this piece of code into a new module, then hit f5 to run it. This is to test your string.

    Code:
    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

  11. #26
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    ya i have created a string it is displaying but not the sqllogfile.

  12. #27
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    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.

  13. #28
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    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

  14. #29
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are no longer using sqllogfail, as far as I can see it has been replaced with
    Code:
        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.

  15. #30
    subhasmita is offline Novice
    Windows 10 Access 2007
    Join Date
    Jul 2017
    Posts
    25
    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.

Page 2 of 3 FirstFirst 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