|
In addition to what John said.
Access is single user - MS has documentation stating the fact somewhere. This should be the single most compelling argument to change to an n# tiered solution.
Access is an OFFICE tool - that argument should kill all further discussions. I don't know the recent history of MS Office upgrades but in the 90s every upgrade would break the applications I had written in Access. Moving to SQL Server eliminated that horror.
Oh yeah and Access sucks!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Access is not single user - based on long experience I would set an upper limit of 15 concurrent users before I would insist on the back end being migrated to SQL server
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: Access is not single user
I did not know that, it has been 15+ years since I used it. Does it still use page locking instead of record locking?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'm not sure of the latest versions, but I think record locking has been implemented - I've not done any Access development for about 6 years (though I still use some databases I created back then for personal use.
RecordLocks Property - Access
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Access is designed to have up to 255 simultaneous readers. It supports about 5 simultaneous writers. Access 2016 runs Access 2010 code with no changes.
Interesting tidbit about MS-Access - Access 95 and 97 actually implemented more of E.F.Codd's relational database rules than Oracle and Sybase.
Access sucks in the wrong environment. In the right environment and with a proper problem domain Access is the right tool. No, I wouldn't use it for a medium or large database project with multiple users but for a single user tracking a small number of items it works great. You can even connect to it via the OleDatabase classes in the dotNet framework.
|
|
|
|
|
obermd wrote: but for a single user tracking a small number of items
I used it as a database for up to 10 users in the 90s along with SuperBase, it sounds like Access has come a long way since then.
I very quickly converted to using SQL Server and never looked back.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The assumption (overall) seems to be that Access is being used to "build" business apps.
The fact is, it's quite reasonable as a "user" tool for querying "back-end" "big" database systems like SQL Server, Oracle, MySQl, etc. For ad-hoc queries, Access is much simpler than SQL Management Studio, BI, and the like.
It can be used to analyze practically any data source including Access, Excel, CSV, SQL Server, Oracle, DBFs, ... and "join" them.
A knee-jerk rejection of Access is short-sighted ... particularly when it comes to "another quicky report request" that any thinking user could handle.
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
So why not move the BE to SQL Server? There may certainly be a business case for that.
Stability and speed. In those respects Access, as a database container, is certainly inferior to SQL Server. And then there is the ease of creating SPs using CTEs etc that is way better than the Access Query Builder.
But as a tool to build user interfaces, ie forms and reports, for a custom solution it is very cost effective and in the hands of a good developer there are few restrictions.
So what is your business case for rewriting their entire application?
|
|
|
|
|
You talking to me? If so, did you even read what I wrote?
"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal
|
|
|
|
|
No sorry Gerry, I was commenting on the original post.
|
|
|
|
|
I agree with you a 100%. Even if the customer cannot afford to buy the full fledged MS SQL Server, using the Express version is still better than staying with the Jet engine.
A lot can be done with MS Access and SQL Server, and and a whole lot faster than by using .NET, specially if we are talking about an application for that basically does CRUD operations.
I think the original poster is just a hater that just can't figure out MS Access & VBA.
Saludos!
|
|
|
|
|
Member 11652832 wrote: I think the original poster is just a hater that just can't figure out MS Access & VBA.
That's not true at all. I've been doing VBA on side projects for many years. Anyone who knows both Access and .Net also knows that Access/VBA is very limited compared to .Net.
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Limited, yes, but it doesn't make Access a handicapped product. It is not a one size fits all for sure.
In my personal experience, the magical number for concurrent users is around 22, though you will start to feel the weight at 12 concurrent users.
As for the limitations of the database again, move to SQL Express. The benefits are endless, and you still have an environment that allows for fast development.
Trust me; there is still a lot of magic in MS Access and BTW, despite some gossips going around; Microsoft DOES NOT have plans to discontinue this product, as there is indeed a very large user base making use of it, specially as a front end.
Fast and dirty? Probably, but never trashy. You can make great looking applications with it.
|
|
|
|
|
MS may have just wasted 6 year of development on Access Web apps which they have now discontinued they are now full steam ahead on developing Access Desktop.
Now that Azure allows you to host an Access desktop app in the Cloud there was simply no need for a dedicated Sharepoint hosted version of Access and about the only time Access will not cut the mustard is if you need to cater for anonymous users like on your typical public access web site.
|
|
|
|
|
Are you claiming that MS Access performs/runs faster than .NET application doing the same thing? I don't agree with you on that. In my experience, Access processes ran significantly slower compared to .Net equivalents.
Unless you meant the time to develop/implement -- for example, whiping up a front-end app, I think you have point in which creating an acesss front-end typically takes less effort than a .Net front-end.
|
|
|
|
|
I never said that. I said FASTER DEVELOPMENT TIME. Of course a .NET is going to perform FASTER, though not necessarily BETTER, as this is very subjective and depends very much on the business needs.
In other words, yes .NET is better; but is that what the customer needs or wants? You first have to find out if the application fulfills their needs and if the changes they recently required are important enough that it would require not an overhaul but a completely new product.
In my experience, if a customer has been using a certain tool for years; our responsibility as analysts is to determine whether this tool will grow with the users needs. That is all you have to find out. Is their database reaching the maximum recommended size? Are there over 20 concurrent users? How much longer is Access being supported?
You answer those questions and you'll find out whether you need to create a new .NET application, overhaul the existing one, just leave it as it is or any other solution in between.
|
|
|
|
|
The faster speeds of the dotNet framework aren't always of benefit to the user. Sometimes they are but many times the system is sitting idle.
|
|
|
|
|
Good point about Express and also note that the developer version is now free.
The combination of Access and SQL Server is quite powerful and RAD.
I'll often prototype a process using VBA recordset (easy to debug but slow to run) then convert the process to use T-SQL.
Although I usually use Pass Through queries (PTs use ODBC) to execute SPs, it's nice to know MS have reversed their decision to deprecate ADODB (and therefore ADO).
Once you replace your BE with SQL Server then there really isn't much to hate. Even if I do need to use .Net you can always build a COM enabled DLL to consume in VBA.
My observation is that some developers, including myself, would love to have a client pay for their time to learn or improve just employ their C# skills. Unfortunately there is rarely a business advantage for the client.
Apologies to Gerry for hijacking his thread.
|
|
|
|
|
Mildly confused here. I have written several .Net applications which suck their data from Access, because that's what the client wanted. Up to half a dozen users is fine, as long as it's on a decent piece of iron. I do draw the line at VBA, though, as that is an anathema to a sensitive soul. The only downside is, as JSOP pointed out, security beyond a fairly trivial level.
Also, it is very little effort to upgrade the database itself to SQLserver, using the MS tools provided.
|
|
|
|
|
"Access really sucks"?
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
Access does not fully implement the SQL standard leading to awkward query design. This leads to long unproductive hours debugging even basic joins like the left before the right and then the nested parentheses.
Without syntax highlighting, it really gets my goat.
Translated to user speak; "whatever duration you want the project delivered, please double it."
|
|
|
|
|
I agree with you on the SQL issue and this is a very good case for moving the BE to SQL Server.
Developing complex queries in SQL Server is just so much easier. CTEs rock! I'm currently building a very complex report using Access without SQL Server and it really does suck trying to do this in the Access Query Builder.
But that doesn't mean you should trash Access for building the UI.
|
|
|
|
|
I vaguely remember that which is why I would use the query designer which worked most of the time until you needed something more than a simple join.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Agree with you there, the awkwardness of simple SQL querying in Access is legendary. With great respect towards cool features like the crosstab queries, the pivot view, the query designer and the Relationship manager, the embedded Forms and Reports which are all great stuff.
The lynchpin though is still the awkward querying and the way Microsoft treats Access development as a misfit orphan. I cut my DBA teeth on MS Access 97. It was great going till v2010 and it all went downhill thereafter.
I'll not even touch the VBA part, it gets me so when one realizes how much Excel has received and what Access has been taken away from.
I believe that the time has come to part with Access as a serious business development tool.
Today in 2017, if a client is looking at enhancing Access to serve business needs, I always remind them of the proverb 'The professional uses a sharper axe to fell a larger tree, where the dilettante makes do with his trusty blunt one'
The difference is in the style and execution. The latter believes that serving the current set of needs is a great achievement. The former believes the forest lies in wait for him. He believes he has just begun.
|
|
|
|
|
What you want is "We should rewrite the app"
But, they will say, it works! They got a point here, haha...
If, for example, all they want is to "add 1 button", then rewriting the whole app for that is overkill...
On the other hand if the app is undergoing regularly maintenance and development, how much time will it takes to get the new WPF app is a critical argument. As well as the on going benefit after that, such as better performance, faster developement, easier maintenance (aka less long nagging bug).
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
