QDBCquery application

[HansH]

Small application in autoit for doing QDBC queries

Changes:

- menu functions are now all functioning

- changed variablenames and added more comments

- added cliboard and save to file for results.

- window resizing now corrected, final bug will be fixed in 3.1.1.71

Todo:

- further improve catalog layout.

You can download the latest version here : http://www.autoitscript.com/fileman/users/HansH/ODBCquery.au3

Have fun

Hans

[awrog]

Hans,

This version works really perfect!

Thank you for your effort and keep up the good work!

Arno Rog

[steve8tch]

Would it take alot of changes to have an option to point the script to an Access mdb file and query the mdb file directly without setting up an ODBC connection?

[Gigglestick]

Would it take alot of changes to have an option to point the script to an Access mdb file and query the mdb file directly without setting up an ODBC connection?

The queries work the same way, you just have to access the file instead of a DSN using a different connection string. See ConnectionStrings.com.

[steve8tch]

Thanks c0deWorm - what a useful site.

I will have a play this evening

[tkrampe]

That is my Problem, what is my error?

[spanga]

I thought this script was great! Too bad it doesn't work with the latest beta

[SmOke_N]

I thought this script was great! Too bad it doesn't work with the latest beta

Latest Beta? I didn't know there was a Beta, there's the 3.2.2.0 release version I believe.

[Will66]

Hi, looks interesting, unfortunately its not working for me using latest distribution 3.2.2

Ran a syntax check in Scite which returned a few line feed errors but still nothings happening.

It would be nice to see a short description of what this script does or requires, i'm new here but have downloaded a few scripts to find out what they do because of insufficient description, quite a few times i've had to search for an include file like excelcom.au3 and in one case found all the required files then lol : msgbox "Sorry this does'nt work for office 2000, bad luck"

Ok, now thats off my chest, thanx to the creators and contributors of autoit for a great product!

I've been playing around with it for couple of days now and finding it more and more useful.

I'm a begginer obviously but 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

Edited January 7, 2007 by Will66

[dragonlord]

the code hangs up for me at the call for ODBCsources() . any ideas why ?

[Wayada]

the code hangs up for me at the call for ODBCsources() . any ideas why ?

Try replacing the ODBCsources function with this one:

Func ODBCsources($h_controlID, $s_RegEntry, $s_Type)
    Local $s_List, $i_dsncount, $s_VarNm, $s_Value, $i
    $i = 1
    $s_VarNm = RegEnumVal($s_RegEntry,  $i)
    $s_Value = RegRead($s_RegEntry, $s_Varnm)
    While $s_VarNm <> ("No more data is available." & @CRLF)
        $s_VarNm = RegEnumVal($s_RegEntry,  $i)
        $s_Value = RegRead($s_RegEntry, $s_Varnm)
        If $s_Varnm <> ("No more data is available." & @CRLF) Then
            GUICtrlCreateListViewItem($s_VarNm & "|" & $s_Type & "|" & $s_Value, $h_controlID)
            $i += 1
            $i_DSNcount += 1
        EndIf
    Wend
EndFunc

[boomingranny]

is anyone else getting "404: The page/file you requested could not be found." when attempting to download this?

[tweast]

Same problem here. Looks like the user file areas have disappeared. Hope it's temporary.

update: Found code in this thread - http://www.autoitscript.com/forum/index.php?showtopic=25549

is anyone else getting "404: The page/file you requested could not be found." when attempting to download this?

Edited August 1, 2007 by tweast

[Zedna]

is anyone else getting "404: The page/file you requested could not be found." when attempting to download this?

Link from first post is to Autoit fileman area.

Autoit fileman area has been corrupted and now recovered for users who made request.

Look here for details.