Is it possible to connect Microsoft Access to a Rootsmagic 8 file. Or import the individual and marriage entries from a Rootsmagic file into an Access database. I am using MS Access within Office 2019 so a fairly up to date version. I am conversant in using MS Access but not very conversant with Windows (version 10) such as installing drivers etc. I have seen the post by Steve Jones but was lost installing SQLite driver. I am interested in running queries on individuals and marriages.
It is definitely possible, and I used to do it all the time. But I haven’t done it in several years so I’m not current in all the details. The same technique actually works both for Microsoft Access and Microsoft Excel. The difference is that with Microsoft Excel all you are really doing is displaying RM’s individual tables in Excel and with Access you can create queries and if you wish the queries can join multiple tables together. Either way, you would need to learn a little something about the RM database’s schema. The best place to learn such things is SQLite Tools for RootsMagic In particular, you might look at RM from Access if you just want to learn about using Access with RM. But even so, you need the bigger picture of the RM database schema.
The SQLite tools site is more oriented to creating queries using SQLite directly than it is to using Access to view the RM database, but you will find a little information about using Access. The driver of which you speak is called an ODBC driver. The concept is that ODBC drivers can be used to allow any relational database software to access tables from any other database. For example, DB2 software can access Oracle tables or Access software can access SQLite tables, which is the one you need, etc.
When you download an ODBC driver for SQLite to your Windows system, you will then need to create what is called a DSN. What the DSN does is to connect the ODBC driver for SQLite to a particular SQLite file - namely your RM database. Your DSN will be given a name and it’s the DSN name that you need to provide to Access or Excel when your are linking them to your RM database. Neither Access nor Excel will know the actual name of your RM database. They will know the name of the DSN and it’s the DSN that will know the name of your RM database.
If I remember correctly, you can make what are called system DSN’s or file DSN’s. It always sounded like file DSN’s were what I needed but I could never get them to work. They probably do work just fine and I was probably doing something wrong. But I had no difficulty getting system DSN’s to work.
By the way, the reason I quit using Access in this manner is that I just found it much easier to write SQL queries directly using an SQLite manager than to do all the pointy clicky things with Access. I have discussed this extensively with my daughter who is a fluent user of Access at work. She knows SQL and she can write SQL when necessary when she is using Access. But she almost never needs to write SQL with Access because she is so good with the pointy clicky stuff. It sounds like you are very good with the pointy clicky stuff as well.
Do be aware that you can set you DSN up so that you are seeing the real, live RM data in Access or so that you can see a copy of the RM data in Access. And if you are seeing the real, live RM database data in RM you can even set things up so you can update the data from Access. But I would never, never update RM from Access. And I mean never, never, never. I mostly run reports from my RM database using SQLite queries and on rare occasions I will update my RM database using SQLite. But I would never do an update of my RM database from Access.
Good luck.
1 Like
Thank you, I had not created the DSN, it was as simple as that. I have then followed the advice posted previously by Steve Jones and have Access now linked to my RM8 file. I will spend some time looking at RM from Access.
However I will also look into using the SQLite manager to access the RM 8 file.
Thank you again.