Acess db viewer

[Will66]

put together a simple access dbviewer - ie, no table reationships and limited error handling.

Basically, click file open to open an msaccess db and view its tables, click a table to view its records...

expandcollapse popup

#include <GUIConstants.au3>
#include <GuiList.au3>
#Include <GuiListView.au3>

opt ("MustDeclareVars", 1)
Dim $gui = GUICreate("DB Viewer", 800, 600)
Dim $filemenu = GUICtrlCreateMenu ("&File")
Dim $fileitem = GUICtrlCreateMenuitem ("Open",$filemenu)
Dim $tablesLabel, $tablesButton, $fieldsList
Global $tablesList
Global $objConn
Dim $file , $msg

GUICtrlSetLimit(-1,200)
Func DB_Open($file)
If Not $tablesLabel Then $tablesLabel=GUICtrlCreateLabel  ("Tables", 10,10)
If Not $tablesList Then
    $tablesList=GUICtrlCreateList ("", -1,25,120 ,97)
    Else
    _GUICtrlListClear($tablesList)
    EndIf
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $file & ";")
Dim $rsTables = $objConn.OpenSchema(20)
With $rsTables
    While Not .EOF
    If .Fields("TABLE_TYPE").Value == "TABLE" Then    GUICtrlSetData($tablesList,.Fields("TABLE_NAME").Value)
       .MoveNext
    WEnd
    .Close
EndWith
$objConn.Close
;If Not $tablesButton then $tablesButton        = GUICtrlCreateButton("View", 10, 125, 50)
EndFunc

Func Table_Open($file, $menustate)
Dim $headerFields
Dim $reclist
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $file & ";")
Dim $rsFields = $objConn.Execute("SELECT * FROM " & $menustate)
Dim $i
For $i = 0 to $rsFields.Fields.count - 1
            $headerFields&=$rsFields.fields($i).name & "|"
Next        
If $fieldsList then
    _GUICtrlListViewDeleteAllItems ($fieldsList)
    GUICtrlDelete($fieldsList)
EndIf    
$fieldsList=GUICtrlCreateListView ($headerFields, 170,25,600 ,250)
    While Not $rsFields.EOF
        $reclist=""
    For $i = 0 to $rsFields.Fields.count - 1
            $reclist&=$rsFields.fields($i).value & "|"
            Next
        GUICtrlCreateListViewItem($reclist, $fieldsList)
        $rsFields.MoveNext
    WEnd    
$rsFields.Close
$objConn.Close
EndFunc

GUISetState ()

Do
    $msg = GUIGetMsg()
        if $msg == $tablesList Then
        Dim $menustate    = GUICtrlRead($tablesList)
        If $menustate Then
        Dim $menutext    = GUICtrlRead($tablesList, 1)
        Table_Open($file, $menustate)
        EndIf
    EndIf
;if $msg == $tablesList then MsgBox(0,"listview", "clicked="& GUICtrlRead($tablesList, 1))
    If $msg = $fileitem Then
        $file = FileOpenDialog("Choose file...",@ScriptDir,"Microsoft Acces Databases (*.mdb)")
        If @error <> 1 Then DB_Open($file)
            EndIf
Until $msg = $GUI_EVENT_CLOSE

[Zedna]

Looks very nice. I like it.

I will test it later.

[Will66]

cheers, this examples very similar to the first but also includes sortable columns by asc/desc, by clicking the field names in column header.

expandcollapse popup

#include <GUIConstants.au3>
#include <GuiList.au3>
#Include <GuiListView.au3>

opt ("MustDeclareVars", 1)
Dim $gui = GUICreate("DB Viewer", 800, 600)
Dim $filemenu = GUICtrlCreateMenu ("&File")
Dim $fileitem = GUICtrlCreateMenuitem ("Open",$filemenu)
Dim $tablesLabel, $tablesButton, $fieldsList
Global $tablesList
Global $objConn
Dim $file , $msg,$sortCols
Dim $rsFields 
Dim $headerFields
Dim $direction

