Jump to content
Sign in to follow this  
ffdream62

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

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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"

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

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  

×