November 19, 2019 at 11:53 pm
We use the Access runtime on a terminal server as the front end, with a SQL Server back end. Each of the 100 users has their own copy of the MDB front-end. In 12 years we went thru 3 upgrades of server OS, database, and MS-Access, and it all still works.
November 20, 2019 at 8:53 am
We use the Access runtime on a terminal server as the front end, with a SQL Server back end. Each of the 100 users has their own copy of the MDB front-end. In 12 years we went thru 3 upgrades of server OS, database, and MS-Access, and it all still works.
OMG when you write a new feature the version upgrade must be painfull - that's why I like web apps (the deployment is someone elses problem 🙂 )
MVDBA
November 20, 2019 at 9:01 am
I can see why you would think so, and true that updates does require a bit of plumbing.
Personally my apps simply check the client version number versus the server version number and, if they are not the same, it launches an update code. Since everything is contained in a single file the update is literally:
FileCopy NewVersionPath,OldVersionPath
And then the app relaunches. I've seen others who use a script to download the latest version each time the app launches. With small frontends (since the data is linked) the filecopy literally only takes a second or two.
November 20, 2019 at 9:19 am
we have the same system - about 1000 ish people log in to many different access clients (stock control, despatch control timesheets etc) - these are split across multiple shifts so I guess 300 terminals with multiple user profiles... each time they login and open up the app they need for that workstation it calls a batch file to check the version.
the biggest problem we've found (and trust me I would have done this as a web app if I had been here when it started) is that we have multiple versions of access. if a developer makes a change in one application and uses the wrong version - the file format can be wrong for the version on the users workstation.
I am only the lowly DBA , so I look after the backend and try to advise on good practice as and when i'm allowed out of my cage 🙂
MVDBA
November 20, 2019 at 9:28 am
OMG when you write a new feature the version upgrade must be painfull - that's why I like web apps (the deployment is someone elses problem )
Not so - as Smiley Coder was saying.
My experience with having Access as a front-end client for SQL Server has been that
MarkD
November 20, 2019 at 9:37 am
but how do you manage version control and merge changes for multiple developers?
only one person can work on it at a time - and there is no way (that I know of) to merge 2 changes.we have 30 or so application engineers fixing issues and implementing changes to an access front end.
And even worse, I'd guess there are a few thousand finance spreadsheets with macros and functions in that are coded by "account managers" that end up on the support team's desk
I'd still argue for a neat little web app which means we can have it under source control and there is zero deployment to the end user.
MVDBA
November 20, 2019 at 9:44 am
I will say that being multiple developers on a project is a real challenge that Access does not solve at all on its own. Are you saying you have 30 engineers on a single access project? Interesting.
There are 3rd party Source Code add-ins, which can do code base merging, such as Ivercy and Oasis, and give you integrated source code control.
I get that you want a web app and there are many valid reasons for that. But those 30 engineers don't know how to make a web app, and the web app developer(s) might not understand the need of the 30 engineers. Also do you have the required staff and budget to take over the task from the engineers?
November 20, 2019 at 10:03 am
30 across our "zoo" of applications (they are all named after animals) - I have no idea how many - maybe 40 or so access frond ends (I still call then .adp).
but when an app developer picks up a support ticket to fix a form , then another dev picks up a ticket to fix a view we have to work that out somehow
the other 28 guys might be working on other systems
Thanks for the hint on those tools - I might have a look later today, i'd not heard of them
Just one more point before I get on with my day job (before I get sacked) - one of the reasons we're shifting away from access is that the modern word is moving towards mobile technology - particularly in a warehouse where you need portable devices
I know laptops are capable of running access and are portable, but you can't stick it in your pocket while you are lifting boxes
which is why I am skewed towards web apps that can be on any device with a browser
MVDBA
November 20, 2019 at 10:08 am
... From a developer standpoint Access STOMPS Visual Studio...
I have a few issues with your post overall, which have been brought up by others, but I'm going to say that's a little ripe for me to leave standing uncommented. VS does a wee bit more than DB forms methinks.
November 20, 2019 at 3:12 pm
I will say that being multiple developers on a project is a real challenge that Access does not solve at all on its own. Are you saying you have 30 engineers on a single access project? Interesting.
There are 3rd party Source Code add-ins, which can do code base merging, such as Ivercy and Oasis, and give you integrated source code control.
That would make a great series of articles, building an Access front end with a VCS.
November 20, 2019 at 3:51 pm
Talk to Phillip Steifel, who created and sells Ivercy.
He's done a number of YouTube presentations that you might want to browse, as well.
George
November 20, 2019 at 4:23 pm
Rod at work wrote:Microsoft Access is the bane of our existence. We have hundreds of Access databases and apps in production today. Allowing for duplication, since its likely more than 1 person uses the same Access db/app I estimate we have about 200 unique Access dbs/apps.
They've been around far longer than I've been in this job. I think the reasons we have so many is the ease of setting one up, the fact that Access is a part of our Office licenses and the speed in which someone and whip together an app and database.
@rod: Let me ask a question: If access wasn't around to solve those business needs, what would happen instead? I mean we can all dream of a world where only professionals develop software, and ideally only after getting 10 years of experience so they know what they are doing. But the issue is that most of these apps solve a business need, for which there was either no budget to have it developed, or IT was too busy to assist.
So if we rule out Access, and rule out professional software, that still leaves us with a business need that has to solved.
Then people grab the most "database" like thing they can get their hands on, which is *shudders* Excel.
Now I love Excel for reporting. But I've seen much much worse Excel messes than I've ever seen Access messes. Excel becomes a hodgepodge of hundreds (or thousands!!) of files that link together in a absolute mess, with references buried deep within cells, so that the whole sheet breaks when someone moves a file. At least with Access you most of the time have a single data backend to look at, and a single frontend to look at.
I would say that in an ideal world, IT would have a resource that could assist their users with setting up proper custom systems, whether it be Excel based, Access based, or Power Apps. This could solve the business needs, while trying to enforce some good programming rules while developing.
You make an excellent point. They used Access precisely because it was a database that the average user could understand. And most certainly it solved the problem they had, especially when it was written. This was even more so because we, as a state agency, have followed the Waterfall approach to all project management, as those it were the Word of God! Consequently, most projects took 2 years to finish, regardless of how short they were. Most customers couldn't wait that long. I really can't blame them for doing it. I hate the fact they did what they did, but looking at it from their point of view I understand why they did it.
My state agency is adopting Agile software practices. This is fantastic! And it is a learning process. We have, after all, a large project management office (PMO) with lots of BAs and PMs in it, all who have for decades spent their time gathering requirements and documenting those requirements down to the minutest detail. The people in the PMO have great power over what happens and when. We're waiting now, for a re-write of specs, so can't really do anything until that's finished. As a developer, I have a good idea what DevOps means to me and the average developer. I also have almost as good an idea what DevOps means to people in IT, especially the DBA. I've never worked in a large enough company/agency that had a PMO until this job. And I don't know enough about DevOps to know how BAs and PMs fit in the process, what their roles are.
You finished by saying that if our users didn't have Access to come up with their own solutions, that they might have used Excel instead. HA! HA! HA! That's happened here as well. There's lots of Excel spreadsheets around doing vital work. Over 2 years ago I worked on a project to remove one office's reliance on Excel spreadsheets. It was a mess before I started on it because about a dozen employees had their own copies of an Excel spreadsheet. Once a month they have to reconcile them all for monthly reports. Once a quarter they had to do a major reconciling of all those spreadsheets and the monthly spreadsheets so they could produce a report to the Cabinet Secretary, who reports to the Governor. So a couple of us wrote an app replace all of those spreadsheets with reports storing the data in a SQL Server database. They loved it, but only used it for about 6 months. Then personnel changed and the new management wanted to go back to the spreadsheets and stopped using our app. That was about 18 months ago. I don't know why and was told not to ask. So, well....
Kindest Regards, Rod Connect with me on LinkedIn.
November 20, 2019 at 4:38 pm
@rod - where is the double facepalm emoji when you need to show your frustration at excel users?
I'm glad you are looking at devops - if you haven't then get yourself a copy of "the phoenix project" - it's a brilliant novel based on how you make devops work...probably one of the books that changed how I look at being a dba
MVDBA
November 20, 2019 at 4:49 pm
We have dozens of small Access databases in our organization (most of them supported by staff outside of IT). A number of them use an MS-SQL back end which provides security and stability to Access. I have also seen some pretty advanced programing in Access. (read data from csv or Excel files, create new tabs in a template Excel spreadsheet and populate with data). Again the main arguments for an Access database are 1. Easy to create a new database for fairly inexperienced users, 2. With VBA coding you can do some fairly amazing things. The main thing, is to split out the front end (forms, queries, macros, etc.) from the back end (tables) and everyone who uses the database have their own copy of the front end. I recently toke an Access database and added some VBA code to automatically fill in values on a form (and the back end table) to lessen user errors. Could the same thing be done with a web interface, yes. But for only a few users it was not necessary.
November 20, 2019 at 4:56 pm
I have found that one of the tricks to selling the org on DevOps or Agile in general is to point out to all of the BAs and PMs that all of their waterfall SLC skills are still in there. The only real difference is that we get good at releasing and that we release rapidly and on schedule. That doesn't change the fact that we still need to do architecture and design work.
Again, my main objection to Access in the enterprise is not well architected solutions. It is the homegrown ones with form references in the queries, no modularization, no standardization, no basic error handling, etc. I have worked on enough of these nightmares that I never want to see another one of them, ever.
Viewing 15 posts - 31 through 45 (of 70 total)
You must be logged in to reply to this topic. Login to reply