MS Access vs other tecnologies (1 Viewer)

Hi Guys

We have been using MS Access 2010 for years and now my boss wants to know from me what limitations we are currently facing and do we need to start using new technologies ? What new technologies will you prefer over MS Access? (Java,C#,SQL server,Net etc...)

Can you guys please share your thoughts on this?

Many Thanks

My understanding is that the limiting factor for most Access implementations is the number of concurrent users. Once that gets to a dozen or more, you can start hitting problems.

There are about 100 users on my floor who have been using the Access database I developed for them and they are having no issues so far apart from the speed.

Once that gets to a dozen or more, you can start hitting problems.

Click to expand...

only if the code and/or tables/relationships are not well designed. I have apps out there with well over 70 concurrent users using an access backend.

@aman - to determine if access is a limiting factor, first you need to decide where you are going.

access consists of (or should consist of) a front end and a backend. The front end sits on a users machine whilst the back end is on network somewhere. Also the backend does not need to be access, it could be sql server, MySQL, dbase etc. (in which case concurrent users can be in the 100's or 1000's) You can also use azure or sharepoint for a web based back end or even link to other data/apps using API's (e.g. for sms, using gmail)

The primary limitation of access is that it only works on windows machines. There is not a version available for iOS or android. But you can install virtual machines on those devices to house windows which in turn can then run access.

One of the benefits is cost - develop an equivalent application in html/java/whatever will cost 10-20 times more because of the significantly longer development time.

So decide where you are going - do you need a bus or a train?

no issues so far apart from the speed.

Click to expand...

this indicates either a slow network (which will affect everything) or code/design that could be written better. To minimise network traffic you need to design like a web page. You also need to take into account the proper use of indexing.

Here is a couple a links I wrote a while ago

https://www.access-programmers.co.uk/forums/showthread.php?t=291269

https://www.access-programmers.co.uk/forums/showthread.php?t=291268

Thanks CJ_London, What are the advantages to migrate the backend from access to sql server? Are access reports better than SSRS or vice versa?

SQL Server is a much more secure database than access. It is also less tolerant of things like badly named fields and poor structure etc.

There is a significant cost overhead if you need an enterprise level server licence.
It deals with large amounts of data without the restrictions on size that Access has.

You can also create complex stored procedures and views to return datasets and perform data manipulation where the processing is done server side, reducing the overhead on the local machine and network traffic, if well designed.
You can automate tasks using SQL Server Agent which can run without someone having to have Access open somewhere.

Backup and restoration are very much better handled and inbuilt by design.

Thanks Minty, How does the Backup and restoration work in sql server?

There are whole books written about it - so this is a brief overview only.
You can set up automated transaction snapshots , and separate automated complete data backups.

These allow you to take a regular (hourly/ whatever period you like) snapshot of the database system that you can roll back / forwards to from your last full backup.
Generally these snapshots are quite small files that allow a quick restore point.

Full backups are exactly what they say on the tin. You can restore these in pretty quick time if your system is set up correctly.

As a guide our main database is 3.7Gb in size. Transactional snapshots grow through the day from a few 100Kb to about 13Mb.
Our Main backup is 2.6Gb is size.

The really clever thing is the speed of backup. When done to local disk a complete backup takes about 2 minutes, and snapshots take literally seconds.
This is on a relatively High Spec VM.

I am convinced it must use black holes and mirrors to achieve this speed of backup.

Aman - I'm with CJ on the speed issue. Your network speed will make a difference. The size of your BE file will make a difference sometimes. The most common problem with a slow DB is going to be improper or insufficient indexing and failure to do proper maintenance such as a compact & repair on a regular basis.

Indexes are important because they provide Access with information it can use to make a faster search for any query that has a name in the list of defined queries. For a dynamically created query string, Access can't so easily analyze & optimize, so if you have common queries with some dynamic features, consider "Parameter Queries" as a way to let Access optimize even without knowing the exact criteria.

Compact & Repair (C&R) is important because normally, a freshly-cleaned database allows you to grab one disk buffer (4KB) at a time and read through all of it. But as time passes, records can become deleted or obsolete. If they don't take up the entire 4KB, then you increase the "clutter" in the buffers so that your efficiency of reading the contents of the buffer is now less. For records in the 100-200 byte range, a freshly C&R'd database will have 20 records or so in a single buffer. After wear and tear due to deletions and other factors, you might have only 15 USABLE records - a 25% loss of storage efficiency.

Even if you DON'T delete records normally but rather just mark them as obsolete, it is worth considering whether you should archive obsolete records to another table. If the obsolete records will not participate in the day-to-day searches, it becomes yet another issue for loss of efficiency. If you had that putative 20-record disk buffer and have 10 obsolete records, you now have 50% search efficiency as long as the obsolete records are still in the same table. Archiving followed by a C&R will make your storage efficiency get back to its maximum.

I'm sure there are other issues to consider, but indexing and a database maintenance period to include C&R will HELP you keep your DB in good shape.

You can use sql server express to try it out.

no limitations on current users, make use of stored procedures etc.

main difference between express and full version is volume of data that can be stored (express=10Gb) and number of cores used - express is limited to one, same as access, full sql uses four. But there can be an issue - everyone piles into using sql server and although there are four, they are shared out amongst all concurrent users (or to be more precise the services they are using at any one time). If the server isn't managed properly, response times go down. Whereas each user has their access 1 core available to them.

with regards reporting, in my opinion, access reports are much easier to create than ones in ssrs

addendum to Doc's post.

SQL Server/Express still needs to be compacted, for the same reasons Doc states. It is just done differently and in a more targeted manner

The Express version of SQL Server is free & may well be MORE than enough for your needs

Further benefits include:
a) stability - crashes should be a thing of the past...
b) additional security
c) can use scripts to update the SQL database - excellent for use when deployed to clients

