Having worked with RMG since it was Family Origins (in DOS), and having been a PC database programmer for 30 years, I did not anticipate any problems getting SQLite and picking up how to use it with RMG. But there has been a roadblock. I cannot find how to start.
I’ve looked at a variety of sites with discussions on SQLite/RMG, but they all seem to start somewhere in the middle, or they tell me how to do things using SQLiteSpy.
I understand Structured Query Language, but there has to be a structure to the commands. I don’t know the name of the table(s) from which I need to query, nor the field names, and I cannot find any sort of “START HERE” instruction to tell me what it is. I cannot find any code examples
I used to use Visual FoxPro to pull data back when it was Family Origins, because the tables were all separate .dbf files. I’d just like to pull my data from RMG, copy it into MS-Access, and do a few reports I cannot do easily with RMG.
If I believe everything I’ve read, this should be easy. What am I missing?
I notice that you mentioned Microsoft Access. I actually prefer just using SQL directly rather than using Microsoft Access, but if you prefer Microsoft Access then you look further on Tom’s site you can find directions for setting up an ODBC link between your RM database and Microsoft Access or Microsoft Excel. Either way, you will need to learn a bit about RM’s database schema. Tom’s link provides excellent information about the schema.
If you are familiar with Power Query (either Excel or Power BI) – that is another option. If you understand table structure and primary keys you should be able to pick it up in short order.
I use Sqlite Expert Person directly as well as Excel /Power BI.
(much easier than MS access in my opinion).
I am also familiar with SQL but had never used SQLite or looked ‘under the hood’ of Rootsmagic until very recently.
The instructions for setting up the ODBC link are indeed very clear. If you use this to open an Access database you immediately get to see the names of all the tables, You can also easily click on the tables to open them and look at the field names and/or the raw data. I found this very useful in understanding something of the structure of the tables and their contents.
How you then write any queries is up to your preferences.
SQLite Spy and SQLite studio are both front ends for working with SQLite. If you’re familiar with Microsoft’s Sql Server they equate to Management Studio, or in Oracle to SQL Developer, i.e. a way of seeing what is in the database and running queries.
On Tom Holdens sqlite tools for Roots Magic site I’ve put a database diagram for RM9 at https://sqlitetoolsforrootsmagic.com/rm9-database-diagram-rm9-database/ and this gives tables and fields. There are lots of useful queries on that website but I recommend just exploring the data first.
I’ve been retired for a year now, and while I still use both Access and Excel on my PC, I haven’t used SQL Server since months before my retirement (it was the backend for our data, while all user interfaces and reports were created in Access), and I’m afraid the “Use it or Lose it” rule is falling into place. Too much of this has turned to Greek for me.
Using Access would be fine for me. The FamilyOrigins iterations of this product, the tables were in XBase, and I regularly and directly opened them in Visual FoxPro (those were the days). Do I need to build an ODBC connection to SQLite, and THEN to RMG? Sorry, I might know a bit about SQL programming, but setting up connections was a once-every-few-years situation at work, and I no longer have access to my network guys for help. : - )
My main motive is to pull data from RMG to create reports in Access that I can’t find in RMG, or just are better quality than an RMG Custom report.
Tom’s link worries me just a bit in that it’s a few years old, using Windows 7, Access 7, and RMG 8 - I have Windows 11 Home, Access 2021, and RMG 9.33.
I’ve worked in PC databases for over 30 years, and used SQL for over 25, but I feel like a complete rookie right now.
I assume you have gone to Tom’s site at SQLite Tools for RootsMagic? Then click on the Learn tab. It’s best to post future questions on Tom’s site.
The basic process to connect Access to your RM database is to download an ODBC connector for SQLite, then to create a DSN which connects to RM, and then to connect to that DSN from Microsoft Access. Access doesn’t really connect directly to your RM database. It instead has to go through an ODBC connector and the Windows way to configure the connector is a DSN.
You can create a file DSN or a system DSN. I’m sure there is a simple way to get a file DSN to work, but I never succeeded in doing so. I had to create a system DSN instead and it worked just fine.
I tried it. It failed. I installed the ODBC driver, and created a new database in Access, but when I got to the point of clicking on “External Data”, it only wanted to EXPORT, while the instructions specify IMPORT.
It says nothing about “create a DSN”, and I mentioned that setting up is a once-every-few-years thing, but I checked, and I have three SQLite DSNs: SQLite, SQLite UTF-8, and SQLite3. They are there, but Access is not following Tom’s instructions.
As I mentioned that he was using older versions of Windows, Acees, and RMG, and that this worried me.
I was correct about Tom using older versions. MS-Access has changed between iterations, and it took me some time to find how to create the link in ODBC.
After that, it didn’t work. I gave it the name of my RMG9 database, gave it a link to 2 of the 3SQLite drivers, and it cannot find a link. It just does not know what to do, and Microsoft “support” is even less supportive than I remember.
I’m calling it a night for now (I’m in Europe, so that’s not far off). I’ll pick this up again in a few days and try again.
You might want to register at Tom’s site and post in the forum with specifics. People are more likely to engage there since it’s not really a RM issue - Forum – SQLite Tools for RootsMagic
The details of Microsoft’s procedures for setting this up have changed through the years, both the Windows piece of it and the Access piece of it. I don’t have Access installed right now, but the procedure for Excel is the same and I just set it up successfully.
Microsoft’s directions say to set up the DSN by going to Start > Control Panel > Administrative Tools > Data Sources (ODBC)… But I don’t see Control Panel as an option so I simply searched for Administrative Tools in the little Windows search box to the right of the Windows icon in lower left hand corner of the screen. From there I clicked on ODBC Date Sources (32-bit). Click on the System DSN tab. As I said, I could never get the File DSN to work. There will be a list of items. Do not choose any of them. Instead, click on the Add button. You will get a very long list of ODBC drivers. The one you want will probably be at or toward the bottom and it will be SQLite3 ODBC Driver. If you see any other SQLite drivers, don’t choose them because they won’t work. If you don’t have one that’s called SQLite3 ODBC Driver, then you haven’t installed it yet. So find it, download it, and install it if necessary. Click Finish. You still have to give a DSN name in the Data Source Name field - any name you wish, and this is the name that will be known to Access. And you have to fill in the Windows file path for your RM9 database in the Database Name field. I don’t think any other boxes need to be checked, so just click OK and you are done with this part.
In Excel, you click the Data tab => Get Data => From Other Sources => From ODBC. You will have to give it the DSN name you created and you probably will have to give it your Windows username and password because it’s a System DSN. If you can get a File DSN to work, you probably won’t have to give the Windows username and password. My recollection is that the exact steps in Access are slightly different than in Excel, but I have no way today to test Access.
And I recommend again moving over to Tom’s site instead of using this forum for issues with SQLite.