Import Log File to MS Access Table

spudw2k · February 10, 2016

Ok, I see you moved the $sLogPath variable into a paramater position for your function. When you call the _HACU_AppendLog function are you feeding it the correct string for that param?

Just some initial advice:

I recommend reading a little bit about the differences between Global and Local (and more) variables. Typically you never want to declare a Global variable withing a function. Also, variables used within a function that have no purpose or business outside of that function should be scoped Local. A brief read and look at the examples will help you understand.
Write code as modular as possible. This can come down to programmer's taste, but can be very useful to reduce code into workable, reliable functions. I'm working on re-writing your UDF in such a fashion and will post it here as soon as I can.

ericbartha · February 10, 2016

Thanks, Spud.

I am not sure what you mean by "variable into a parameter position". Does this mean the variable, passed from my script, is used directly in the DoCmd.TransferText() function? I do know that the variable is the correct string, as I use the same variable for a different UDF that actually writes the lines to the log. I also created four message boxes to display the values being used by the DoCmd.TransferText() function; verifying they were the correct values. Everything, there, at least, checked out.

I will read through the variable declaration page you shared with me to hopefully get a better understanding. I know this is something I did not fully grasp when I started. I understand the concept and the idea of it, but not necessarily what each one means and does.

I will try and do some research on making code modular, too. I do not fully understand what you mean, but, hopefully, the re-write you provide brings some clarity.

Also, thank you so much for doing the re-write! I think I will learn a lot...

Eric

Edited February 10, 2016 by ericbartha
Clarity

spudw2k · February 10, 2016

Here's some code to chew on. This is untested, so I may have made a mistake somewhere, but you can at least see what I am talking about and play around. Feel free to ask me anything if you need clarification. There are soooo many ways to accomplish things...this is just one way.

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

Demo()

Func Demo()
    ;Local Variables
    Local $sDBPath = "G:\AutoIT\Log Audit\LogAuditDB.accdb"
    Local $sLogFile = "G:\AutoIT\Projects\Daily eStatement Auth File FTP\Test Environment\Logs\Daily_eStatement_Auth_File_Log - 021016.txt"

    ;Create Access.Aplpication COM Obj
    Local $oAccess = _AccessApplicationObjCreate()

    ;Show Access Application Window
    _AccessApplicationShow($oAccess)

    ;Open Access Database
    _AccessOpenDB($sDBPath)
    
    ;Append Log File
    _HACU_AppendLog($oAccess, $sLogFile, "DailyeStatements")
EndFunc


Func _AccessApplicationObjCreate()
    ;Create Access.Application COM Obj
    Local $oAccess = ObjCreate("Access.application")
    ;If ObjCreate failed, Return 0 and Set Error to 1
    If Not IsObj($oAccess) Then Return SetError(1, 0, 0)
    ;Return COM Obj
    Return $oAccess
EndFunc

Func _AccessApplicationShow($oAccess, $bVisible = True)
    ;If Not Valid COM Obj, Return 0 and Set Error to 1
    If Not IsObj($oAccess) Then Return SetError(1, 0, 0)
    ;Show or Hide Window
    If $bVisible Then
        ;Default
        $oAccess.Visible = True
    Else
        $oAccess.Visible = False
    EndIf
    ;Return 1
    Return 1
EndFunc

Func _AccessOpenDB($sDBPath)
    ;If Not Valid COM Obj, Return 0 and Set Error to 1
    If Not IsObj($oAccess) Then Return SetError(1, 0, 0)
    ;If $sDBPath file path doesn't exist, Return 0 and Set Error to 2
    If Not FileExists($sDBPath) Then Return SetError(2, 0, 0)
    ;Execute OpenCurrentDatabase Method and Return Nothing  - https://msdn.microsoft.com/en-us/library/office/ff837226.aspx
    Return $oAccess.OpenCurrentDatabase($sDBPath)
EndFunc


Func _HACU_AppendLog($oAccess, $sLogPath, $sProjectName)
    ;If Not Valid COM Obj, Return 0 and Set Error to 1
    If Not IsObj($oAccess) Then Return SetError(1, 0, 0)
    ;If $sLogPath file path doesn't exist, Return 0 and Set Error to 2
    If Not FileExists($sLogPath) Then Return SetError(2, 0, 0)
    ;Validate Project Name
    Local $sTableName = ""
    Switch $sProjectName
        Case "DailyeStatements"
            $sTableName = "T_Log_DailyeStatements"
        Case "ImportOAOArchiveDocuments"
            $sTableName = "T_ImportOAOArchiveLog"
        Case Else
            ;If invalid Project Name. Return 0 and Set Error to 3
            Return SetError(3, 0, 0)
    EndSwitch
    ;Local Variables
    Local Const $iAcImportDelim = 0
    Local Const $sSpecName = "ImportHACULog"
    ;Execute TransferText Import Method
    Return $oAccess.DoCmd.TransferText($iAcImportDelim,$sSpecName,$sTableName,$sLogFile)
