Need help with access database query and print to text file

enRAYYY · March 17, 2016

Hi, I've recently been trying to get my head around a solution for a script that will search a .mdb file for a specific table called "REPAIR_DATA" search for a column named "KEY" and a specific value in that column and then copy the values of specific columns that line up with the row of the specified value for the "KEY" column. To explain this further i've attached a screenshot of an example access database.

For example I would like to query the data base for the KEY "5" and return the values for columns "DATA1" and "DATA3"

I would then like to output this to a text file so that the first line of the text file contains the value of "KEY,5" for "DATA1" and line 2 contains the value of "KEY,5" "DATA3"

As per the included picture the text file would contain the following data:

;Start of text file

2

4

;End of file

I've gotten my head around most of the basic autoit syntax/functions however I'm really struggling with this one. Any and all help would be appreciated.
Sidenote: Going through alot of personal issues atm and sleep is not something I have been getting alot of, If my question is too confusing I'll try to explain it a little better after a good nights sleep

exp.PNG.06b59e60a36024878e9162126e299b6c

enRAYYY · March 18, 2016

Bump, any and all help is appreciated.

spudw2k · March 18, 2016

You example is a little confusing to me. Shouldn't you be referring to the columns instead of having a row with headers?
Anyways--as a starting point--here is an example I pieced together from a UDF I made a while back which queries in the fashion you want, but refers to the column names.

The example creates a TempDB.MDB file (in the script dir), inserts test data, queries data, deletes TempDB.

#AutoIt3Wrapper_UseX64=n

#Region - Constants and Variables
Global $objConnection = ObjCreate("ADODB.Connection")
Global $objRecordSet = ObjCreate("ADODB.Recordset")
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")
Global $adCurrentProvider = 0
Global $adCurrentDataSource = 0

Const $adOpenForwardOnly = 0
Const $adOpenKeyset = 1
Const $adOpenDynamic = 2
Const $adOpenStatic = 3
Const $adLockReadOnly = 1
Const $adLockPessimistic = 2
Const $adLockOptimistic = 3
Const $adLockBatchOptimistic = 4
Const $adProviderSQLOLEDB = "SQLOLEDB"
Const $adProviderMSJET4 = "Microsoft.Jet.OLEDB.4.0"
Const $adProviderMSJET12 = "Microsoft.ACE.OLEDB.12.0"
#EndRegion


#include <Array.au3>   ;Only needed for This Demo Script

$adCurrentProvider = $adProviderMSJET4   ;Microsoft.Jet.OLEDB.4.0
$adCurrentDataSource = @ScriptDir & "\TEMPDB.MDB"

;Establish ADO Connection
_OpenConnection($adCurrentProvider,$adCurrentDataSource)

;Create Table
Dim $arrFields[4]=["[KEY] INTEGER","[DATA1] INTEGER","[DATA2] INTEGER","[DATA3] INTEGER"]  ;Init Fields for Table Creation
_CreateTable("REPAIR_DATA",$arrFields)

;Insert Records
Dim $arrFieldsandValues[9][4]=[["[KEY]","[DATA1]","[DATA2]","[DATA3]"],[1,1,1,1],[2,1,1,1],[3,1,1,1],[4,1,1,1],[5,2,3,4],[6,1,1,1],[7,1,1,1],[8,1,1,1]]   ;Init Fields and Values for Insert
_InsertRecords("REPAIR_DATA",$arrFieldsandValues)

;Retrieve Records from Table with Where Clause
Dim $arrSelectFields[4]=["KEY","DATA1","DATA2","DATA3"]   ;Init Select Fields for Recordset
Dim $arrWhereFields[1]=["[KEY] = 5"]
$arrRecords = _GetRecords("REPAIR_DATA",$arrSelectFields,$arrWhereFields)
_ArrayDisplay($arrRecords)

;Drop Database
_DropDatabase()   ;Closes ADO Connection first if using MS.JET.OLEDB.4.0



#Region - Functions, Subs, Methods
Func _AddColumn($varTableName,$varColumnName,$varColumnType)
    If Not IsObj($objConnection) Then Return -1
    $strAddCol = "ALTER TABLE " & $varTableName & " ADD " & $varColumnName & " " & $varColumnType
    Return $objConnection.Execute($strAddCol)
