Access via DB tools
Posted 13 October 2009 - 10:24 PM
I understand that RM4 changes all of that to a single file database. I didn't see anything in the FAQs or the Forums that told me about that single file's format and whether or not it is accessible from conventional external DB-oriented tools such as MS Access. Where do I look to learn about that?
Posted 14 October 2009 - 04:24 AM
Posted 18 April 2013 - 10:14 AM
Posted 18 April 2013 - 03:16 PM
Posted 18 April 2013 - 07:27 PM
I think most users who use SQLite against RM use one way or the other way of running queries, but not both. I tend to use both, depending on the nature of the problem at hand. Originally, I made an effort to make my SQLite queries function in both environments with little or no changes between the two environments. Doing so was really a fool's errand because it turned out the dialects of SQL between SQLite and Access are so radically different. So now I just choose the environment that's most appropriate to the problem at hand and customize my query for that particular environment.
The trickiest problem you are likely to encounter is that RM uses a proprietary collating sequence for many text columns that can make sorting on those columns a little tricky. The purpose of the proprietary collating sequence is to sort without regard to case while simultaneously dealing with international characters See the wiki for some strategies to overcome this problem. I find the collating sequence problem slightly more difficult to cope with in Access than with an SQLite manager, but the problem can be dealt with in either environment.
Tom is the master of the SQLite query against the RM database, and deserves huge credit for his pioneering work and for generously sharing his insight and expertise with the rest of us. And there are many other RM users who similarly donate their insight, expertise, and queries to the wiki.