MS Access and year 2020 AD, how well are they working together?

[rollerbladegirl]

I thought to make this a personal question, but I now think that this discussion should be available for others to read and learn from.


I recently read someone's bio on this site:

"Career Summary
I've been working on MS Access databases since about 1996--completely self-taught. I am currently working in the [...], managing databases in the [...] world, but mainly focusing on [...]." (Deletions by me.)

and it suggests a question for me.

In the years since the mid 1990's MS Access (and Office Suite in general) seem to have been advertised (pushed) as being persistently better. I do not recall having seen any facts backing up claims of such increase in usability etc.

Thus, my question: MS Access and year 2020 AD, how well are they working together? Not with skepticism as to "how" as in how can it happen, but "how well" is it going for that 1990's software in the 2020's?

Next I read:

"Expertise
MS Access, VBA coding, Office automation"

Access was powerful back then. VBA was powerful back then. By 1999, Visual Studio 6 (not later versions) was a powerful engine of industrial production. How is Access doing now?

This question is for those that have experience with Microsoft Access from the 1990s until now. What is your conclusions about the usefulness of that Access compared to the usefulness of the 2020s version?

To start: I think that it has deleted some of its usefulness. I think that in some of the things that MS added to it, it became anti-secure to a level of anti-useful. Thus, I do not know beyond that as I have not used the 2020 version due to its breaking security protocol locally where I am working.

What experience between the two do you report?

And no. Do not base your response on your opinion that all which is new is better. I have learned that that Boolean is not always correct.

.

[NeoPa]

I believe that MS Access still has relevance in 2020. This is in spite of changes over the years rather than because of them. Yes, there have been positive changes to the product over that period, but there have also been changes that were inconsequential as well as negative.

I'll cover the rest of Office briefly. I believe that has moved well with the times. There are many changes over that period for some of the other applications that are both fundamental and impactful. Just look at Excel. It was extraordinary in the 1990s, but it's even more so now. I use it a fair bit but there are levels of power there I barely ever touch. There are levels some of my Excel MVP friends deal with I don't even know about.

Access is a very different animal though. In the modern world, where portability and ubiquity of platform are not only buzzwords but also set a base level of expectation, Access is not something that fits well into that paradigm, for a number of very solid reasons that are not about it being left behind. What it does and what it's required to do simply don't fit there well. Imagine working with databases, that require full screen real-estate, but on a phone.

Access works as a database application (Obvs!), but within that scope it's also a RAD (Rapid Application Development) tool. Many have tried to duplicate that and none has come close so far. What Access makes available is still very much required and needed across many situations in today's world. Mostly in business but also in hobby/personal situations too.

It's often mistaken for a Jet/ACE solution. That is to say a solution based on a very much lower level database system than what most are used to in today's world (SQL Server, Oracle, other fully fledged database servers). Jet/ACE is not a database server, it's a database engine - one of relatively limited capabilities. Great as an entry-level offering but highly criticised as not being a robust server. Quite rightly too. It isn't. However, the criticism tends to be directed at Access instead of Jet/ACE so people normally see Access as a toy. This is a totally inappropriate assessment.

Access is still so prevalent, even today, because it does the job, and generally does it very well. Even where Access is outlawed due to the misunderstanding of how everything fits together by IT departments & upper management who don't appreciate the full picture correctly, it is still found a great deal. This is because it's a powerful tool that does what people need. Add to that the fact that, compared to other development systems at least, it's relatively easy to pick up and make progress with, and you can see why, in spite of all the negative marketing, it won't lie down and die.

In my world, as an experienced developer who uses Access in conjunction with Excel where necessary, I see many scenarios where medium / large businesses have the main part of their business running successfully and reliably in Access. Generally that's using a proper database server as the Back End, but in one of mine we have multiple branch BEs in Access still. I wouldn't plan it that way, or recommend it even, but it does work and the company has a >£100 million turnover.

I haven't touched much on the VBA side of things. This is a static language now but that brings with it stability. When used properly & well it's very powerful. I won't say as powerful as c or any of its derivatives but nevertheless powerful.

