MS Access Engine – Using EXCEL #msaccess #msexcel #charts #filters

Microsoft Excel is a great tool for filtering/sorting/reporting on data in a Grid format. It also provides additional features such as various sorts of in-built graphs based on the data you are using as well as providing, for more advanced users, in-built calculation functions (formulas) that can be used to manipulate the data.

Naturally, as a Microsoft product it provides in-built connectivity to Microsoft Access databases.

If you have set up the links shown in the topics in this wiki concerning MS Access Engine and MS Access Engine – Event Query then you will now have the ability to look at, sort, filter, graph, manipulate data from your RM database from within Excel.

I am using Excel 2007 – so my screen shots reflect this. However, connectivity to MS Access has existed in Excel for many versions so you should be able to achieve the same/similar results from earlier versions of Excel – although the menus and screen layouts may be slightly different.

Firstly – we should remember that we are not directly connecting to our live RM data – we are using the Access database engine as a link through to the real data within the RM database.

Secondly – there is more than one way to connect to the Access engine from within Excel. One way is to click on the Data tab, select the “From Other Sources” drop down and click on “From Microsoft Query”. This will give you a list of your ODBC sources, inclusing the one you set up when you downloaded and implemented the ODBC driver. If you use this route you will see all the RM Tables which you can open as required. However, you will have the same limitations as were outlined in the other articles – you will not be able to select or sort on all fields.

The easiest wat to get to the data is to just open the Access database you created in following the instructions in the “MS Access Engine – Event Query” wike.

Go to File Open, make sure you are seeing the Access files (not just the Excel work book files, which is the default) and find your Access databse and open it. You will then be shown a screen listing the Queries you set up within the Access database:

If you select the MVSEventsCombinedQuery it will bring all the Events on your RM database up for display in EXCEL. This may take a moment or two – as Excel seems to read all the events in before populating the Grid. On my PC (quite fast) it takes about 12seconds to read in and display a total of 7500 rows:

Once this has been done you can use the usual features within Excel to sort / filter. In the example below I have set a filter on “Census” record types only and selected the Surname of “Waller” only. I have also hidden columns I do not wish to display. These enquiries are quick to do and instant to update onscreen.

WARNING. Do not UPDATE any data unless you are confident you understand what you are changing will not affect the operation of RM.

Prev: MS Access Engine | MS Access Engine – Event Query Next: MS Access Engine – Excel Pivot Tables | MS Access Engine – The BLOB Fields

Discussions & comments from Wikispaces site