CMIS to EXCEL VBA functions
Attached is a zip containing a spreadsheet that through the magic of VBA Macro's allows Excel to query some basic student information from a CMIS database. Three examples are given, one to return student id(s) given some search criteria, one to return teaching groups given a student id and one to return any field from the students table given a student id. Results from queries are held in a simple cache to aid performance (but don't expect to use this to produce large 'live' worksheets that fly along).
As given the workbook will need to be told where your database resides and the name of it. You will also need appropriate access permissions associated with your AD/Windows credentials. I recommend not accessing the CMIS database directly but create a separate DB and create views to allow read only access to the relevant tables or access a test/non live instance.
To set up, open the workbook but do not enable active content. Fill in the Server and Database cells (highlighted in red on the config tab) and then save and close the workbook. Now open the workbook, enable Macros and switch to the functions tab to see some examples of the function calls. If you didn't enter the database information correctly, the sheet will appear to hang as it tries to establish a connection to the DB and waits and waits and waits for the connection to timeout. If you did enter it correctly, you should see that the functions have returned some results.
For other use, the server and database names can be hard coded into the DBDefaults procedure (in the VBA). This avoids using cells in the worksheet to hold the values and the VBA can then be locked before it is accessed by end users.
Those with modest VBA and SQL skills and some knowledge of CMIS back end will easily be able to extend this by providing functions to access other areas of CMIS. If you do write other functions, please share them here.
This code is provided as is without warranty - use at your own risk etc. If it parties in your house till 3am, wakes the neighbours is sick on the carpet and eats your cat, do let me know but only because I like a laugh. Copyright is implicit - not having a copyright messages don't mean you can do what you like without permission. IT and MIS support Staff working in schools may make use of this code for use in their schools. If you go selling it for profit or start claiming it was you wot wrote it, I'll come find you and give you a jolly good telling off which may not be limited to words like "naughty", "bad" and "dishonourable cad". In summary - non profit use is just dandy but please don't profit from it without at least asking.