Jump to content
KF5WGB

.mdb Can not write field back to record

Recommended Posts

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

Share this post


Link to post
Share on other sites
Posted (edited)

Have you thought about using Excel to modify your .mdb database? 

Originally I suggested using Excel to modify the mdb.  Although, Excel will read  the file, saving it back out as an .mbd file is not very straight forward and is probably not worth the effort.  However, you can use Excel to export the database as a CSV or other character-delimited text file.  And if you want to automate that process, you can use the Excel UDF.

Edited by TheXman

Share this post


Link to post
Share on other sites
Posted (edited)
56 minutes ago, TheXman said:

Have you thought about using Excel to modify your .mdb database?  You can also use Excel to export the database as a CSV or other character-delimited text file.  And if you want to automate the process, you can use the Excel UDF.

TheXman,

The finished Script/EXE runs on different PC's... I write this script for HAM operators all over the world to Upload/ sync their database/logbook with an online service.
It needs to be a standalone thing. Once the upload is done, the "Q" letter is added to field 48. Upload part is already finished. The next time the program runs it "sees" which record has been uploaded and which one still needs to be send.

Thanks for looking into it. I check out Excel UDF and see if I can do it that way,

Edited by KF5WGB

Share this post


Link to post
Share on other sites

Some points:

5 hours ago, KF5WGB said:

It needs to be a standalone thing.

Relying on Access doesn't make the app standalone. You can easily drop this dependancy by switching to SQLite.

13 hours ago, KF5WGB said:

3: Start with 1st record
7: next record until all records are processed.

This isn't the way most relational databases work. DBs use tables which contain rows (commonly called records) with no specific order. Think about rows in a table as a mathematical set. The language typically used to interact with a DB (RDBMS = relational data base management system) is SQL.

To obtain the list of rows having column fldQSLConfByS you would use something like:

select * from MyTable where fldQSLConfByS not like '%Q%';

The resultset of this query is the subset of rows which do not contain the letter Q in column fldQSLConfByS. You can get then in 2D array form and obtain the string you want for each row. Append yourself Q where it goes or build a more explicit query doing that by itself.

To append in the DB the letter Q to this column where it isn't already present in the string:

update MyTable set fldQSLConfByS  = fldQSLConfByS || 'Q' where fldQSLConfByS not like '%Q%';

 


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

My two cents worth

  • mdb is MS Access, it is primitive form of database with spreadsheet type functionality
  • Upgrade to SQLite, it will change your life
  • Use AutoIt's built in SQLite functions, or
  • Use the ADO.UDF

You are wasting your own time with mdb.  

(I agree with everyone here :) )

Have fun


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
1 hour ago, Skysnake said:
  • Upgrade to SQLite, it will change your life

Agree 100%, I even switched from a commercial DB format to SQLite; best (DB) decision I made 😉

Share this post


Link to post
Share on other sites
Posted (edited)

i have used ms access to model to prototype complete application databases and then you can export/import to whatever commercial one you want, it's not a bad tool at all. i just would not use it for heavily used large  multi-user database applications--depending on the application.

once i made an access db to do all the meeting scheduling built into an ASP based factory website (controls free--all code) calendar application, for that purpose it was great, users loved it and it became the defacto way to schedule meeting rooms and such. They could eventually upgrade it to SQL Server but it imports flawlessly

 

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Share this post


Link to post
Share on other sites
Posted (edited)
4 hours ago, jchd said:

Some points:

Relying on Access doesn't make the app standalone. You can easily drop this dependancy by switching to SQLite.

It does NOT rely on M$ Access. Where did you get that? It is just a .mdb database. I do not have M$ Access, never will get it. Libre Office or Open Office do just fine for me.

Edited by KF5WGB

Share this post


Link to post
Share on other sites
Posted (edited)

@everyone,

Gents, the database is not created by me, my mistake to not point that out enough.  It is the native DB  N3FJP's Amateur Contact Logging Program (ACLog - www.n3fjp.com) saves the log file. It is an Amateur Radio HAM program to log contacts.

I just wanted to use access.au3 to add "Q" to a specific field (jchd, it has nothing to do with Access) as a "switch" after I read the full record and sync it with an online database (QRZ.COM) with my A2Q+ program. It looked 'simple' and easy to use without getting to deep into studying SQL or ADO. I thought I can get it done and if I run into a problem, somebody might have some experience with it , or used access.au3.

ADO (ADO 2.1.15 BETA)
un- commented the one I wanted (Example_MSAccess), set full path and the ADO example throws this error:

"C:\Users\KF5WGB\Desktop\AutoIT\UDF\mdb_database\ADO_mdb_access\ADO.au3"(381,89) : error: _ArrayDisplay() called with wrong number of args.
        _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors)
        ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^

Looks like I am out of luck.

Edited by KF5WGB
added ADO

Share this post


Link to post
Share on other sites
1 minute ago, Earthshine said:

post the db with some test data, peeps here will help out  I am sure. heck, I might even give it a crack.

you really should learn basic SQL, it's so easy. also post your script. help us help you

Thanks Earthshine,
The script (modified example from Access.au3), database sample and the access.au3 are attached to my first post.

Share this post


Link to post
Share on other sites

Hi guys,

Looked into, well tried, Autoit's SQLite examples from the help file.
As soon as I start any example, SQLite_Startup() stops with: "SQLite.dll Can't be Loaded!" error msg.

I have AutoIt 3.3.14.5 installed.
#include <SQLite.au3>
#include <SQLite.dll.au3>
both au3 files are in the include folder.

ADO:
downloaded it again and still get the errors. (see post before) Giving up on that one.

Any idea about the SQLite problem?

Thanks for any help

Share this post


Link to post
Share on other sites
Posted (edited)
5 hours ago, KF5WGB said:

Any idea about the SQLite problem?

The SQLite DLLs (32/64-bit) are no longer a part of the AutoIt installation and the code to automatically download the DLL file has been commented out of the include file for some time now.  You just need to manually download the DLLs and place them on your file system.  You can get the latest DLL files directly off of the sqlite website on the SQLite Download Page

Note that the 32-bit and 64-bit versions that you download will have the same name (sqlite3.dll).  The 64-bit version should be renamed to sqlite3_x64.dll in order for it to automatically work with the sqlite.au3 include file.

Edited by TheXman

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

  • Similar Content

    • By Blois
      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!
       
    • By mLipok
      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
×
×
  • Create New...