Jump to content
Sign in to follow this  
GEOSoft

MSAccess UDF [updated]

Recommended Posts

I can use this UDF even without MS access installed. The udf creates objects available in windows by odbc data sources.

Thanks to GEOSoft's udf, I was able to create an inventory system using MDB as a database. ;)


First learn computer science and all the theory. Next develop a programming style. Then forget all that and just hack. -George Carrette[sub]GD Keylogger Creator (never released)[/sub][sub]Garena Autojoin v3.0[/sub]

Share this post


Link to post
Share on other sites

I was going to suggest that many of the functions would be more flexible if you allowed the $adSource parameter to be either the full file/pathname to a database, or an object to an already-open database, but...

I am a little confused by what this code, at the top of many functions, is doing?

$oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('',$oADO)
    Else
        $oADO = _dbOpen($adSource)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)

Aren't you simply assigning a string to the $oADO variable, therefore it will never pass the IsObj() test?

I was thinking something like this might be useful:

If IsObj($adSource) Then
        $oADO = $adSource
    Else
        $oADO = _dbOpen($adSource)
        If IsObj($oADO) = 0 Then Return SetError(1)
    EndIf
Edited by Spiff59

Share this post


Link to post
Share on other sites

I'm using _accessQueryLike() on a script that pulls from a database containing over a million financial transaction entries.

I use the querylike to search for the current date's revenue and then it outputs it and is graphed in real time.

One major issue I've been running into is the amount of time it takes for querylike to search through all those entries.

It's currently taking about 5-6 seconds. It's fine that it's taking that long, but I have about 5 autoit scripts constantly pulling data every 30 seconds as well as the core server program writing data constantly.

Is there anyway to tell querylike what line to start searching from?

My goal would be to record where the querylike found the data last time and start searching from that point on next run.

Thanks

Share this post


Link to post
Share on other sites

GEOSoft, I saw where others in this thread had posted the same error I'm now getting:

C:\Program Files\AutoIt3\Include\Access.au3 (72) : ==> The requested action with this object has failed.:
.Fields.Item($I -1) = $rData[$I]
.Fields.Item($I -1) = $rData[$I]^ ERROR
>Exit code: 1    Time: 4.377

I didn't see where you had found out what the deal was with this error, any chance that's been figured out since then?

I have been searching for the answer to this same post and some other error's for the last week.

I found another thread with the COM error code snippet posted below.

After adding to the end of "AccessConstants.au3" script I was able to discover exactly what my problem was.

Hopefully this will assist someone else with the same issue.

Thanks GEOSoft for the great UDF! Now its just a learning curve to get everything to work as I need it to.

Global $oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

Func MyErrFunc()
    $HexNumber = Hex($oMyError.number, 8)
    MsgBox(0, "COM Error", "COM Error Details:" & @CRLF & @CRLF & _
            "err.description is: " & @TAB & $oMyError.description & @CRLF & _
            "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
            "err.number is: " & @TAB & $HexNumber & @CRLF & _
            "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
            "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
            "err.source is: " & @TAB & $oMyError.source & @CRLF & _
            "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
            "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1)
EndFunc ;==>ComErrHandler

Share this post


Link to post
Share on other sites

While we're on the Type Mismatch errors, I might as well add my $0.02 in...

;$adCol = 'a NUMBER | b DATETIME | c TEXT(50) | d TEXT(50) | e TEXT(50) | f MEMO | g YESNO | h YESNO'

$rData = $i_RecNum & '|' & $s_PubDate & '|' & $s_Section & '|' & $s_ArticleTitle & '|' & $s_Author & '|' & $s_Article & '|' & $b_Active & '|NO'

_accessAddRecord($s_DBFile , $s_MDBTblName, $rData, $adCol = 0)

This code fails at : $b_Active & '|NO'

I've added the recommended onerror code and both fields show to fail with Type Mismatch.

Both fields are YESNO fields. No matter what I try (T/F, Yes/No, YES/NO, TRUE/FALSE) I get a Type Mismatch.

The DB is set for those two to be YESNO.

If I take OFF that part it works fine.

What do I need to pass it in order for YES/NO fields to be updated? Has anyone been able to successfully add/update a Y/N field in an MDB?

Edited by ChrisA

Share this post


Link to post
Share on other sites

The "Access.au3" downloaded from the website seems to have a bug in Function "_accessQueryLike". The line "$Rtn = $Rtn & Chr(29);;<<====== Separate the records with a non-printable character" should be " $Rtn = $Rtn & $oRec.Fields($adCol).Value & Chr(29)" in order to correctly return values of the specified field when $adFull <> 1.

Share this post


Link to post
Share on other sites

Thanks. If I ever go back to supporting the UDF I will look at that. As it is now I no longer install Access at all so I can't test it.

I might install it on one of the systems just for testing soon, in the meantime just make the changes you have to for your needs but please continue to post those changes here.


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

Thanks. If I ever go back to supporting the UDF I will look at that. As it is now I no longer install Access at all so I can't test it.

I might install it on one of the systems just for testing soon, in the meantime just make the changes you have to for your needs but please continue to post those changes here.

Some issue with the Array - rectified.

Func _accessAddRecord($adSource, $adPwd, $adTable, $rData, $adCol)
    If Not IsArray($rData) Then
        $rData = StringSplit($rData, '|')
    EndIf
    $oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('', $oADO)
    Else
        $oADO = _dbOpen($adSource, $adPwd)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)
    If Not IsObj($oADO) Then Return SetError(2, 0, 0)
    $oRec = _dbOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    With $oRec
        .Open("SELECT * FROM " & $adTable, $oADO, $adOpenStatic, $adLockOptimistic)
        .AddNew
        If IsArray($rData) Then
            For $I = 1 To $rData[0]
                $rData[$I] = StringStripWS($rData[$I], 1)
                .Fields.Item($I-1) = $rData[$I]
            Next
        Else
            .Fields.Item($adCol) = $rData
        EndIf
        .Update
        .Close
    EndWith
    $oADO.Close()