While I would be happy to see the abiliity to use something like c# or c++ integrated into Access as an alternative option my feeling is that the natural marketplace for Access developers means there would be a very small minority that would care about such a change considering VBA does pretty well everything required anyway. That's just my feeling from what I hear around. BASIC-type languages have always seemed to be easier for the untrained to pick up it seems.

Interesting question RollerBladeGirl. Normally this might not be considered to be appropriate for the question forum but I will certainly allow it as it may well prove very intersting.

[rollerbladegirl]

Thank you NeoPa.

This is what I was looking for. A descriptive answer that I and others can use to judge previously great software vs current highly advertised software.

I think that you are saying that Microsoft Access from the 1990's is still powerful for small businesses that do not require highly complex databases.

Your answer helped me to decide to not use beyond 1990's MS Access for now. I am looking forward to being part of developing a widely accessed system and I have wondered about how to approach it. Way back in the 1990's when MS software was so solid and reliable, there was a company that advertised world-wide among hackers that their system was hack-proof. It was built with Microsoft Windows NT4. Their system literally became known among hackers as an un-hackable system. That company used MS Access for at least one industrial contract. I know of this. I have wondered if MS Access was still powerful. I read your post. I think that it still is. I do not need a D9 Caterpillar Dozer to move a hand full of dirt. Just the right tool for the right job.

You detailed a lot there. I look forward to others giving their experiences.

I apologize for this not being a strictly technical question, but it was a question that I have been wondering about a technical subject for a while.

Thank you NeoPa.

[datamvp]

I have been using MS Access for the better part of 28 years. In my experience it is the premier front-end tool for building desktop databases, although I like SQL-Server as a data respository. While Microsoft has added some functionality, the core program has been more than sufficient since the early 1990's. As far as Visual Studio goes, while it is a good overall programming tool, it can't touch the usefulness of any database design tool, much less Access. Until a tool superior to Access as a database design application is invented, Access will remain the foremost product of its kind.

[rollerbladegirl]

Thank you datamvp.

I get another solid answer. This is a great site!

28 years! That means (if I have this correct) that when Microsoft released Access version 1.0 on November 13, 1992 you got it and started then. That was before Access 2.0 came out for Windows 3.1. Interesting.

I am trying to learn C++11. I would like to be able to use C++11 to read and write and append records in MS Access. I would like to know which version can easily handle 1,000 records, and which version can easily handle 10,000 records, and which version can easily handle 100,000 records. These might be via a base 100/1000 local area network (in-house and not via the internet). This could address my original question of how MS Access is doing in 2020 AD further.

You said

"although I like SQL-Server as a data respository."

How does that affect my previous questions? Would SQL-Server be better as a strictly data "repository," or should I still be looking at some version of 1990's Access?

Oh, to know such things.

[NeoPa]

RollerBladeGirl:
I think that you are saying that Microsoft Access from the 1990's is still powerful for small businesses that do not require highly complex databases.

That's not quite what I was saying. I still believe MS Access is a very appropriate tool even for medium & large businesses. In such cases I wouldn't recommend using the Jet/ACE data handling capabilities as they are limited - particularly as far as security goes.

However, as I was trying to express earlier, Access can be used as a front end (user-facing) interface to data stored in a more reliable data depository. Such as SQL Server and Oracle for instance. Much of the security, and also much of the logic, may be incorporated by top end experts within the server side (back end) of the system while Access can be used in order to give users controlled interaction with that data.

Typically the ability to produce something which users can use from data already available in your server repository is so much more straightforward that it can be produced orders of magnitude more quickly than alternative approaches.

From what you say in a later post your interest is mainly in something that just works in your company offices. Many are looking for pretty software that works across different platforms. The latter is where Access could be considered weak, but the former - what I believe you're after - is playing to the strengths of the Access product.

As far as my friend is concerned (an erstwhile MVP for Access for many many years) you are spot on. He's used it since it came out in 1992. You couldn't find a more experienced person for Access - except maybe there is one who's been working (for MS) creating Access since way back then too. I guess being on the team trumps being a fan, even if they've been a fan since the very start.