EndFunc

Func _CloseConnection()
    Return $objConnection.Close
EndFunc

Func _CloseRecordSet()
    Return $objRecordSet.Close
EndFunc

Func _CreateDatabase($varDatabaseName=0)
    If $adCurrentProvider = $adProviderMSJET4 Then
        $objADOXCatalog = ObjCreate("ADOX.Catalog")
        $strProvider = "Provider=" & $adCurrentProvider & ";Data Source=" & $adCurrentDataSource
        $objADOXCatalog.Create($strProvider)
        $objADOXCatalog = 0
        Return 0
    ElseIf $varDatabaseName Then
        $objConnection.Execute("CREATE DATABASE " & $varDatabaseName)
        Return $objConnection.Execute("USE " & $varDatabaseName)
    EndIf
    Return 0
EndFunc

Func _CreateTable($varTableName,$arrFields)
    If Not IsObj($objConnection) Then Return -1
    If Not IsString($varTableName) Then Return -2
    If Not IsArray($arrFields) Then Return -3
    $varFields = ""
    $varFieldCount = Ubound($arrFields)-1
    For $x = 0 to $varFieldCount
        $varFields &= $arrFields[$x]
        If $x < $varFieldCount Then $varFields &= " ,"
    Next
    Return $objConnection.Execute("CREATE TABLE " & $varTableName & "(" & $varFields & ")")
EndFunc

Func _DropColumn($varTableName,$varColumnName)
    If Not IsObj($objConnection) Then Return -1
    $strDropCol = "ALTER TABLE " & $varTableName & " DROP COLUMN " & $varColumnName
    Return $objConnection.Execute($strDropCol)
EndFunc

Func _DropDatabase($varDatabaseName=0)
    If Not IsObj($objConnection) Then Return -1
    If $adCurrentProvider = $adProviderMSJET4 Then
        _CloseConnection()
        If MsgBox(4+16,"Are you sure?","Are you sure you want to delete" & @CRLF & $adCurrentDataSource & " ?" & @CRLF & @CRLF & "This Cannot Be Undone!") = 6 Then
            Return FileDelete($adCurrentDataSource)
        EndIf
    Else
        $objConnection.Execute("USE master")
        Return $objConnection.Execute("DROP DATABASE " & $varDatabaseName)
    EndIf
EndFunc

Func _DropTable($varTableName)
    If Not IsObj($objConnection) Then Return -1
    Return $objConnection.Execute("DROP TABLE " & $varTableName)
EndFunc

Func _GetRecords($varTable,$arrSelectFields,$arrWhereFields=0)
    If Not IsObj($objConnection) Then Return -1
    If Not IsObj($objRecordSet) Then Return -2
    _OpenRecordset($varTable,$arrSelectFields,$arrWhereFields)
    If Not $objRecordSet.RecordCount Or ($objRecordSet.EOF = True) Then Return -3
;   Dim $arrRecords[1][1]
;   $objRecordSet.MoveFirst
;   $x = 0
;   For $objField in $objRecordSet.Fields
;       ReDim $arrRecords[1][$x+1]
;       $arrRecords[0][$x]=$objField.Name
;       $x+=1
;   Next
;   Do
;       ReDim $arrRecords[UBound($arrRecords)+1][$x]
;       $x = 0
;       For $objField in $objRecordSet.Fields
;           $arrRecords[UBound($arrRecords)-1][$x] = $objField.Value
;           $x+=1
;       Next
;       $objRecordSet.MoveNext
;   Until $objRecordSet.EOF
    Dim $arrRecords
    $arrRecords = $objRecordSet.GetRows()
    _CloseRecordSet()
    Return $arrRecords
EndFunc

