ericbartha

Import Log File to MS Access Table

30 posts in this topic

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

Share this post


Link to post
Share on other sites



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....

Share this post


Link to post
Share on other sites

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. :blink:

Share this post


Link to post
Share on other sites

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"'

 

Share this post


Link to post
Share on other sites

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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites

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? ;)

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

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 by spudw2k

Share this post


Link to post
Share on other sites

@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."

 

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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 by spudw2k

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. :(

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

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 by spudw2k

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Untitled.png

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#20 ·  Posted (edited)

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! :P Any suggestions for improvements or error-handling are welcome. ;)

Regardless, here is the UDF:
 

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 by ericbartha

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now