Import Log File to MS Access Table

[ericbartha]

Hello everyone,

I've created a UDF that writes a new line to a space delimited, apostrophe text-qualified, log file each time it is called. The UDF forces certain information to be captured and keeps everything in the same format, organized under fields. This all works perfectly. Now, however, I am trying to import this generated log file into MS Access 2010 using COM Objects (something I do not really understand yet), and I cannot get this step to work.

I've been able to get AutoIt to create an instance of Access and open the correct database using

Local $oAccess = ObjCreate("Access.application")

$oAccess.Visible = True
$oAccess.OpenCurrentDatabase("C:\myDatabase.accdb")

but I cannot get the next step, actually importing (appending) the data. I've been playing around with something like 

$oAccess.DoCmd.TransferText("acImportDelim","ImporteStatementLog","T_DailyeStatementAuthLog",$sLogFile)

where ImporteStatementLog = saved import step, T_* = desired table, and $sLogFile = the path of my log file.

Has anyone done anything like this before or could offer some insight? I am thoroughly confused and in need of some help. Thank you in advance,

Eric

[spudw2k]

Hmm.  Looks correct to me.  The $sLogFile points to a an actual file I assume. Are there spaces in the path?  Perhaps it needs to be wrapped in quotes....

[ericbartha]

Hi, Spudw2k.

Thanks for the reply. I have $sLogFile declared and initialized like so:

Local $sLogFile = "G:\AutoIT\Projects\Daily eStatement Auth File FTP\Test Environment\Logs\Daily_eStatement_Auth_File_Log - 020216.txt"

Full path, in quotes. I've also tried replacing the variable and just using the full path directly, also to no avail. I'm seriously baffled. 

[spudw2k]

Just for giggles can you try this

Local $sLogFile = '"G:\AutoIT\Projects\Daily eStatement Auth File FTP\Test Environment\Logs\Daily_eStatement_Auth_File_Log - 020216.txt"'

 

[kylomas]

ericbartha,

Have you tried it like this...

Local $sLogFile = '"G:\AutoIT\Projects\Daily eStatement Auth File FTP\Test Environment\Logs\Daily_eStatement_Auth_File_Log - 020216.txt"'

(what spudw2k suggested?)

kylomas

[ericbartha]

Hi guys,

Just tried it, but still no luck. This is what my console is outputting...

 

Quote

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"P:\AutoIt\Testing\Access\CreateDB.au3" (10) : ==> The requested action with this object has failed.:
$oAccess.DoCmd.TransferText("acImportDelim","ImporteStatementLog","T_DailyeStatementAuthLog",$sLogFile)
$oAccess.DoCmd^ ERROR
->16:24:26 AutoIt3.exe ended.rc:1
+>16:24:26 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 1.054
 

 

Maybe this helps give some direction? 

[spudw2k]

Let's try putting in a COM error handler to see a more detailed error message.  Try inserting this code into your script.

;Insert this line at top of script
Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")


Func _ErrFunc($oError)
    ; Do anything here.
    ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _
            @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _
            @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _
            @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _
            @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _
            @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _
            @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _
            @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _
            @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _
            @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF)
EndFunc   ;==>_ErrFunc

 

Edited February 8, 2016 by spudw2k

[PACaleala]

@ericbartha: your DoCmd line is too short and Access does not start the Import process.

The DoCmd line should end in comma False and the closing round bracket - if the source file does not have "FieldNames"

Microsoft in the Access help file says : "Indicate whether the first row contains column headings, or whether it should be treated as data."

 

[spudw2k]

According to this article, the fifth argument (HasFieldNames) is Optional and is False by default, so in theory adding ", False" as the fifth parameter shouldn't have any impact.

@ericbartha I think I spotted it.  In VB/VBA acImportDelim is an Integer Constant.  You are passing a string to the method.  Try passing the equivalent Integer value for that "TranserType".

Hint hint (0)

 

edit:  If you wanted to do it a/the "right way"
 

Global Const $ACIMPORTDELIM = 0

$oAccess.DoCmd.TransferText($ACIMPORTDELIM,"ImporteStatementLog","T_DailyeStatementAuthLog",$sLogFile)

 

Edited February 9, 2016 by spudw2k

[ericbartha]

Hey Spud,

So I REALLY thought adding the Int variable would be the solution; sadly it was not. 

I've noticed that after the DB opens, though, it closes right away. Could the issue be the way that event is being handled? i.e. is the DB, or even the instance of Access, closing before the DoCmd can be executed?

I'll try adding the COM debugging and add more once I get some feedback from that.

Thanks so much.

[spudw2k]

Hmmm, well the data type for that param definitely smells right.  The behavior you describe sure sounds like it could be impacting the rest of the script.  The fact that the windows shows then disappears could indicate a timing issue.  Adding the COM error handler might shed a little more light (better error codes).  Also try adding some delays at milestone lines (i.e. sleep for 5 seconds after .Visible = True) to find where the "break' is happening. 

Good luck

[ericbartha]

Thanks! Well I added a sleep after the OpenDB event and everything worked fine and Access stayed open. It was once the sleep ended and it tried to import the data that Access terminated, I am guessing because of the COM error, as shown below.

CreateDB.au3 (12) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     You cannot import this file.
    err.source is:      
    err.helpfile is:    
    err.helpcontext is:     -1
    err.lastdllerror is:    0
    err.scriptline is:  12
    err.retcode is:     0x800A7B1F

