Conference materials to download are below with the talk details.
Date: Tuesday 9th April 2024.
Location: Imperial College, London. Our thanks to Imperial for hosting the event.
Pre-conference: There is no pre-conference session this time, however after the success of last year’s pre-conference dinner, we will hold another (venue TBC).
Cost: £20+vat inclusive of VAT (primarily to cover the cost of lunch. Tea Coffee will be self-service, pay on the day).
Agenda: Download available here
VBA Under The Bonnet.
Abstract
Ever wondered how VBA works under the bonnet? Microsoft created a programming language which hides difficult tasks like allocating memory from developers, but sometimes it’s useful to understand what’s happening behind the scenes. Adrian will pull back the curtain on some of these secrets and describe how Access manages pointers, allocates memory etc, but at an introductory level only – no rocket science involved!
Speaker Bio: Adrian Bell
Adrian has been working in IT since leaving school during which time he’s had five ‘proper’ jobs and three stints as working for himself. His most recent job prior to working as a consultant was with Stat Plus Limited, where he found himself responsible for, and increasingly working with, various Access databases. By the time he left in 2010, Stat Plus had been bought out by and merged with Office Team and Adrian had been involved with migrating all their data into their system – a massive job that was fully successful and completed by the due date. Since leaving Office Team in September 2010, Adrian has managed to set up a small but successful company consulting to SME businesses that need help with Access and data generally. His main claim to fame as an MVP is his contribution to the Access support forum on Bytes.com: http://bytes.com/profile/51203/neopa/. Currently he has just over 30,000 posts out there which is a clear indication of how much interest there still is in Access. Adrian is married to Carolyn and they have two grown-up children – Christopher and Sarah. They all live in Shirley near Croydon.
Downloads:
SAFER GP Buttercup Data Collection APP: The joy of blind data collection on GP data networks.
Abstract
You know when you want to make a quick and dirty little database that will do what you have been promised to be a simple job?
And then users/funders look at it and say ‘Hey, we could do so much more now we’ve seen what you’ve done!’
This is one of those times – in 2019, a simple Excel spreadsheet (to aid NHS GP Practices in inviting people to research) became a simple database application …
… then a more complex ‘installed APP’ (*) with its very own 74-page SOP(+) and the fight began to get it working on GP networks.
- Could JB his merry team face – and yea, overcome – the challenges of getting Practice admin staff setting up MS Access DBs on 200+ GP data networks?
- Would the epic battle between 32-bit and 64-bit Office derail [or pointlessly double] their efforts?
- Would the digital tsunami known as N365 rollout drown them, or would they ride the wave like true MS Office surfer dudes?
- Would the autonomous licensing strategy mean they were all washed up at GP practices that didn’t have MS Access installed?
- Would the GP APP work on OneDrive in its current front end/back end classic format? (spoiler: nope)
- (Would the GP APP actually work?)
- And most importantly – if all else failed – would the NHS Area IT Support step up and enable us – or nobble us??
Find out in this thrilling(**) instalment of the continuing professional life of James Brimicombe( ++). With APP demo!
*** Those easily frightened by NHS regulatory literature may elect to sit at the back, near exits. ***
(+) Standard Operating Procedure
(*) called ‘APP’ to make it more hip and contemporary
(**) a relative term for IT people
Speaker Bio: James Brimicombe, Cambridge University
James has been designing and building database applications that capture and validate study data at the University of Cambridge for 18 years – either with MS Access on internal data servers, or online eCRF / Survey platforms / Web sites. He is a jack of all trades and master of none, but the one constant in his career has been MS Access, the veritable swiss army knife of data management.
Download available here (see ListServer for password)
Optimising SQL Traffic from the Back-end to the Front-end by using User-specific Parameters
Abstract
Bryant’s first law of split databases is “only send and retrieve the data you need”. Bryant’s first law of databases is “why write something when someone else has already done it better?”. When upgrading from Access to SQL it’s possible to leave highly inefficient record-sources in your code (a query over the entire order detail lines table to process just 1 order’s records). Instead, with a little work, you can eliminate inefficient front-end design by parameterising SQL Views quickly and easily. The presentation will show you how to create a single custom parameter table in your SQL Back-end that allows filtered SQL Views to be used as a minimal data source to your front end. The great thing about this is that your frontend uses generic code but filters per user (or per workstation, or by both) so that your user retrieves from SQL only the rows they need to perform their next step.
Speaker Bio: PJ Bryant
Peter has run his own consultancy since 2004 and (after a fashion) specialises in not specialising; he’s worked in almost every sector ranging from automotive to hi-fi, from reseller to print, and financial services to charity. He has used Microsoft Access since the early beta program three (thanks Andrew!) decades ago and his projects are now more about data analysis, function and business problem solving rather than the most amazing code writing. Projects are nearly always lengthy prototyping/agile projects where the client starts out quite unsure of their expected final outcome. As well as Access/SQL development he gets involved in general IT Management for SME’s as well as project management for resellers and enterprise.
NB Access Database, SQL database and scripts will be available shortly
Slides for presentation April 2024 PJB Slides
Application Architectures with Access and the Internet: Discussion Session
Abstract
Perhaps you’ve developed an application with an Access Frontend and SQL Server backend – and the SQL Server database is on a local Server. But then the client/sponsor says they are interested in opening the data up to clients or suppliers. How would you go about doing this?
Possible solutions include Azure, Terminal Server, web frontends using PHP or .NET, PowerBI, PowerApps, SSRS – and many more besides. But what is the best option for your requirements?
Rod will chair a discussion on architectural options for Access/Web applications. Bring along your questions, or if you have experience of such projects then please share these with other members. The discussion then leads into the next session …
Speaker Bio: Rod Gordon
Rod runs a software business with twenty staff who provide business management systems for over 25 exam boards such as Royal Academy of Dance, Chartered Institute of Taxation, Chartered Institute of Procurement Supply. The web-based system written in .NET/SQL (www.parnassusonline.com) supports tens of thousands of users across over 75 countries processing millions of records.
THERE ARE NO PRESENTATION SLIDES BECAUSE THIS WAS A CONVERSATION, NOT A TALK
Automating an Access SQL Database, including Web Forms, with No Code
Abstract
Every time a client says “Can we get people to enter data on the web for this?” my heart sinks. My brain doesn’t have enough space to remember what little it knows and also add information about C#, ASP .NET and all the other required topics. So, I’ve always chickened out at this point.
Peter B has presented before on using PowerApps, which is a really interesting solution, but comes with a number of license-related headaches.
Faced again last year with this problem on a project for Oxford University, I leveraged a number of technologies:
- Cognito Forms for a powerful, flexible, no-code web form that can be embedded on a website with no licensing costs
- Make – to automate moving the data to the tables, as well as running as many stored procs as you can eat, and
- SQL Azure to hold the data
Total cost – including Azure – under £15 per month!
Speaker Bio: Andrew Richards
Andrew has been training people on and using Microsoft Access since 2000. Since he set up The IT Service in 2009, he’s built and helped to build databases which manage
- Estate Agents sales
- the tracking of samples in a forensic laboratory
- the monitoring of land usage in Tibet
- the River Thames Flood Alleviation Scheme (for the Environment Agency)
- exams at Imperial College London
- staff time at Oxford University
- store openings for Marks and Spencer and
- the sales performance of CDs by Jedward (remember them?)
…along with lots of other dull ones. Most of his work these days uses SQL Azure as the back end with Microsoft Access as a front end.
Content is stored with the online repeat of session.