MS Access database - List of files

[price98]

Hi,

In Microsoft access, I created the database as shown below figure.In autoit, I have to create check boxes named as Controller,Simulator,Testing (as shown in figure). Suppose If I select testing check box then all related files to testing attribute  has to shown in database. Can anyone give any idea ?

Thanks

[FrancescoDiMuro]

Hi @price98

• ADO (by mLipok) for managing MS Access Database;
• GUICtrlRead() to read the state of your checkboxes.

Post always your code, so we can see if we can help you more on it.

Cheers

[price98]

Hi @FrancescoDiMuro,

Previously one of our friend helped in this code. At that time , I am doing only for desktop files. I don't know about ADO.

Thanks

#include <GUIConstantsEx.au3>
#include <File.au3>

Local $aFileList
Local $hGui = GUICreate("ProgramData", 450, 300)
Local $hButton_txt = GUICtrlCreateCheckbox("File list TXT", 144, 32, 97, 17)
Local $hButton_pdf = GUICtrlCreateCheckbox("File list PDF", 144, 62, 97, 17)
Local $hList = GUICtrlCreateList("", 10, 100, 430, 200)
GUISetState(@SW_SHOW)

$aFileList = _FileListToArray(@DesktopDir, "*.*", 1)  ; add your path here

While True
    $sMsg = GUIGetMsg()
    Switch $sMsg
        Case -3
            Exit
        Case $hButton_txt, $hButton_pdf
            $want_txt = IsChecked($hButton_txt)
            $want_pdf = IsChecked($hButton_pdf)
            GUICtrlSetData($hList, '')
            If $want_txt Or $want_pdf Then
               For $i = 1 To UBound($aFileList) - 1
                   $ext = StringLower(StringRight($aFileList[$i],4))
                   If ($want_txt And $ext == '.txt') Or _
                      ($want_pdf And $ext == '.pdf') Then
                       GUICtrlSetData($hList, $aFileList[$i])
                   EndIf
               Next
            EndIf
    EndSwitch
WEnd

Func IsChecked($control)
    Return BitAnd(GUICtrlRead($control),$GUI_CHECKED) = $GUI_CHECKED
EndFunc

 

[FrancescoDiMuro]

7 hours ago, price98 said:

I don't know about ADO.

Google is, and will always be your best friend

[price98]

@FrancescoDiMuro

Thanks for suggestion. I am new to autoit. So, I am unable to solve the above problem. Can you give me small example?

I have ´ .accdb´ file. In that file the data is present which is shown in above figure. Can I have any chance to edit previous code?

[FrancescoDiMuro]

@price98
Take a look here
There are a lot of Examples to play with.

[price98]

@FrancescoDiMuro

I already open that page. I try to open ADO Example Access. It shows like ´ This page is still a work in progress. ´

[FrancescoDiMuro]

 

[mLipok]

@price98 did you use my ADO.au3 UDF  ?

 

[price98]

thank you guys for your suggestions

[price98]

hi,

I done upto here. Can anyone tell me how to do from here?

expandcollapse popup

#include <GUIConstantsEx.au3>
#include <FileConstants.au3>
#include <MsgBoxConstants.au3>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; GIVEN DATA ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; File names
Global Const $File_1 = "Test.txt"
Global Const $File_2 = "Test2.txt"
Global Const $File_3 = "Test3.txt"
; Attribute names
Local Const $Attribute_1 = "Controller"
Local Const $Attribute_2 = "Simulator"
Local Const $Attribute_3 = "Testing"
; Files attributes
Global $File_1_Attr[3] = [$Attribute_1, $Attribute_2, $Attribute_3]
Global $File_2_Attr[3] = [$Attribute_3, "", ""]
Global $File_3_Attr[3] = ["", $Attribute_1, ""]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; GUI FORM EXAMPLE ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
$Form_Main = GUICreate("GUI managing Database", 250, 380)
$Group_Attributes = GUICtrlCreateGroup("Attributes", 20, 20, 200, 130)
$Checkbox_1 = GUICtrlCreateCheckbox($Attribute_1, 40, 50)
$Checkbox_2 = GUICtrlCreateCheckbox($Attribute_2, 40, 80)
$Checkbox_3 = GUICtrlCreateCheckbox($Attribute_3, 40, 110)
$Group_Files = GUICtrlCreateGroup("Files", 20, 160, 200, 200)
$Label_1 = GUICtrlCreateLabel("", 40, 180, 150, 20)
$Label_2 = GUICtrlCreateLabel("", 40, 220, 150, 20)
$Label_3 = GUICtrlCreateLabel("", 40, 260, 150, 20)
GUISetState(@SW_SHOW)
; Files variablies
Global $File_1_Content = ""
Global $File_2_Content = ""
Global $File_3_Content = ""
Global $File_1_Access = False
Global $File_2_Access = False
Global $File_3_Access = False
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; PROGRAM BODY ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; Files content read
    If Not File_read($File_1) Or Not File_read($File_2) Or Not File_read($File_3) Then
        Exit
    EndIf