As to your question again, if you have a project of any real size then Jet/ACE (The data handling part of Access.) is not advised. SQL Server, on the other hand, would seem to fit perfectly. I can't talk about other RDBMS Servers, like Oracle for instance, as I don't know them well enough. I'm sure they'd do an equally competent job. for any of these though, Access is still a good option to interface with the users. IE. The desktop software could (and probably should) be Access.

As for using C++, that is certainly an option. I'm afraid you'd need to find some supporting libraries to go that route. I doubt you'd get the speed and reliability of development that one would from Access of course.

Keep posting if there's anything you don't understand.

Best wishes for the project whatever you decide.

[rollerbladegirl]

Thank you NeoPa.

I want to use it with XP Pro and XP's sp2 (not XP sp3).

I have been studying SQL servers since your post and I think that I have chosen SQL 7.0.

I chose SQL 7.0 because I think that I might not have to call Microsoft every time I crash it and reload it. Or not have to call Microsoft if I change the server configuration or the entire server. (?) Just put in the CD Key and get going again. I do not know, but maybe that is how it works. I would like the latest SQL that I can do that with, and it looks like that is SQL 7.0 . I really do not know this yet. But, I have start somewhere.

I read that SQL 7.0 with it's own Service Pack 3 works with up to XP Pro.

I read (on a Microsoft site?) that SP4 for SQL 7.0 does not work with XP Pro, but then on another site (Here!) I read that it does. Reference: https://bytes.com/topic/sql-server/a...7-0-sp4-xp-pro . I like this site!

Back to Access. It looks like from your post that I might want to use Access 97 or Access 2000. It also looks like I might want to use SQL 7.0 sp4.

I am currently planning that the users do not have access to the MS Access (97 or 2000) and that the users do not have access to the SQL 7.0 .

The system seems to currently be like this:
[User] - to - [Web server] - to - [Data]

The [Data] system might be like this:
[Web server] - to - [Access (server?)] - to - [SQL 7.0]

Maybe?

It looks like in 2020 AD, Access can still being used as a powerful tool.

[NeoPa]

Hi RollerBladeGirl (RBG for short).

I'm a little confused. Perhaps I didn't understand your earlier posts clearly enough. It seems like you have some restriction on the software you plan to use. I was under the impression you were using everything up-to-date.

Generally speaking I wouldn't recommend the use of outdated software nowadays. Quite apart from the security holes because they were mostly built in a time period when the threats were nowhere near as sophisticated, some of the newer software has increased in capability to quite a large extent. SQL Server is a case in point. I was using SQL Server 2000 back in the day and it was fine - already a big jump beyond 7 - but it didn't have CTEs for instance.

Can you share why it is you have to work to these restrictions? I couldn't begin to warn you of all the possibilities of problems you could expect to encounter. You would certainly want to avoid any contact with the internet on such systems.

RollerBladeGirl:
The [Data] system might be like this:
[Web server] - to - [Access (server?)] - to - [SQL 7.0]

Hmmm. If this is web enabled then it wouldn't typically be using Access - certainly not between the web and the data. I think there may still be some serious confusion here. In a scenario where the user interfaces with a web page they aren't interfacing with Access.

[rollerbladegirl]

I did not want the user to be using my MS Access.

I would have a program that would be running on their computer (like a game or such).

My program on their computer would access my server itself maybe via an encrypted connection.

My server would not allow them, or my program that is running on their computer, or anyone else on the internet wired or wireless to pass through. It has been done. It can be done.

My server would gather data that had been sent from my program running on the user's computer, then process that data, then send that data to another (internal to my location and firewalled) server (maybe an Access server?).

The Access server would then look at a database and decide what to do with the information (read, write, append, update, etc.) on the data server (SQL?).

Then the process sends data back from the Access server to the web server.

Then the web server decides what to do with the data, send it on to my program that is running on the user's computer, or do something else.

The user sees my program on their computer and interfaces with it. They do not see beyond that. If they ping trace or whatever they decide to do, they do not get past my web server. Whatever they see, if they think that they see beyond it, could be a false positive. There is a process that does that which I know of being used more than 20 years ago.

