.mdb Can not write field back to record

[KF5WGB]

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

[TheXman]

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 May 21, 2019 by TheXman

[KF5WGB]

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 May 21, 2019 by KF5WGB

[Earthshine]

Use the ADO udf. Time for you to learn something

[jchd]

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%';

 

[Skysnake]

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

[Earthshine]

learn some basic sql too

[dmob]

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 😉

[Earthshine]

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 May 21, 2019 by Earthshine

[KF5WGB]

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 May 21, 2019 by KF5WGB

[KF5WGB]

@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 May 21, 2019 by KF5WGB
added ADO

[Earthshine]

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

Edited May 21, 2019 by Earthshine

[KF5WGB]

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.

[Earthshine]

kk, let me have a look.

[KF5WGB]

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

[Danp2]

@KF5WGB You might want to check out the following thread. I have used this UDF to read from and update an Access database.

 

[TheXman]

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 May 22, 2019 by TheXman

[AdamUL]

You can get the SQLite DLLs for AutoIt from https://www.autoitscript.com/autoit3/pkgmgr/sqlite/, as specified in the Help File for _SQLite_Startup.  

 

Adam