EndFunc

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

Note. There is a fair amount of error checking going on...always a good practice...but no cleanup code. If you are feeling adventurous see if you can write some "cleanup" functions to properly close the database and release the COM object.

ericbartha · February 10, 2016

So, without even going through this yet, just glancing it over, when you mentioned being as modular as possible, you meant calling functions for everything. Right now, most of my scripting is being handled by the main script, with only one or two functions.

Is there a benefit to doing it this way? I read the entire page on declaring variables and I understand, for memory purposes, local variables inside functions are more efficient since they are destroyed at the end of the function unless byref or static is used. Other than this, though, is there another reason for coding this way?

I will go through this in detail and see what I can learn and test it. Hopefully, it solves the initial problem as well! Thanks again, Spud.

Edited February 10, 2016 by ericbartha
Clarity

spudw2k · February 10, 2016

There can be many advantages to writing modularized code. The two best reasons--in my opinion--are reduction of code and optimization. Now, not every function needs to be as irreducible as possible. The key is to eliminate redundancy.

Say I want to do the following:

Create a GUI
- Add a control
- Set control data
- Set control state
- Repeat 4 times

You could "hardcode" each control,

Global Const $GUI_EVENT_CLOSE = -3
Global Const $GUI_DISABLE = 128

GUICreate("Test", 200, 100, -1, -1)

GUICtrlCreateLabel("", 0, 0, 60, 20)
GUICtrlSetData(-1, "Data: " & 1)
GUICtrlSetState(-1, $GUI_DISABLE)

GUICtrlCreateLabel("", 0, 20, 60, 20)
GUICtrlSetData(-1, "Data: " & 2)
GUICtrlSetState(-1, $GUI_DISABLE)

GUICtrlCreateLabel("", 0, 40, 60, 20)
GUICtrlSetData(-1, "Data: " & 3)
GUICtrlSetState(-1, $GUI_DISABLE)

GUICtrlCreateLabel("", 0, 60, 60, 20)
GUICtrlSetData(-1, "Data: " & 4)
GUICtrlSetState(-1, $GUI_DISABLE)

GUICtrlCreateLabel("", 0, 80, 60, 20)
GUICtrlSetData(-1, "Data: " & 5)
GUICtrlSetState(-1, $GUI_DISABLE)

GUISetState()

While 1
    $iMsg = GUIGetMsg()
    If $iMsg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd

GUIDelete()

Exit

or you could make a function to handle the control parts and reduce your code

Global Const $GUI_EVENT_CLOSE = -3
Global Const $GUI_DISABLE = 128

GUICreate("Test", 200, 100, -1, -1)

_GUICtrlCreateLabel(1)
_GUICtrlCreateLabel(2,0,20)
_GUICtrlCreateLabel(3,0,40)
_GUICtrlCreateLabel(4,0,60)
_GUICtrlCreateLabel(5,0,80)

GUISetState()

While 1
    $iMsg = GUIGetMsg()
    If $iMsg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd

GUIDelete()

Exit

Func _GUICtrlCreateLabel($sData = "", $iX = 0, $iY = 0, $iWidth = 60, $iHeight = 20)
    GUICtrlCreateLabel("", $iX, $iY, $iWidth, $iHeight)
    GUICtrlSetData(-1, "Data: " & $sData)
    GUICtrlSetState(-1, $GUI_DISABLE)
EndFunc

Even still, you could employ an Array to "streamline" the process

Global Const $GUI_EVENT_CLOSE = -3
Global Const $GUI_DISABLE = 128

GUICreate("Test", 200, 100, -1, -1)

Local $aLabels[5][5]=[[1,0,0,60,20],[2,0,20,60,20],[3,0,40,60,20],[4,0,60,60,20],[5,0,80,60,20]]

For $iX = 0 to Ubound($aLabels)-1
    _GUICtrlCreateLabel($aLabels[$iX][0],$aLabels[$iX][1],$aLabels[$iX][2],$aLabels[$iX][3],$aLabels[$iX][4])
Next

GUISetState()

While 1
    $iMsg = GUIGetMsg()
    If $iMsg = $GUI_EVENT_CLOSE Then ExitLoop
WEnd

GUIDelete()

Exit

Func _GUICtrlCreateLabel($sData = "", $iX = 0, $iY = 0, $iWidth = 60, $iHeight = 20)
    GUICtrlCreateLabel("", $iX, $iY, $iWidth, $iHeight)
    GUICtrlSetData(-1, "Data: " & $sData)
    GUICtrlSetState(-1, $GUI_DISABLE)
EndFunc

This is an over simplified example--as you can see, there are many options.

ericbartha · February 10, 2016