Func _GetTablesAndColumns($varSystemTables=0)
    If Not IsObj($objConnection) Then Return -1
    $objADOXCatalog = ObjCreate("ADOX.Catalog")
    $objADOXCatalog.ActiveConnection = $objConnection
    Dim $arrTables[1][2]=[['Table Name','Columns Array']]
    Dim $arrColumns[1][2]=[['Column Name','Column Type']]
    For $objTable In $objADOXCatalog.Tables
        Local $varSkipTable = 0
        If Not $varSystemTables Then
            If StringInstr($objTable.Type,"SYSTEM") or StringInstr($objTable.Name,"MSys")=1 Then $varSkipTable = 1
        EndIf
        If Not $varSkipTable Then
            ReDim $arrTables[UBound($arrTables)+1][2]
            $arrTables[UBound($arrTables)-1][0] = $objTable.Name
            ReDim $arrColumns[1][2]
            For $objColumn in $objTable.Columns
                ReDim $arrColumns[UBound($arrColumns)+1][2]
                $arrColumns[UBound($arrColumns)-1][0] = $objColumn.Name
                $arrColumns[UBound($arrColumns)-1][1] = $objColumn.Type
            Next
            $arrTables[UBound($arrTables)-1][1] = $arrColumns
        EndIf
    Next
    $objADOXCatalog = 0
    Return $arrTables
EndFunc

Func _InsertRecords($varTableName,$arrFieldsAndValues)
    If Not IsObj($objConnection) Then Return -1
    If Not IsArray($arrFieldsAndValues) Then Return -2
    For $y = 1 To UBound($arrFieldsAndValues)-1
        $strInsert = "INSERT INTO " & $varTableName & " ("
        $varFieldCount = UBound($arrFieldsAndValues,2)-1
        For $x = 0 To $varFieldCount
            $strInsert &= $arrFieldsAndValues[0][$x]
            If $x < $varFieldCount Then $strInsert &= ","
        Next
        $strInsert &= ") VALUES ("
        For $x = 0 To $varFieldCount
            $strInsert &= "'" & $arrFieldsAndValues[$y][$x] & "'"
            If $x < $varFieldCount Then $strInsert &= ","
        Next
        $strInsert &= ");"
        $objConnection.Execute($strInsert)
        If $errADODB.number Then
            If Msgbox(4+16+256,"Insert Record Error","Statement failed:" & @CRLF & $strInsert & @CRLF & @CRLF & "Would you like to continue?") <> 6 Then Return -3
        EndIf
    Next
    Return 1
EndFunc

Func _OpenConnection($varProvider,$varDataSource,$varTrusted=0,$varInitalCatalog="",$varUser="",$varPass="")
    If Not IsObj($objConnection) Then Return -1
    $adCurrentDataSource = $varDataSource
    $adCurrentProvider = $varProvider
    If $adCurrentProvider = $adProviderMSJET4 Then
        If Not FileExists($adCurrentDataSource) Then
            If MsgBox(4+16,$adCurrentDataSource & " does not exist.","Would you like to attempt" & @CRLF & "to create it?") = 6 Then
                _CreateDatabase()
            Else
                Return 0
            EndIf
        EndIf
    EndIf
    $strConnect = "Provider=" & $adCurrentProvider & ";Data Source=" & $adCurrentDataSource & ";"
    If $varTrusted Then $strConnect &= "Trusted_Connection=Yes;"
    If $varUser Then $strConnect &= "User ID=" & $varUser & ";"
    If $varPass Then $strConnect &= "Password=" & $varPass & ";"
    $objConnection.Open($strConnect)
    If $varInitalCatalog Then
        Return $objConnection.Execute("USE " & $varInitalCatalog)
    Else
        Return 1
    EndIf
EndFunc

Func _OpenRecordset($varTable,$arrSelectFields,$arrWhereFields=0,$varCursorType=$adOpenForwardOnly,$varLockType=$adLockReadOnly)
    If Not IsObj($objConnection) Then Return -1
    If Not IsObj($objRecordSet) Then Return -2
    $strOpen = "SELECT "
    $varFieldCount = UBound($arrSelectFields)-1
    For $x = 0 to $varFieldCount
        $strOpen &= "[" & $arrSelectFields[$x] & "]"
        If $x < $varFieldCount Then $strOpen &= ", "
    Next
    $strOpen &= " FROM " & $varTable
    If IsArray($arrWhereFields) Then
        $strOpen &= " WHERE "
        $varFieldCount = UBound($arrWhereFields)-1
        For $x = 0 to $varFieldCount
            $strOpen &= $arrWhereFields[$x]
            If $x < $varFieldCount Then $strOpen &= ", "
        Next
    EndIf
msgbox(0,"Get Records",$strOpen)    
    Return $objRecordSet.Open($strOpen,$objConnection,$varCursorType,$varLockType)
EndFunc

