Sign in to follow this  
Followers 0
Will66

Acess db viewer

4 posts in this topic

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...

#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

Share this post


Link to post
Share on other sites



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

#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

Share this post


Link to post
Share on other sites

Looks good and I can probably use it.

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


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

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  
Followers 0