how to create an error log for importing data?

[illusionek]

Hi Guys,

I am using below code to import various Excel spreadsheets into Access. However every now and again below code goes into error, usually because there is no ‘toimport’ range in the Excel file.

So I would like to use On Resume Next, so it can import all other files. But in order to do it, I need also to be able to somehow capture information about all files that went into error, so I can fix them.

I would like to create like an ‘error log’ and I would like Access to update it with information about all the files that were not uploaded + error message + date/time.

Ideally I would like it to be in Excel file. I tried to use DoCmd.TransferSpreadsheet but it does not do what I need.

So I would be grateful for any help I can get.

Function import()
Dim strFile As String
 
    DoCmd.SetWarnings False

'   Set file directory for files to be imported
    strPath = "C:\test\"
'   Tell it to import all Excel files from the file directory
    strFile = Dir(strPath & "*.xlsx*")
 
'   Start loop
    Do While strFile <> ""
        ' Import file
        DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="TestTable", FileName:=strPath & strFile, HasFieldNames:=False, Range:="toimport"
        ' Loop to next file in directory
        strFile = Dir
    Loop
End Function

 

[CJ_London]

try something like this - you'll need to determine the error number by running the code without error trapping

Function Import()
Dim strFile As String
 
    DoCmd.SetWarnings False
 
'   Set file directory for files to be imported
    strPath = "C:\test\"
'   Tell it to import all Excel files from the file directory
    strFile = Dir(strPath & "*.xlsx*")
 
'   Start loop
    [COLOR=red]on error goto errctrl[/COLOR]
    Do While strFile <> ""
        ' Import file
        DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="TestTable", FileName:=strPath & strFile, HasFieldNames:=False, Range:="toimport"
        ' Loop to next file in directory
        strFile = Dir
    Loop
    [COLOR=red]Exit Function[/COLOR]
 
[COLOR=red]errctrl:[/COLOR]
[COLOR=red]  select case err[/COLOR]
[COLOR=red]      case ? 'error for range not found[/COLOR]
[COLOR=red]          currentdb.execute("INSERT INTO tblimporterrors (filename, errNo, errDescription ) VALUES (" & strPath & strFile & ", " & err & ", " & err.description & ")")[/COLOR]
[COLOR=red]          resume next[/COLOR]
[COLOR=red]      case else[/COLOR]
[COLOR=red]           msgbox "Untrapped error in Import function " & err  & ": " & err.description[/COLOR]
[COLOR=red]  end select[/COLOR]
 
End Function

 

[illusionek]

Hello!

Many thanks for your help. Unfortunately, your code does not work

I have done some digging and I found out that text values need to be between single '. This solved problem for filename but I can't get it for error description.

I tried many different combination and I just can't get it right.

I would be grateful for any suggestions

 CurrentDb.Execute ("INSERT INTO tblimporterrors (filename, errNo, errDescription )Values('" & strFile & "' , " & Err.Number & " , '" & Err.Description & "')")

 

[CJ_London]

the description is text so should be surrounded with single quotes - sorry, should have included them

I can't see anything wrong with your code - unfortunately 'it doesn't work' doesn't give me much to go on -what errors are you getting or is it updating the table but without a description or what?

I presume you have created the table tblimportErrors?

Also, what is the error description? if it includes single quotation marks you need to modify your code slightly to replace it /them with two single quotes i.e.

& Err.Description &

becomes

& Replace(Err.Description,"'","''") &

If the above doesn't solve the problem, please be clearer on what isn't working

 

[illusionek]

Hi,

Yes, your are right, 'it doesn't work' is not terribly helpful. Schoolboy error

If I use

'" & Err.Description & "'

I get Run-Time error 3075, which is basically an error message that I expect to see when there is no 'toimport' range. So it looks like the whole error handling procedure is ignored.

I have done the table mentioned below and if I use the same code but I replace

'" & Err.Description & "'

with for example 0, the code works well.

It has something to do with the error description itself but I am not sure what's the problem if for example

'" & strFile & "'

works well.

I have tried suggested below Replace and it worked! MANY THANKS.

However could you please explain why? I do not really get it ... I understand that we replace single ' with double but nothing more

 

[CJ_London]

A single quote is used as a string delimiter e.g.

"'" & err.description & "'"

but if your err.description is "this doesn't work"

you would end up with

'this doesn't work'

So where does the string finish? after the n? No, so a double single quote tells vba to treat it as a single quote.

otherwise it assumes it is the end of the string and goes on to try to interpret the rest of the code - and fails.

If you have names in your database such as O'Reilly the system will fail in the same way you have found with err.description

Hackers can use this to damage systems because they write after the single quote some code that the system does understand.

For example if you have a login routine where a user types in their name,

so if they type fred'DROP TABLE SECURITY

(this is made up code but you get the idea) the system will delete the table called security