[MS ACCESS] How can I put a full table in an array ?

[ffdream62]

Hi everybody,

I'm searching how I can pick a full table from an Access Db and put it in an array.

I already managed to search & write into a table (using the very nice Access.au3) but not to pick arrays yet.

Can you help me ?

Thx in advance

[Crazyd]

If you just want the data you could export it as a CSV file and load it into an array, but besides that I have no clue. Access I haven't touched yet with AutoIT.

[ffdream62]

I managed to create a function to do this.

I used the Access.a3u shared as a base and added my function to the bottom.

_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