MSAccess UDF [updated]

[adik2dmax666]

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.

[Spiff59]

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 August 23, 2010 by Spiff59

[kjcdude]

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

[ptrex]

@kjcdude

The ADO bookmark property will get you going.

http://www.w3schools.com/ADO/prop_rs_bookmark.asp

Rgds

ptrex

[Monoxide]

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

[ChrisA]

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 November 16, 2010 by ChrisA

[StevenOfPomroyIL]

Can AutoIt run a VBA macro installed in a module in a database?

I need something similar to the Excel Macro Launcher:

$oExcel = _ExcelBookOpen($sFilePath1, 1, True)

$oExcel.Run("MyExcelMacro")

[netegg]

good! but a question. why can i find how to set main field（auto/custom define）

[netegg]

_accessSaveXML can't use with _xmldomwrapper.au3? if can, how? who has some idea? Thanks

[RGong]

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.

[GEOSoft]

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.

[DeltaRocked]

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

[dien44]

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 June 17, 2011 by dien44

[murfaul]

Does this UDF work with access 2010?

[ffdream62]

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"

expandcollapse popup

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 August 17, 2011 by ffdream62

[JailDoctor]

Hi,

I went to my web site >Code >> My extra UDFs >> Access.au3 and did not fin the Access.au3 or the zip files. Are they no longer available?

Where can I find them?

[ldub]

Here (Last modified: Sunday, May 01, 2011) :

http://autoit.mvps.org/udf_code.aspx?udf=access

[JailDoctor]

Thanks!

[Silver]

Thanks!

[GEOSoft]

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?