GUICtrlSetLimit(-1,200)
Func DB_Open($file)
If Not $tablesLabel Then $tablesLabel=GUICtrlCreateLabel  ("Tables", 10,10)
If Not $tablesList Then 
    $tablesList=GUICtrlCreateList ("", -1,25,120 ,97)
    Else
    _GUICtrlListClear($tablesList)
    EndIf
$objConn = ObjCreate("ADODB.Connection")
$objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $file & ";")
 Dim $rsTables = $objConn.OpenSchema(20)
With $rsTables
    While Not .EOF
    If .Fields("TABLE_TYPE").Value == "TABLE" Then  GUICtrlSetData($tablesList,.Fields("TABLE_NAME").Value)
       .MoveNext
    WEnd
    .Close
EndWith
$objConn.Close
;If Not $tablesButton then $tablesButton        = GUICtrlCreateButton("View", 10, 125, 50)
EndFunc

Func Table_Open($file, $menustate,$sortCols)
$headerFields=""    
Dim $reclist
Dim $sortDir
If $sortCols Then 
    If $direction = "Asc" then
        $direction = "Desc"
    Else
        $direction = "ASC"
    EndIf
    
    $sortDir=" ORDER BY " & $sortCols & " " & $direction    
EndIf


$objConn = ObjCreate("ADODB.Connection")

$objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $file & ";")
;MsgBox(0,"sql","SELECT * FROM " & $menustate & $sortDir)

$rsFields = $objConn.Execute("SELECT * FROM " & $menustate & $sortDir & ";")

Dim $i
For $i = 0 to $rsFields.Fields.count - 1
            $headerFields&=$rsFields.fields($i).name & "|"
Next        
If $fieldsList then 
    _GUICtrlListViewDeleteAllItems ($fieldsList)
    GUICtrlDelete($fieldsList)
EndIf   
$fieldsList=GUICtrlCreateListView ($headerFields, 170,25,600 ,250,-1,$LVS_EX_GRIDLINES )
    While Not $rsFields.EOF
        $reclist=""
    For $i = 0 to $rsFields.Fields.count - 1
            $reclist&=$rsFields.fields($i).value & "|"
            Next
        GUICtrlCreateListViewItem($reclist, $fieldsList)
        $rsFields.MoveNext
    WEnd    
$rsFields.Close
$objConn.Close
EndFunc

GUISetState ()

Do
    $msg = GUIGetMsg()
        if $msg == $tablesList Then
        Dim $menustate  = GUICtrlRead($tablesList) 
        If $menustate Then
        Dim $menutext   = GUICtrlRead($tablesList, 1) 
        $sortCols=""
        $direction="Asc"
        Table_Open($file, $menustate,"")
        EndIf
    EndIf
    ;if $msg == $tablesList then MsgBox(0,"listview", "clicked="& GUICtrlRead($tablesList, 1))
    If $msg = $fileitem Then
        $file = FileOpenDialog("Choose file...",@ScriptDir,"Microsoft Acces Databases (*.mdb)")
        If @error <> 1 Then DB_Open($file)
        EndIf
        
        If $msg == $fieldsList Then
        ;MsgBox(0,"listview", GUICtrlGetState($fieldsList))
        Dim $array = StringSplit($headerFields, "|")
        ;MsgBox(0,"test",$array[GUICtrlGetState($fieldsList)+1])
        
        ;MsgBox(0,"array",$array(GUICtrlGetState($fieldsList)))
        ;MsgBox(0,"listview2", $fieldsList[GUICtrlGetState($fieldsList)].value)
        Table_Open($file, $menustate,$array[GUICtrlGetState($fieldsList)+1])
        EndIf
Until $msg = $GUI_EVENT_CLOSE

[GEOSoft]

Looks good and I can probably use it.

You might want to take a look at my Access UDF.