Func _ErrADODB()
    Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
             "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
            )
    Local $err = $errADODB.number
    If $err = 0 Then $err = -1
EndFunc
#EndRegion

Edited March 19, 2016 by spudw2k

enRAYYY · March 19, 2016

Will have a fiddle with that example code and see what I can make from it. Thank you for your help.

To explain my first post a little better (Have had slightly more sleep) Without actually creating the db I wasn't able to change the column names in the screenshot so the DATA1, DATA2, DATA3 were substitutes for the column names.

A better explanation would be: I would like to select the row where the value for column key is "5" and then choose which columns from that same row to save data to the textfields.

E.g I would like to query the db column"KEY" for the value "5" and save columns DATA1 and DATA3 from that row to the text file.

spudw2k · March 19, 2016

Perfect. I modified my example to reflect the field names you want (KEY, DATA1, DATA2, DATA3). I also added a msgbox popup to show the SELECT statement being used against the MDB. The result(s) from a query are returned as an array. You could then pick out what you want from the array or optimize the results array and write to a file. Are you familiar with using arrays?

Also, I just threw the UDF into and example in one script. If you visit the link I posted to the old UDF you can see when they are separated the usage is a little clearer. There are also clearer example comments. I may spend some time cleaning up the UDF and bringing it up to my current scripting standards.

Edited March 19, 2016 by spudw2k

enRAYYY · March 20, 2016

Not familiar entirely with arrays but life is a learning curve and knowledge doesn't hurt to have. Will look into writing the array to a text file. Thank you for responses they are greatly appreciated spud.

enRAYYY · March 20, 2016

Was able to tweak your example into an almost fully functional script for what I needed, Just having issues writing the array to a file.
I added the include for file.au3 however placing the line "_FileWriteFromArray($sFilePath, $arrRecords, 1)" after the line "_ArrayDisplay($arrRecords)" is not writing to/creating a file. The array does however display correctly beforehand. In the long run I'm looking to split each columns field into a separate line in the text file any ideas?

Edited March 20, 2016 by enRAYYY

spudw2k · March 21, 2016

The last parameter you are setting in the _FileWriteFromArray function (1) is telling the function to start at the second row of the array. I know that seems counter intuitive, but arrays are (what is called) zero-based, which means the first Array element has an index of 0.

If the array had more than one record in it, the _FileWrite function would've continued to write each record...but sine there was only one record returned you we're essentially "skipping" it.

Remove the last param and it should work.

_FileWriteFromArray($sFilePath, $arrRecords)

If you are finding that the output of the array isn't what you want I can point you in the right direction to learn about using arrays.

enRAYYY · March 22, 2016

You are a gentlemen and a scholar.
With the array written to the text file I can go back to mainly using autoit for window automation (Found that super easy).
I'm really appreciative for your help, I'm honestly keen to get you a $10 giftcard for something (not much but im broke) Is there anything like steam/Playstore/Playstation/Xbox that I could get you a $10 giftcard for for your help? It's been awesome.

Also will look into arrays further as it can't hurt to know how to manipulate them better even though i don't currently need to, is there any links you could provide for that that you would recommend, haven't found too much useful info thats not already in the autoit help file on google.

spudw2k · March 22, 2016

Don't worry 'bout it...it's a nice gesture, but I'm happy to help. Treat yourself to a charleston chew and chill it in the freezer.
This wiki article has some good info on arrays and how they can/are used in AutoIt.

enRAYYY · April 1, 2016

Thanks again for all your help spud, I've been racking my brain about this for a couple of hours over the past few nights, so in the array I have multiple columns from a single row of the open table, I would like to write each columns field of said row to separate lines of a text file so the first columns field is the first line, the second columns filed is the second line ect. Is there any chance you'd be able to help me out with this one?

The array already contains all the information I need, i'd just like to separate each column to a separate line in the text file.

jchd · April 1, 2016

_ArrayToString is your friend here.

enRAYYY · April 1, 2016

Ahhhhh appears I was looking too hard in the wrong places. That easily got me going.

$arrArray = _ArrayToString($arrRecords, @CRLF)
FileWrite(@ScriptDir & "Output.txt", $arrArray)

Thank you.

Edited April 1, 2016 by enRAYYY

Need help with access database query and print to text file

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members