That makes sense. I definitely want to be as efficient as possible. I am trying to modularize the main script now. The immediate benefit I see is I have a few steps that I do in every single script (like a Live boolean to toggle "testing" on or off) that really could be a function instead. I'm noticing that as I make these changes, once I understand what is happening, it is actually much easier to follow.

ericbartha · February 10, 2016

Alright, so I have a boolean Global Const $bIsLive = True. Then I have a UDF that checks to see if the boolean is live and if the script is uncompiled. If so, it prompts the user ensuring they actually want to run (just a redundancy to ensure the developer has the live/testing boolean set correctly). It looks like this:

;   Script Status
;
;   Current Mode: LIVE
;   Use the below Boolean, $bIsLive, to change Live/Testing status.

Global Const $bIsLive = True
Global $bContinueLive

$bContinueLive = _HACU_CheckIsLive($bIsLive)
If $bContinueLive = False Then
    ;Write to log
    Exit
EndIf

MsgBox(0,"","Do something else")

Func _HACU_CheckIsLive($bLiveStatus)

    Local $iLiveCheck
    If $bLiveStatus = True And @Compiled = 0 Then
        $iLiveCheck = MsgBox(1, "Live Version, Not Compiled...", "You are running in live and from the .au3 file (not compiled). Are you sure you wish to continue?", 30)
        If $iLiveCheck <> 1 Then
            Return SetError(1, 0, False)
        Else
            Return True
        EndIf
    
    EndIf
EndFunc   ;==>_HACU_CheckIsLive

I tried to follow the rules about declaring variables, setting error codes, and returning values. Does this look a little more like an actual script? Anything that SHOULD be changed? Anything that could be changed?

Edited February 11, 2016 by ericbartha

spudw2k · February 10, 2016

Looks reasonable to me sir. I must say, learning to employ "good" coding habits early on is a very positive thing. I wish I started as "proper" as you are shaping up to be.

One (convoluted) comment:
When dealing with Global variables there is not (necessarily) a need to pass that value to a param. You could call it straight from within a function. This is particularly useful when you need to "change" a global variable from within a function.

;Change this
$bContinueLive = _HACU_CheckIsLive($bIsLive)
;...
Func _HACU_CheckIsLive($bLiveStatus)
;...
    If $bLiveStatus = True And @Compiled = 0 Then
    
;To this
_HACU_CheckIsLive() ;Remove Parameter
;...
Func _HACU_CheckIsLive()    ;Remove Paramter
;...
    If $bIsLive = True And @Compiled = 0 Then   ;Refere to $bIsLive directly

One instance however when you would want to pass it as a variable is if you want to pass a "copy" in order to maintain the integrity of the Global variable. Be sure to read about ByRef too when it comes to variables. That can be important when you get into this modular stuff and want to code efficiently.

edit: I'm not an expert and don't claim to be, but I do have several years of AutoIt scripting experience...which counts for something, right?
Seriously, I've learned a lot about the language and about Windows programming in general since I started with AutoIt. Take what I say for what it's worth until someone corrects it and provides something better.

Edited February 10, 2016 by spudw2k

ericbartha · February 11, 2016

I sincerely appreciate your advice and guidance. The only other "programming" I have done is VBA for Excel, Access, and Outlook scripting, Arduino and some Javascript, HTML(5), and CSS(3). I started a new job in December have been trying to learn AutoIt since.

I did read about ByRef and I totally understand the potential of it. The reason I passed the variable to the parameter is because the above example is actually a little misleading. The function is actually contained within a separate .au3 file (hacu.au3), which I have included (#include <hacu.au3). The reason I have done it this way is because the above function is going to be a standard requirement for all scripts. A lot of our scripts upload files to 3rd parties and we do not want to send duplicate information, so this function is a little extra security to ensure we are only running the "live" version when we really intended to.

With that being said, if the included functions can actually read the global variables from whatever script is calling it, then that is a whole different story. I didn't think it worked that way, but I could be wrong. Maybe I will try it..

Edit: Oh, and several years of AutoIt programming is definitely worth something. Especially when considering I have about 2 months...

Edited February 11, 2016 by ericbartha

spudw2k · February 11, 2016

Take a look at any given "Constants" include file (standard UDF) and you see they are littered (in a good way) with Globals (GUIConstantsEx.au3 for example).

One more tip for when dealing with multiple .au3 scripts. There is a statement found in all of the bundled include files as the first line, namely "include-once". I just wanted you to be aware of it. A quick read and you'll see why.

So, you "should" be able to create a Global variable in any script and reference it from any other script as long as the source script with the desired global is included. The best thing (at least organizationally) would be to store the Global relevant to rest of the functions contained within one script (livecheck) and include livecheck in whatever scripts need to use it.

You'll have to do some testing as far as when the check executes. My recommendation would be author the check script so it works stand-alone, then in theory when you call the "include" it should execute at that time.

Import Log File to MS Access Table

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members