If my program is a game, then they see the result of something and that is all. No further interaction is allowed.

I know of such processes from years ago. I studied TPM (of which many manufacturers came up with versions of their own). I studied EFI and UEFI (a lot of history there). I studied telemetry. What I previously described, locks all of that out. I am back to using low cost systems that can handle [?] much which I am now studying.

By the way, I have sp4 for Microsoft SQL Server 7.0. Where can I download sp1 and sp2 and sp3. I would like to have this collection. Microsoft links show sp4. I am looking elsewhere for the others.

Thanks.

Security is paramount. I hope that you understand.

It still looks like Access from the 1990's is valid for 2020 AD. Add to that SQL from back then. Nice.

I get it now what you are asking (maybe). The editors in the office would have use of Access to input and change things as required manually. Not the end users out on the internet. Does that make it clear?

[twinnyfo]

rollerbladegirl:
Security is paramount.

Yeah - we understand. So, we also know that MS Access, inherently, is not the most secure application for data storage. I work with "sensitive" material--not classified, but stuff that not everyone should have access to. Because I know of the inherent weaknesses of MS Access as an application, instead of relying on things such as passwords within Access or other trappings that can bog down one's DB, I rely instead on the platforms on which my MS Access DBs are housed to manage security.

For example, we use authenticated smart cards for access to our systems--as well as access to our network locations and SharePoint pages. If one does not have a smart card and PIN, they simply can't get into our systems (or at least the folks who are able to access these systems are far too advanced to care about the data that I am concerned with). I use the user authentication to control who can navigate to my network locations.

Within all this, there is an inherent level of trust that must be shared by all our users. I must trust that my users aren't going to share the information they access--but I have no real control over that. However, I know, very confidently, that no one who ought not to look at my data is looking at my data. We have tested this extensively and the only "other" people who can even see the data are the "super admins" who have the ability to override any permissions I may set--but, again, they're way too busy to care about the data I'm using.

So, I have found that while MS Access is not the most secure application in itself, there are ways to mitigate some of those security issues.

Thanks for the discussion. Hope this hepps!

[datamvp]

rollerbladegirl

The actual date of Access's introduction was October 28, 1992 see: http://accessmvp.com/Arvin/AccessOrigins.htm I began using it 1 day later on the 29th.

Access is actually 2 applications, the front-end which allows building and using forms, reports, code, etc. on the desktop, and a back-end, also know as JET or ACE which contains the data. As a front-end, Access is world class and unequalled or unexceeded by any other application not requiring the Internet.

You stated that you do not want your users using MS Access. I would disagree. That is precisely what an Access front-end does better than anything.

The back-end, while good, does have some limitations. Serious security being one of them. I have built some secure databases using Access and Active Directory, but a good hacker could probably breach them. The back-end also fails with large numbers of users. The stated limit is 255, but in practice, I doubt that number is accurate. While others have reported 200 concurrent users, my experience, with a well designed back-end is under a hundred. The front-end, with a server based back-end such as SQL-Server can handle thousands of users.

[rollerbladegirl]

Thank you all.

Thank you NeoPa.
Valuable historical experience compressed into a few paragraphs.

Thank you twinnyfo.
Added aggressively isolate Access 97 and SQL Server 7.0. from the internet.

Thank you datamvp.
Added maybe just use Access 97 as an in-house (local) access point for editor personnel in the office. Maybe bypass it in the exterior user processes. Use it for updates to the SQL Server 7.0 only.

I thought to ask a simple question. I received a compressed historical account of direct use and the resulting opinions based upon experience. Thank you.

Thank you.

[twinnyfo]

Anytime, rollerbladegirl!

You seem very well engaged on the forum with how you interact with others and the site in general. It's good to have you on board! We hope we can be of much additional service; and we also hope that we will be able to learn from your experiences. This is a collaborative site and I have learned much of what I know from folks like NeoPa. Now I am able to share with others the things I've learned and developed over time.

Grace and peace!