Jump to content


Photo

Access via DB tools


  • Please log in to reply
5 replies to this topic

#1 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 109 posts

Posted 13 October 2009 - 10:24 PM

I'm still using RM3. All the DBF, CDX and FPT files are comfortingly familiar. I have often opened up MS Access, linked to the RM tables (or imported them) and generated my own queries and reports. I've been doing that since about Family Origins V5. This gives me the flexibility to do things not (yet) envisaged by the RM designers. I've not yet used this to modify the RM files, but theoretically it would work, it's just a little risky.

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?

#2 Ken Rury

Ken Rury

    Advanced Member

  • Members
  • PipPipPip
  • 49 posts

Posted 14 October 2009 - 04:24 AM

I would like to know how to view the data direct also as I have data that is in my database that RM won't display, just shows a blank line. It was a bug when they migrated my data from Family Origins and have yet to fix in 4.0.6.0.

#3 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 109 posts

Posted 18 April 2013 - 10:14 AM

An old post with no answers yet. Has anyone come up with a way for some external SQL-like tool to query against the RM database structure?

#4 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6162 posts

Posted 18 April 2013 - 02:38 PM

Visit the links in my signature.

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#5 GlenB

GlenB

    Advanced Member

  • Members
  • PipPipPip
  • 109 posts

Posted 18 April 2013 - 03:16 PM

Thanks TomH ... all you had to say was SQLLite. Now I'm good to go and do whatever I want! I'll scan through your site to see if you've uncovered any gotchas I should avoid. I expect I can "do no wrong" on SELECT queries but may need to be careful on UPDATE/INSERT. As always - if you don't backup your database before "messing" with it, you deserve everything that goes wrong.

#6 Jerry Bryan

Jerry Bryan

    Advanced Member

  • Members
  • PipPipPip
  • 3443 posts

Posted 18 April 2013 - 07:27 PM

After you get into the wiki as suggested by Tom, you will quickly discover that there are (at least) two good ways to run queries (or even updates) against RM's SQLite database. One way is to use ODBC to link the database to Access (or any other such database tool that has on ODBC driver for SQLite). The other way is to run SQLite queries natively using an SQLite manager. The manager most often used seems to be SQLiteSpy.

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.

Jerry