As for compacting in SQL server, you 'shrink' the database

I would also agree that trying to do reports in SQL server is more difficult than in Access though the built in reports are very good.

I would second that if you want to upgrade in the first instance move the backend to SQL Server - express should be fine.

If you have managed to develop a working Access System for that many users this should be within your capabilities. Note you want to give plenty of time for this - it is slow to implement new structure at the same time as learning how to do it!

This has the advantage that internally you will be able to continue with the access front end and if you really want you can design a separate Web front end. But web front ends are a more complicated project than changing the backend.

Note it is quite possible to design a web front end concurrently to your use of the access front end. This means there is not much to loose from experimenting with web interface. Web interface design is significantly harder than access interface and will be unlikely to be as rich. (This is often a complete killer for transition to the web of backend systems)

Clearly you do need to do something to try and improve speed. Optimisation within Access is however probably your easiest option and should be tried first. Moving the backend to SQL Server Express would be a good first step.

Note it is quite possible to design a web front end concurrently to your use of the access front end.

Click to expand...

But don't use the access web app - it is about to be deprecated. Any system written in it will need to be rewritten by I think the middle of next year

I should add that a good combination is both Access and Web Apps linked to the same SQL Server. This really would give you the best of both worlds and also means you don't need to lose all that development you've put into the existing interface.

repeat about web apps - there is no point in developing anything

https://blogs.technet.microsoft.com...log/2017/03/31/access-web-apps-to-be-retired/

Thanks aman for posting this thread. This wis what exactly I am lookin for.

CJ_London, Just wondering if we use azure or sharepoint for a web based back end then how can the frontend be a web based? Does access 2010 provide any functionality for web based frontend?

CJ_London said:

only if the code and/or tables/relationships are not well designed. I have apps out there with well over 70 concurrent users using an access backend.

@aman - to determine if access is a limiting factor, first you need to decide where you are going.

access consists of (or should consist of) a front end and a backend. The front end sits on a users machine whilst the back end is on network somewhere. Also the backend does not need to be access, it could be sql server, MySQL, dbase etc. (in which case concurrent users can be in the 100's or 1000's) You can also use azure or sharepoint for a web based back end or even link to other data/apps using API's (e.g. for sms, using gmail)

The primary limitation of access is that it only works on windows machines. There is not a version available for iOS or android. But you can install virtual machines on those devices to house windows which in turn can then run access.

One of the benefits is cost - develop an equivalent application in html/java/whatever will cost 10-20 times more because of the significantly longer development time.

So decide where you are going - do you need a bus or a train?

Click to expand...

Just wondering if we use azure or sharepoint for a web based back end then how can the frontend be a web based? Does access 2010 provide any functionality for web based frontend?

Click to expand...

If using Azure, you can develop a web based front end - but it won't be access, you would need to develop using something like visual studio. Probably the same with sharepoint, I don't use it so don't know

You connect to sql azure in the same way you would a LAN based sql server/express - same driver, but a different connection string. The thing to remember is that its primary firewall is specifying which IP's are allowed access - which means you need fixed IP's, not dynamic IP's. You can open up to dynamic IP's but you will then have lost your primary defence against hacking. If you are a collection of home users, each user can request their ISP to provide a fixed IP, usually involves a small one off charge. But unlikely they will be able to connect if sitting in a wifi hotspot somewhere. There are ways round it but you will need to write an API

Access did provide functionality for a web based front end but a) it was very limited with what you could do and b) it is being fully deprecated next year.

CJ_London said:

this indicates either a slow network (which will affect everything) or code/design that could be written better. To minimise network traffic you need to design like a web page. You also need to take into account the proper use of indexing.

Here is a couple a links I wrote a while ago

https://www.access-programmers.co.uk/forums/showthread.php?t=291269

https://www.access-programmers.co.uk/forums/showthread.php?t=291268

Click to expand...

I read through the links and I want to ask about one point you raise.

I see the suggestion of not having forms bound to large tables, but rather only access the specific row needed.

I have never implemented this method because I have a few questions. I'm hoping someone can help explain the reasons behind it better and how to implement it.

1) At first glance, it makes sense that loading in one record rather than loading in 10,000 records is going to be a lot faster. However, based on my understanding of how Access loads data, I don't get this. My understanding is that when you request a specific row in SQL Server (or any database) then the server acts proactively to retrieve the requested row so only one row goes down the network (or internet), whereas JET/ACE are simply acting as a file share, so requesting one row requires the entire table to be transferred and it's only the client computer doing the filtering to display the row we need. If that's the case, why is basing a form on a single row an advantage? The entire table has to cross the network anyway.

2) In addition, even if the entire table has to be transferred if the form is bound to the unfiltered table, then loading the form should be much slower, but once the form is loaded and the data is transferred and cached/loaded into RAM or wherever the data resides, retrieving one row from a table that's already been transferred should be comparable or even faster than retrieving the row from the server each time the user navigates.

I am sure that these questions are because of a lack of knowledge but if someone can explain it, I would be most grateful.

As far as implementation, I can think of two ways to do it:

1) Using a completely unbound form and using DAO to load in the data, edit the data, add and delete. This sounds like a LOT of coding which really diminishes the benefits of Access

2) Binding the form to a query that only returns one row using a WHERE in the primary key. I get the concept but does anyone have a fully functional demo of this? I want to see how additions work (i.e. if your form is bound to a single record, then how do you ADD to that recordset...?), how does OnCurrent code work (I guess it gets moved to the code that loads the data?) how about subforms on the form? How and when does that data load? And so on

Once again, thanks a lot. I am sure that many others can benefit from this information!

SHADOW