Search the Community

Hi database and SQL masters, This is my first time dealing with .mdb files and Arrays and I have a few questions. I just need to change one field in a databases of unknow # of records. The script runs on different PC's. Each PC has a database with the same structure just more or less records. Let me explain what I want to do: 1: Open .mdb 2: Get the number of records 3: Start with 1st record 4: Check DBfield "fldQSLConfByS" if Letter "Q" is in the field 5: If Letter exists goto next record 6: If not, add Letter Q to existing entry and write it back to "fldQSLConfBYs' field in DB record Put complete record (all fields) into a workable string (I think ArraytoString does that) for further processing or even better into $fieldsxy[xy] [xy] 7: next record until all records are processed. I found ADO.au3 UDF, which is over my head and an overkill to what I need to do, I think. Then I stumbled across Access.au3.I modified the example.au3 and can read the DB, display the fields etc. but I can not write back to the DB. Always get: "Error in writting Data" See Section: ; ******************************************************* ; 10 - Edit/change Record ; ******************************************************* No idea why I can not write the data back. Playing with it for a week now and can not figure it out. Is there an easier way to just edit/change one DBfield? Oh.. and how do I display or read an array value? ( $datafieldxy = $array[xy][xy] ) Is _ArrayToString($avArray_Record, "", 48,48) the only way? (48, number of field) Attached are 3 files: Access.au3 - the UDF ~~ HAMLog_AccessDB.au3 - Scripy ~~ testdb.mdb - Database Thanks for any help HAMLog_AccessDB.au3 testdb.mdb Access.au3

Hi Guys, Fine? I have this code and I use it to perform the query, however when I change the query to INSERT it is not working return error. #include <GUIConstants.au3> #include <MsgBoxConstants.au3> #include <Array.au3> Global $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") Example() Func Example()     Local $dbname = FileOpenDialog("Choose Access Database", @ScriptDir, "Access files (*.accdb)", 1)     If @error then Return SetError(@error, @extended, 0)     $adoCon = ObjCreate("ADODB.Connection")     $adoCon.Open("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=" & $dbname & ";Uid=;Pwd=;")     $adoRs = ObjCreate("ADODB.Recordset")     GUICreate("listview items", 550, 250, 100, 200, -1, $WS_EX_ACCEPTFILES)     Local $idListview = GUICtrlCreateListView("Codigo    |Nome         |Valor  ", 10, 10, 520, 150) ;,$LVS_SORTDESCENDING     $queryInsert = INSERT INTO TABLENAME VALUES (''aaaaa'', ''bbbbbb'', ''cccccc'')     Local $aResult     With $adoRs         .CursorType = 2         .LockType = 3         .Open($queryInsert, $adoCon)         If @error Then             ; deal with Probable SQL error             Return SetError(1)         EndIf         If Not .EOF Then $aResult = .GetRows()         .Close()     EndWith     $adoRs = 0     _ArrayDisplay($aResult, 'UBound($aResult)=' & UBound($aResult))     For $iRow_idx = 0 To UBound($aResult) - 1         GUICtrlCreateListViewItem($aResult[$iRow_idx][0], $idListview)     Next     $adoCon.Close     GUISetState()     ; Loop until the user exits.     While 1         Switch GUIGetMsg()             Case $GUI_EVENT_CLOSE                 ExitLoop ;~             Case $idButton ;~                 MsgBox($MB_SYSTEMMODAL, "listview item", GUICtrlRead(GUICtrlRead($idListview)), 2)             Case $idListview                 MsgBox($MB_SYSTEMMODAL, "listview", "clicked=" & GUICtrlGetState($idListview), 2)         EndSwitch     WEnd EndFunc   ;==>Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError)     ; Do anything here.     ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _             @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _             @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _             @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _             @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _             @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _             @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _             @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _             @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _             @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc   ;==>_ErrFunc Help, Please!

A one user ask about Creating XLS file with ADO. This was interesting to me so I use uncle google and find this: https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928 EDIT: https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx How you can read in this mentioned above link, This is not posible with ADO but quite easy with ADOX. Here is AutoIt example: ;~ https://forums.autodesk.com/t5/visual-basic-customization/ado-connection-create-file-excel/td-p/1675928 ;~ https://msdn.microsoft.com/en-us/library/ms675849(v=vs.85).aspx #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7 Global Const $ADO_adInteger = 3 Global Const $ADO_adVarWChar = 202 #include <Array.au3> #include <MsgBoxConstants.au3> _Example() Func _Example() ; Error monitoring. This will trap all COM errors while alive. ; This particular object is declared as local, meaning after the function returns it will not exist. Local $oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc") #forceref $oErrorHandler Local $oConnection = ObjCreate('ADODB.Connection') Local $oCatalog = ObjCreate('ADOX.Catalog') Local $oTable = ObjCreate('ADOX.Table') ; Local $oColumn = ObjCreate('ADOX.Column') $oConnection.open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & @ScriptDir & '\ADOX_EXAMPLE__MyContacts.xls' & ";Extended Properties=Excel 8.0") $oCatalog.ActiveConnection = $oConnection With $oTable .Name = "MyContacts" .ParentCatalog = $oCatalog ; Create fields and append them to the new Table object. .Columns.Append("ContactId", $ADO_adInteger) .Columns.Append("CustomerID", $ADO_adVarWChar, 16) ; Make the ContactId column and auto incrementing column .Columns("ContactId").Properties("AutoIncrement") = True ; Create fields and append them to the new Table object. .Columns.Append("FirstName", $ADO_adVarWChar, 80) .Columns.Append("LastName", $ADO_adVarWChar, 80) .Columns.Append("Phone", $ADO_adVarWChar, 20) .Columns.Append("Notes", $ADO_adVarWChar) EndWith $oCatalog.Tables.Append($oTable) ;Clean up $oConnection.Close $oConnection = Null ; $oColumn = Null $oTable = Null $oCatalog = Null EndFunc ;==>_Example ; User's COM error function. Will be called if COM error occurs Func _ErrFunc($oError) ; Do anything here. ConsoleWrite(@ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & @CRLF & _ @TAB & "err.number is: " & @TAB & @TAB & "0x" & Hex($oError.number) & @CRLF & _ @TAB & "err.windescription:" & @TAB & $oError.windescription & @CRLF & _ @TAB & "err.description is: " & @TAB & $oError.description & @CRLF & _ @TAB & "err.source is: " & @TAB & @TAB & $oError.source & @CRLF & _ @TAB & "err.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _ @TAB & "err.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF & _ @TAB & "err.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _ @TAB & "err.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _ @TAB & "err.retcode is: " & @TAB & "0x" & Hex($oError.retcode) & @CRLF & @CRLF) EndFunc ;==>_ErrFunc Have fun, mLipok