I am involved in a small project with a local sporting club to digitise it’s playing records.
The club has scorebooks going back to the 1930s in it’s storeroom where they are OK but still vulnerable to fire, vermin, and other paper destruction vectors, so it’s preserving that history.
Anyhow, we have a couple of collators who are transcribing the books into Excel templates ('cause that’s near universal in availability and user familiarity) which they send to me and I then aggregate and consolidate them into a MS Access (2016) database. It comprises three related data tables imported from Excel and an interface only of series of reports or forms. There is no data entry or transactions within the application. Just straight forward enquiries/extracts of the “show me all the guys who did [this] in [this] season and [this] grade” ilk.
Eventually the results will go into the national web-based and supported application we use for all the current grade fixtures played since 2008 but that could be some time away.
All is going well, we are now into the games played in the 1960s and within living memory. It’s time to show others our progress.
Now the original plan was that there’d be a couple of meetings and I’d run a demonstration off my laptop and let the club directors and committee each have a play. Not a problem. But Sydney is in COVID-19 lockdown. The app is only a couple of dozen MB so I could distribute by email, thumb drive or use something like DropBox but none of the target audience have MS Access.
Yes, I can do a Zoom and share my screen which might be the most practical option but I’d like the directors to get their own hands on the application. For some of these guys it will be the first time they have seen their own playing records for 40 years. A chance to quietly revive memories of past glories.
So I’d like to deploy something like a standalone version
Now based on my Googling the answer on how best to do this ranges from:
It can’t be done.
Convince them they need a MS Access license
It can be done with Runtime
There are utilities to compile standalone executable MS Access applications.
Can anybody advise if they have any similar experience and what solution they found worked best?
I like MS Access. I was using it this evening as a coding environment, plus it has persistent storage, reports, and forms for managing the reports.
But installing the runtime is like installing Office. It’s a honking big install that needs to be updated whenever there is an office update, and sometimes messes with your other office install.
Your application must start with an autoform or autoexec macro (because you don’t get the menus and the database window). If it does that, and it’s important enough to the user, you can trial the runtime. I haven’t used it for years, so I don’t know what the state of play is with the licencing.
If the application isn’t valuable enough to justify inflicting a MS Office installation on their computers, then there is no solution.
Yes, The MS Access runtime is only a part of Office, but it’s a heavy part, and if it isn’t the same version as their Office version, you’re inflicting two versions of Office on the same machine.
Personally, I’d look at writing a series of Word mail-merge macros for Demo purposes, filling a Word document instead of a report. A whole new application and a lot of work, but the kind of thing I did sometimes back in the day.
Just to note, Access does not play well at all with the back end DB hosted on OneDrive or similar - whilst it’s possible to coerce Access to split the DB with the backend in the cloud, the database will very quickly get corrupted because things like table and record locks won’t work properly.
It’s been a long, long time since I used ODBC. It used to be supported by Access.
I used Crystal Reports to connect to the database with ODBC. I use Crystal regularly and it’s very versatile for reporting. You can link tables. select records and data fields.
ODBC gives you network capabilities.
My reporting was from Oracle and I linked in a few tables entered into Access. The data in Access was work product entered by our dept.
Looks like it’s still supported. Sort of. Nothing is absolute with Microsoft.
I’m assuming you only want to get copies to others so they can see what it does with no updates of the master DB - Microsoft provide a free run-time for Access you can download and distribute with the database - that keeps everyone anyway from your precious master DB
How is this application going to be used after delivery?
Are several people going to keep it on their own laptops, who may not have an MS Office licence?
If so, then a third-party application would be better. For a simple standalone app and a read-only database, probably an SQLite database would work well, but a third-party app and an Access database would also work with the redistributable.
The other solution would be an online database, which could be fairly quick to create with the right tools.
Correct. Standalone demo versions. If they corrupt them que sera, sera.
This project will be gathering historic game data for another 12-15 months. If needed I can send out another demo version with updated data every 6 months if there is interest.
They’ll probably play with the demo a couple of times. Hopefully make some suggestions.
Once we have all the history loaded into MS Access there’ll be a copy on the Club’s computer, which does not have MS Access now but will in due course. The operational copy will be on my desktop as Registrar plus adequate back-ups.
Then once we get the OK to load the history into the national app we cut across to that, where all the current stuff is entered by the team captains each week.
I’ll second ODBC - can you figure how to write Excel macros and run them against the Access database?
(Since you mention the helpers are using Excel already).
For simple “fill in the boxes” queries against a set of tables, this should work.
Technical nitpick: Excel and the rest of Office support a number of connection protocols that allow connection to an ‘Access’ database. You would probably start with OLEDB rather than ODBC.
