Having the database read-only in Access sounds like a feature: Writing to the GnuCash database using anything except the GnuCash API voids your warranty.
Access insisting on a non-text Primary Key an Access bug. All of GnuCash's tables have primary keys already, but they're mostly text representations of GUIDs because SQLite doesn't support 128-bit unsigned ints nor--unlike MySQL and Postgresql--have a UUID field type. Regards, John Ralls > On Jan 21, 2022, at 1:25 PM, Thomas Forrester <tlforres...@gmail.com> wrote: > > I have tried to switch to SQLite3 this afternoon, and although I cannot > answer all your questions, I have observed the following: > > 1. Regarding your stability on Windows question, Check Wiki on SQLite. > It's pretty ubiquitous at this point and extraordinarily stable on all > platforms. I don't think you're going to have an issue with stability. > > 2. There are log files when using SQLite3, but I cannot say if they work > as you are asking. > > 3. Using the recommended ODBC driver, I was able quite easily to connect > the GnuCash database to Microsoft Access, but immediately realized there > are insurmountable problems that I have not found with ODBC/Access using a > MySQL backend for GnuCash. Specifically, although the tables link into > Access with the expected number of rows, All column data shows as > #DELETED#. According to more research, I find the following > incompatibility between the way the GnuCash database is designed and what > Access aspects (from stackoverflow): > > [...] Access (Jet) wants a table to have a unique index in order to be able > to insert/update the table if necessary. > > If your SQLite table doesn't have a unique index (or primary key), then > Access will only allow read access to the table -- you can't edit the > table's data in Access, but the data displays fine. > > To make the table updateable you might revise your SQLite code (or using a > SQLite tool) to add an index to the table. > > If your PK/unique index happens to use a TEXT field, that's fine for > SQLite. However, when you link to it in Access, Access will show the > #DELETED# indications. > > The chain of events appears to be: > > Access/Jet notices the unique index, and tries to use it. However, SQLite > TEXT fields are variable length and possibly BLOBs. This apparently doesn't > fulfill Access's requirements for a unique index field, hence the #DELETED# > indication. > > To avoid that problem, the index has to be a SQLite field type that Access > will accept. I don't know the complete list of types that are acceptable, > but INTEGER works. > > > 4. I don't hold any hope of this, but it would be truly outstanding if the > development team considered this issue (#3 above) and worked to resolve > it. It most certainly is an outlier issue and not something germane to > GnuCash itself, in fact it is more akin to assisting someone in shooting > themselves in the foot. But to someone who understands the proper > precautions, it sure would be helpful! (Not sure why ODBC to Access using > MySQL works fine. Different db structures for different dbs???) > > > > > > > > On Fri, Jan 21, 2022 at 2:48 PM Kalpesh Patel <kalpesh.pa...@usa.net> wrote: > >> Few questions in regards to SQLite3 backend recommendation because >> currently >> XML format for my data is compressed size of 1.82MB so exploring moving to >> it. Not sure if this is considered small sized XML file or not but . >> >> >> >> 1 - SQLite was natively born on UNIX systems with UNIX systems in mind so >> how stable is it for Windows and how well is it supported from the GNC >> perspective? >> >> 2 - Does it still create the log file to be able to replay it like XML >> storage creates? >> >> 3 - How can I roll back transactions (more so related to roll back from >> import errors)? With XML I can take the previously created file and make it >> my current file after renaming it. >> >> >> >> Any other differences to keep in mind other than backup regiment? >> >> >> >> >> >> >> >> ------------------------------ >> >> >> >> Message: 8 >> >> Date: Fri, 21 Jan 2022 08:43:04 -0800 >> >> From: john <jra...@ceridwen.us <mailto:jra...@ceridwen.us> > >> >> To: Thomas Forrester <tlforres...@gmail.com <mailto:tlforres...@gmail.com> >>> >> >> Cc: Peter <pan...@iinet.net.au <mailto:pan...@iinet.net.au> >, >> gnucash-user >> >> <gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org >>> >>> , john.lay...@laymanandlayman.com <mailto:john.lay...@laymanandlayman.com> >> >> >> Subject: Re: [GNC] Exiting GnuCash Lock file Vol 226, Issue 57 >> >> Message-ID: <e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us >> <mailto:e0d29238-0072-40cd-aea7-8dacecea3...@ceridwen.us> > >> >> Content-Type: text/plain; charset=us-ascii >> >> >> >> Very few users will be able to manage a MySQL database server safely. >> Fortunately GnuCash provides a SQLite3 backend that doesn't require any >> database admin skills. The only caveat is that unlike XML it doesn't write >> a >> fresh file every session so you need to arrange backups on your own; the >> OS's built-in snapshotting facility (rather grandly named Time Machine on >> macOS and simply Windows Backup on Windows) is good for this, just make >> sure >> that your GnuCash data folder is included in its list of things to back up. >> >> >> >> Regards, >> >> John Ralls >> >> >> >> >> >>> On Jan 21, 2022, at 6:45 AM, Thomas Forrester <tlforres...@gmail.com >> <mailto:tlforres...@gmail.com> > wrote: >> >>> >> >>> Seems like a lot of work. No doubt you're finding interesting >> >>> anomalies, but... >> >>> >> >>> Rather than using xml data files, which have always seemed odd to me >> >>> (a la, this thread), if you use a MySQL database instead, all saves >> >>> are automatic and completely bypass the need for all this testing. >> >>> You still have to save each transaction, or edits to a transaction >> >>> (Enter key), but otherwise you can freely close the program without >> >>> any worries about loosing data, and there is no save prompt on the way >> >>> out. I've run with a MySQL database from the start without any >> >>> problems at all. Just a thought, and I recognize some may have >> >>> reasons not to choose that path, although I'm not sure what they would >> be. >> >>> >> >>> On Thu, Jan 20, 2022, 11:22 PM Peter <pan...@iinet.net.au >> <mailto:pan...@iinet.net.au> > wrote: >> >>> >> >>>> John, >> >>>> Well yes it did terminate or close. >> >>>> gnucash was closed and was then reopened without any reported issue. >> >>>> Other than it failed to save the changes and delete the lock file. >> >>>> Maybe if you could tell me how to confirm it closed or terminated >> >>>> correctly, I will go and test and see what happens. >> >>>> This issue I noted in Test#2 was actually caused by me not following >> my >> >>>> own test process. >> >>>> >> >>>> On 21/01/2022 04:48, John Layman wrote: >> >>>> >> >>>> The test cases that appear to have failed aren't conclusive, however, >> >>>> without co nfirming that GnuCash had actually terminated, and >> >>>> terminated normally. >> >>>> >> >>>> -----Original Message----- >> >>>> From: gnucash-user [1]<gnucash-user-bounces+john.layman= >> >>>> ieee....@gnucash.org <mailto:ieee....@gnucash.org> > On >> >>>> Behalf Of Peter >> >>>> Sent: Thursday, January 20, 2022 9:39 AM >> >>>> To: [2]gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> Subject: Re: [GNC] Exiting gnucash Lock file Vol 226, Issue 57 >> >>>> >> >>>> Here is the testing methods I used to verify the issue I have noted. >> >>>> >> >>>> Selected a known small good gnucash data file. >> >>>> Storage Location: Local D: c: drive is Samsung SSD and D: is Seagate >> >>>> 2TB GNUCAS H Storage type : XML and compressed Operating system: >> >>>> Windows 10 GNUCASH versio n 4.9 The opening method in ALL test was to >> >>>> use the GNUCASH icon on the TASKBAR and then select the recent (test) >> >>>> file If the LOCK file was not deleted it was r emoved using the >> >>>> windows File Explorer >> >>>> >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #1: - change, Save Button and then File->Quit >> >>>> >> >>>> Result: This was repeated 5 times each time the same result - Lock file >> >>>> was dele >> >>>> ted and the change was Saved. >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #2 - change then File>Quit and save at 15 seconds on timer >> >>>> >> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT >> >>>> saved in >> >>>> the data file This test was actual performed more than 5 times but I did >> >>>> note 5 >> >>>> of 5 in one contiguous run. There were a few test that asked to save the >> >>>> transa >> >>>> ction prior to the question of save the file I was never able to get it >> to >> >>>> repea >> >>>> t twice in a row. >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #3 - change and exit File->Quit wait for the Save dialogue then let >> >>>> it time >> >>>> out >> >>>> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >>>> the dat >> >>>> a file >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #4 - change and wait for autosave then File->Quit >> >>>> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >>>> the dat >> >>>> a file >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #5 - change and wait for autosave then use X method >> >>>> >> >>>> Result: In test 5 of 5 Lock file was deleted and the change was saved in >> >>>> the dat >> >>>> a file >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Test #6 - change then use X method wait for the save dialogue and save >> at >> >>>> 15 sec >> >>>> onds on timer >> >>>> >> >>>> Result: In test 5 of 5 Lock file was NOT deleted and the change was NOT >> >>>> saved in >> >>>> the data file >> >>>> >> >>>> >> >>>> >> >> ---------------------------------------------------------------------------- >> ---- >> >>>> --------------------- >> >>>> >> >>>> Conclusion: >> >>>> >> >>>> There is an issue in exiting with the SAVE button enabled/Highlighted >> and >> >>>> comply >> >>>> ing with the dialogue to SAVE the Data file. >> >>>> Test#2 and Test#6 >> >>>> >> >>>> Four methods of exiting that do delete the LOCK file and SAVE the latest >> >>>> changes >> >>>> A: Waiting to exit after an Autosave then File->Quit >> >>>> B: forcing a Save by using the SAVE button then File->Quit >> >>>> C: Waiting to exit after an Autosave then the X method >> >>>> D: forcing a Save by using the SAVE button then the X method >> >>>> >> >>>> X method works if you do a SAVE using the Save Button or wait till >> after >> >>>> an Au >> >>>> tosave then exit via the X method >> >>>> >> >>>> There is an issue with exiting and relying on gnucash to save the data >> if >> >>>> gnucas >> >>>> h produces the dialogue to SAVE the data then File->Quit or the X method >> >>>> The Sav >> >>>> e does not work and the LOCK file is not deleted. >> >>>> >> >>>> I cannot safely say I do not use the X method as I now have doubts >> about >> >>>> it, I >> >>>> may use it or I may not. >> >>>> >> >>>> Hope this helps someone as now I understand why sometimes it does work >> and >> >>>> does >> >>>> NOT work, there is a bug in it or is it a misinterpreted enhancement. >> >>>> >> >>>> >> >>>> >> >>>> _______________________________________________ >> >>>> gnucash-user mailing list >> >>>> [3]gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> To update your subscription preferences or to unsubscribe: >> >>>> [4]https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> If you are using Nabble or Gmane, please see [5] >> >>>> https://wiki.gnucash.org/wiki/Ma >> >>>> iling_Lists for more information. >> >>>> ----- >> >>>> Please remember to CC this list on all your replies. >> >>>> You can do this by using Reply-To-List or Reply-All. >> >>>> >> >>>> References >> >>>> >> >>>> 1. mailto:gnucash-user-bounces+john.layman=ieee....@gnucash.org >> >>>> 2. mailto:gnucash-user@gnucash.org >> >>>> 3. mailto:gnucash-user@gnucash.org >> >>>> 4. https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> 5. https://wiki.gnucash.org/wiki/Mailing_Lists >> >>>> _______________________________________________ >> >>>> gnucash-user mailing list >> >>>> gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>>> To update your subscription preferences or to unsubscribe: >> >>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>>> If you are using Nabble or Gmane, please see >> >>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information. >> >>>> ----- >> >>>> Please remember to CC this list on all your replies. >> >>>> You can do this by using Reply-To-List or Reply-All. >> >>>> >> >>> _______________________________________________ >> >>> gnucash-user mailing list >> >>> gnucash-user@gnucash.org <mailto:gnucash-user@gnucash.org> >> >>> To update your subscription preferences or to unsubscribe: >> >>> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> >>> If you are using Nabble or Gmane, please see >> https://wiki.gnucash.org/wiki/Mailing_Lists for more information. >> >>> ----- >> >>> Please remember to CC this list on all your replies. >> >>> You can do this by using Reply-To-List or Reply-All. >> >> >> >> >> >> >> >> ------------------------------ >> >> >> >> _______________________________________________ >> gnucash-user mailing list >> gnucash-user@gnucash.org >> To update your subscription preferences or to unsubscribe: >> https://lists.gnucash.org/mailman/listinfo/gnucash-user >> If you are using Nabble or Gmane, please see >> https://wiki.gnucash.org/wiki/Mailing_Lists for more information. >> ----- >> Please remember to CC this list on all your replies. >> You can do this by using Reply-To-List or Reply-All. >> > > > -- > Tom > Thomas L. Forrester > 3211 Patty Lane > Middleton, WI 53562-1652 USA > 608-831-0769 > _______________________________________________ > gnucash-user mailing list > gnucash-user@gnucash.org > To update your subscription preferences or to unsubscribe: > https://lists.gnucash.org/mailman/listinfo/gnucash-user > If you are using Nabble or Gmane, please see > https://wiki.gnucash.org/wiki/Mailing_Lists for more information. > ----- > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.