EndFunc   ;==>_accessAddRecord

Share this post


Link to post
Share on other sites

hi GEOSoft.

i need some function: add attack file, copy attack file and activate contents attack file in Access. can you help me?. Thanks

Edited by dien44

Share this post


Link to post
Share on other sites

I managed to create a function to put a query or a full table in an array

_accessQueryToArray

$adSource = full path to the access database (including the database)

$adTable = table name (in bracket)

$adCol = Colomn NAME to search in

$Find = Value to search (empty value means "show all"

Func _accessQueryToArray($adSource,$adTable, $adCol,$Find = "")
    
    Local $I,$Rtn
    Local $I
    Local $ResultArray[1][15]
    
    $oADO = 'ADODB.Connection'
    If IsObj($oADO) Then
        $oADO = ObjGet('',$oADO)
    Else
        $oADO = _dbOpen($adSource)
    EndIf
    If IsObj($oADO) = 0 Then Return SetError(1)         
    $oRec = _dbOpenRecordset();ObjCreate("ADODB.Recordset")
    If IsObj($oRec) = 0 Then Return SetError(2)
    
    ;The query
    $oRec.Open ("SELECT * FROM "& $adTable & " WHERE " & $adCol & " Like '%" & $Find & "%'", $oADO, $adOpenStatic, $adLockOptimistic)
    
    If $oRec.RecordCount < 1 Then
        Return SetError(1)
    Else
        SetError(0)
        
        $oRec.MoveFirst()
        ;;MsgBox(0,'TEST', "Number of records: " & $oRec.RecordCount);;<<======  For testing only
        
        $ligne = 0
        $col = 0

        $maxcol = _accessCountFields($adSource,$adTable)
        
        Do   
            For $I = 0 To $maxcol-1
                if $col = 0 then
                    $ResultArray[$ligne][$col] = $oRec.Fields($I).Value
                    $col = $col+1               
                else
                    $ResultArray[$ligne][$col] = $oRec.Fields($I).Value
                    $col = $col+1
                endif
            
                if $col = $maxcol Then
                    $ligne = $ligne + 1
                    redim $ResultArray[$ligne+1][$col]
                    $col = 0                        
                endif
            Next
            $oRec.MoveNext()
        Until $oRec.EOF

                redim $ResultArray[$ligne][$maxcol]

        $oRec.Close()
        $oADO.Close()
      
      Return $ResultArray
   EndIf
EndFunc

EDIT : Added a REDIM of the array by the end of the script to avoid an empty line.

Edited by ffdream62

Share this post


Link to post
Share on other sites

Read This

this udf is no longer supported and is almost guaranteed to fail under the new versions of autoit.

it requires a rewrite to make it compatable with the new autoit com code and since i don't even use msaccess any longer you can rest assured that i won't be doing it.

it will not be updated and my site doesn't even contain links to the file now.

within a few minutes i will be removing the file entirely since it was only left up there for storage purposes anyway.

do not email me asking for support

do not pm me asking for support.

do not post asking for support

i've said many times it's not supported any longer and yet people insist on using it so you do so at your own risk.

have i got my point across yet?


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...