I am trying to research that err.number, but all I can find is issues with SharePoint. As far as the description goes: I verified that I could upload the file manually using the wizard and the same import step I created.

Maybe it is time to explore other options. 

[spudw2k]

That err.description line, "You cannot import this file", is a pretty clear indicator that the problem lies with the file. Now you just have to figure out what and why.  One forum thread I found suggested that some users were having issues importing files that did not have the correct file extension.  That doesn't appear to be the case based on the string you showed above.  Keep troubleshooting...getting closer.

 

edit: Have you tried making a different file to import just for testing?  Try creating a dummy file with just a couple of records and see what effect that has.

Edited February 9, 2016 by spudw2k

[ericbartha]

I figured that one was because of the way we had wrapped up the string with quotes i.e. we used '"\path\log.txt"'. I dropped the apostrophes and that part worked fine. Then it started telling me that the import step name did not exist; which makes no sense because I literally copied and pasted it from Access. I am trying to investigate this now.

[spudw2k]

good...progress

Edited February 9, 2016 by spudw2k

[ericbartha]

Spud; you're awesome - I got it!

So within Access there is a difference between an Import Saved Step and an Import Specification; where the TransferText function is looking for a specification and I was trying to pass it the name of an import step. While using the import step wizard, if you click "advanced" you can create an import specification. The name defined when you click "Save As" is the name that needs to be passed to the function.

Of course, I tried to make my own UDF so I can call this from any script, and it will not work through the UDF, but it will work through the original testing script you've been helping me with. Doh!

Either way, thanks so much, Spud!

[spudw2k]

Glad to help.  Way to go!

[ericbartha]

Hey Spud,

I have one more question if you're still willing to help. I have two separate scripts; the one above you helped me with and a UDF I made based on the above script. When running the standalone script it works well and appends the data to the table. However, when calling the UDF I get the following error:

aily_eStatement_Auth_File.au3 (127) : ==> COM Error intercepted !
    err.number is:      0x80020009
    err.windescription: Exception occurred.

    err.description is:     The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.
    err.source is:      
    err.helpfile is:    jeterr40.chm
    err.helpcontext is:     5003051
    err.lastdllerror is:    0
    err.scriptline is:  127
    err.retcode is:     0x800A0BEB

I found this article (http://www.aspdotnet-suresh.com/2013/01/c-microsoft-office-access-database.html) talking about the same error; stating that it is a permissions issue. In my case I would disagree; why would one script have permissions when the other does not?

Any thoughts?

[spudw2k]

Good question, but I suspect if it works fine outside of your UDF form, then there is something intrinsic about how your UDF is crafted that is causing the issue...right?  I think the error may not be the right direction to troubleshoot (at this time).  Can you share some code from your UDF so I can take a deeper look?

Curious that in the err.description is lists a "blank" file name ('').  How are you passing the filename to the method?

[ericbartha]

That is my thought as well, but there is nothing that jumps out at me. I'll post all the code, but take it easy on me, as I am new to programming and self-taught! Any suggestions for improvements or error-handling are welcome.

Regardless, here is the UDF:
 

expandcollapse popup

Func _HACU_AppendLog($sLogPath, $sProjectName)

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

Global Const $iAcImportDelim = 0
Global Const $sSpecName = "ImportHACULog"
Global $sTableName
Global Const $sAuditLogDB = "G:\AutoIT\Log Audit\LogAuditDB.accdb"

Global $sUndefinedProject = False

; The offical project name is the corrosponding project name found in the first field of the ScriptCurrentVersions.csv file. This is the compiled name of the script which can be found under the _
; #pragma Compile(Out, "*.exe") statement, found at the top of every projects script. Only one set of quotes is required. i.e. do not wrap the quotes found within the Compile Pragmatic in additional quotes. _
; Drop the file extension. (.exe)

Select

    Case $sProjectName = "DailyeStatements"

        $sTableName = "T_Log_DailyeStatements"

    Case $sProjectName = "ImportOAOArchiveDocuments"

        $sTableName = "T_ImportOAOArchiveLog"

    Case Else

        $sUndefinedProject = True

EndSelect

If $sUndefinedProject = True Then
    Return 2
    Exit

Else

    Global $oAccess = ObjCreate("Access.application")
    $oAccess.Visible = True
    $oAccess.OpenCurrentDatabase($sAuditLogDB)

    Sleep(1000)

    $oAccess.DoCmd.TransferText($iAcImportDelim, $sSpecName, $sTableName, $sLogPath)

EndIf

Return 0

EndFunc   ;==>_HACU_AppendLog

and here is the original script:

Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")
Global $sLogFile = "G:\AutoIT\Projects\Daily eStatement Auth File FTP\Test Environment\Logs\Daily_eStatement_Auth_File_Log - 021016.txt"
Global Const $ACIMPORTDELIM = 0
Global $CurrentDB

Local $oAccess = ObjCreate("Access.application")
$oAccess.Visible = True
$oAccess.OpenCurrentDatabase("G:\AutoIT\Log Audit\LogAuditDB.accdb")

Sleep(1000)

$oAccess.DoCmd.TransferText($ACIMPORTDELIM,"ImportHACULog","T_Log_DailyeStatements",$sLogFile)

The bottom works fine, where the top one doesn't. If you look at the end of the top code, within the else statement, the core of what it is doing is virtually identical.

Anyways, thanks for your continued support.

Edited February 10, 2016 by ericbartha