; Endless loop
While 1
    ; Checkbox status changed
    $msg = GUIGetMsg()
    Switch $msg
        Case $Checkbox_1, $Checkbox_2, $Checkbox_3
            $File_1_Access = False
            $File_2_Access = False
            $File_3_Access = False
            Access($Checkbox_1)
            Access($Checkbox_2)
            Access($Checkbox_3)
            ; Files show
            If $File_1_Access Then
                GUICtrlSetData($Label_1, $File_1_Content)
            Else
                GUICtrlSetData($Label_1, "")    
            EndIf
            If $File_2_Access Then
                GUICtrlSetData($Label_2, $File_2_Content)
            Else
                GUICtrlSetData($Label_2, "")    
            EndIf
            If $File_3_Access Then
                GUICtrlSetData($Label_3, $File_3_Content)
            Else
                GUICtrlSetData($Label_3, "")    
            EndIf
        Case $GUI_EVENT_CLOSE ; Close GUI
            ExitLoop
    EndSwitch
WEnd
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; FUNCTIONS ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Func File_read($Filename)
    Local $hFileOpen = FileOpen($Filename, $FO_READ)
    If $hFileOpen = -1 Then
        MsgBox($MB_SYSTEMMODAL, "", "An error occurred when reading the file " & $Filename)
        Return False
    EndIf
    Switch $Filename
        Case $File_1
            $File_1_Content = FileRead($hFileOpen)
        Case $File_2
            $File_2_Content = FileRead($hFileOpen)
        Case $File_3
            $File_3_Content = FileRead($hFileOpen)
    EndSwitch
    FileClose($hFileOpen)
    Return True
EndFunc
;
Func Access($Checkbox)
    If GUICtrlRead($Checkbox) = $GUI_CHECKED Then
        For $vElement In $File_1_Attr
            If $vElement = GUICtrlRead($Checkbox, $GUI_READ_EXTENDED) Then
                $File_1_Access = True
            EndIF
        Next
        For $vElement In $File_2_Attr
            If $vElement = GUICtrlRead($Checkbox, $GUI_READ_EXTENDED) Then
                $File_2_Access = True
            EndIF
        Next
        For $vElement In $File_3_Attr
            If $vElement = GUICtrlRead($Checkbox, $GUI_READ_EXTENDED) Then
                $File_3_Access = True
            EndIF
        Next
    EndIF
EndFunc

 

[FrancescoDiMuro]

@price98

I don't see any code related to access a Database, so we can't help you

[price98]

hi,

I done upto here. Here I done for 3 files(static), If i have to do infinity number of files(dynamically). How to do?

And after clicking controller checkbox, How to list the files?

Can anyone help me?

Thanks

expandcollapse popup

