Home Page list
MS Access Engine #msaccess #msexcel #crystalreports #odbc
I wanted to be able to create my own reports from the RM database and the custom report facility in RM has a number of shortcomings:
- it is not very sophisticated and even quite apparenly simple reports are impossible to achieve (eg show me which census records are missing for an individual)
- I am not familiar with the tool - but I am more familiar with other tools such as Crystal Reports, Excel and Access and the facilities they give (including a range of superior default report layouts)
- There is an issue that any custom reports may get lost when transferring data from one RM database to another through a GED interface.
In addition, although I am familiar with SQL as a concept I have not used it beyond the basic level and therefore the manual construction of INNER, OUTER, UPPER and LOWER joins as well as UNIONS and DIVORCES meant that the use of the various SQL Lite managers was not that helpful. The facilities for automatically linking Tables within Crystal or Access allow users at my level to make the necessary Table Links without having to understand too much of the syntax or how it is achieved.
I quickly discovered that although there is a readily available ODBC driver available it could not be used directly within Crystal Reports (throwing up error internal error messages). Although it did work when using it within VB or Visual Studio. Unfortunatley the latter options then meant the manual coding of the SQL syntax.
After much experimentation and thought i realised that MS Access could be used to link to the RM database using the OBDC driver. This provides a number of benefits to those who may want to have more access to the internals of the RM database:
- many users will have MS Office installed including those with the Access option
- MS Access provides simplified, visual Table linking options and removes much of the need to know SQL syntax
- the MS Access tables are readily understood by most programmes that read databases (unlike SQL Lite). So once you have linked to the RM Database via Access you can get to the data from many, many other programmes; eg EXCEL, WORD and so on. This opens a lot of opportunities to Users who have a modest amount of IT literacy without being full-blown experienced programmers
There a a few quirks to oversome however. Nonetheless, it will take no more than 10 minutes to create a working Access link to the live RM data and a futher 20-30 minutes will both iron out odd quirks to allow flexible access, selection and formatting of reports from your live RM data. This is 'one-time' effort - once it is done then new reports can be created or existing reports amended very easily - using the tools you are familiar with.
How To Do It.
PLEASE NOTE: I am using MS Access V7. If you have earlier versions there is no specific reason this will not work but some of the dialogs within MS Access have changed and you wil have to select the appropriate options accordingly.
PLEASE NOTE. I have used this on two computers; one running Microsoft Vista and the other Windows 7.
PART ONE - Installing the ODBC Driver.
The ODBC driver for SQL Lite is available free from here.
There are a number of versions of the driver available but unless you are running some old software you would usually select the latest version at the top (this is the version I used).
Download and install the driver. (This was entirely trouble free for me - but if you have any problems you may want to check you are logged on with Administrator priviledges).
PART TWO - Creating the Access Database and Linking to RM
Launch MS Access. Select the option to create a new database and name it and save it.
Select the "External Data" tab at the top and click on the "more" option. You will be given a list of options. Select the top one "ODBC Database".
You will be presented with a screen asking if you want to Import the Data or Link to the data
If you choose to IMPORT the data you will, when the process is complete, just have a copy of your data in the Access database. This will not be automatically updated when you change your live data. It is unlikely you will want to use this option unless you intend to manipulate and update the data.in a safe environment.
If you select the 'LINK to Data source by creating a Linked Table' option then no data will be put in the Access database and program will always go to your live database to get the information you want. This is the option to select if you want to be able to most easily report on the live data in your RM database. After you have selected this option click the OK button.
You will then be presented with a "Create New Data Source" dialog; select the appropriate option and click OK.
You will then be presented with a further "Create New Data Source" dialog; scroll down and select the 'SQLite3 ODBC Driver' and click OK.
You will then be presented with a further dialog - just click the Finish button.
You will then be presented with a 'SQLite3 ODBC DSN Configuration' dialog. Enter into the Data Source Name field a user friendly name that you want to remember the database by; eg 'Live RM Data'.
Then use the Browse button to go and select your live (or test) RM database that you actually want to link to.
Ignore all the other fields and press the OK button.
You will then be returned to you previous ODBC dialogs which you can now close with the OK buttons.
Finally you will return to MS Access where you will see the 'Import Objects' dialog of all the tables available in your RM database. At this point is best to 'Select All' the tables and press the OK button.
You can now save your Access database.
The good news is that you only have to do all of the above ONCE. You now have access to all your RM tables via the Access database and you will see that you can open them and browse them in the normal way from within Access.
BEWARE - as well as browsing you will be able to overtype the data in the database. Do not do this unless you know what you are doing and understand the consequences of altering the data.
Next: MS Access Engine - Event Query