#include <GUIConstantsEx.au3>
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; DB DATA ;;;;;;;;;;;;;;;;;;:::;;;;;;;;;;;;;;;;
Local $DB_Name = "Database4.accdb"
Local $Table_Name = "bhanu"
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; VARIABLES ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; File names
Global $Files[3] = ["", "", ""]
; Files attributes
Global $File_Attr[3][3]
; Attribute names
Local $Attr_Name[3] = ["", "", ""]
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; PROGRAM BODY ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
; DB read
$AdoCon = ObjCreate("ADODB.Connection")
$AdoCon.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $DB_Name)
For $i = 0 To 2 Step 1
    $AdoRs = ObjCreate("ADODB.Recordset")
    $AdoRs.CursorType = 1
    $AdoRs.LockType = 3
    $AdoRs.Open("SELECT * FROM " & $Table_Name & " WHERE ID = " & ($i + 1), $AdoCon)
    $Files[$i] = $AdoRs.Fields(1).Value
    $File_Attr[$i][0] = $AdoRs.Fields(2).Value
    $File_Attr[$i][1] = $AdoRs.Fields(3).Value
    $File_Attr[$i][2] = $AdoRs.Fields(4).Value
Next
$AdoCon.Close
; Attributes implode
Local $a = 0
For $i = 0 To 2 Step 1
    For $j = 0 To 2 Step 1
        If $a < 3 And Not $File_Attr[$i][$j] = "" Then
            For $k = $a To 2 Step 1
                If $Attr_Name[$k] = $File_Attr[$i][$j] Then
                    ContinueLoop 2
                EndIf
            Next
            $Attr_Name[$a] = $File_Attr[$i][$j]
            $a = $a + 1
        EndIf
    Next
Next
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; GUI FORM EXAMPLE ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
$Form_Main = GUICreate("GUI managing Database", 250, 380)
$Group_Attributes = GUICtrlCreateGroup("Attributes", 20, 20, 200, 130)
$Checkbox_1 = GUICtrlCreateCheckbox($Attr_Name[0], 40, 50)
$Checkbox_2 = GUICtrlCreateCheckbox($Attr_Name[1], 40, 80)
$Checkbox_3 = GUICtrlCreateCheckbox($Attr_Name[2], 40, 110)
$Group_Files = GUICtrlCreateGroup("Files", 20, 160, 200, 200)
$Label_1 = GUICtrlCreateLabel("", 40, 180, 100, 20)
$Label_2 = GUICtrlCreateLabel("", 40, 220, 100, 20)
$Label_3 = GUICtrlCreateLabel("", 40, 260, 100, 20)
GUISetState(@SW_SHOW)
; GUI loop
While 1
    $msg = GUIGetMsg()
    Switch $msg
        Case $Checkbox_1, $Checkbox_2, $Checkbox_3
            GUICtrlSetData($Label_1, "")
            GUICtrlSetData($Label_2, "")
            GUICtrlSetData($Label_3, "")
            Access($Checkbox_1)
            Access($Checkbox_2)
            Access($Checkbox_3)
        Case $GUI_EVENT_CLOSE ; Close GUI
            ExitLoop
    EndSwitch
WEnd
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; FUNCTION ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
Func Access($Checkbox)
    If GUICtrlRead($Checkbox) = $GUI_CHECKED Then
        Local $Chkbox_label = GUICtrlRead($Checkbox, 1)
        If $File_Attr[0][0] = $Chkbox_label Or $File_Attr[0][1] = $Chkbox_label Or $File_Attr[0][2] = $Chkbox_label Then
            GUICtrlSetData($Label_1, $Files[0])
        EndIF
        If $File_Attr[1][0] = $Chkbox_label Or $File_Attr[1][1] = $Chkbox_label Or $File_Attr[1][2] = $Chkbox_label Then
            GUICtrlSetData($Label_2, $Files[1])
        EndIF
        If $File_Attr[2][0] = $Chkbox_label Or $File_Attr[2][1] = $Chkbox_label Or $File_Attr[2][2] = $Chkbox_label Then
            GUICtrlSetData($Label_3, $Files[2])
        EndIF
    EndIF
EndFunc

 

Database4.accdb

[FrancescoDiMuro]

On 1/10/2018 at 1:19 PM, price98 said:

If i have to do infinity number of files(dynamically). How to do?

_FileListToArray() or _FileListToArrayRec().

On 1/10/2018 at 1:19 PM, price98 said:

And after clicking controller checkbox, How to list the files?

Check the state of the checkbox, and do an action only if it is $GUI_CHECKED; there are a tons of examples on the Forum

[price98]

thanks for your reply