|
I would argue 2 is incorrect. Access can be multiuser, but you have to implement it properly.
You need a central error handler that retries particular classes of errors
If (ErrorNum < 3000 Or ErrorNum > 4000) AND ErrorNum <> 2448 Then
'Process As Error
Else
'Retry up to 60 times
(60 times for the same error no) before presenting any error to the user.
Also you need the code and data mdbs seperated into different databases.
This works like a charm for multi-user.
Mind you, I have moved on from Access long ago.
|
|
|
|
|
2 is utter BS - I have had pure Access apps successfully support 20 people editing at a time, and Access with a SQL back end supporting 50+ users
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: I have had pure Access apps successfully support 20 people editing at a time, and Access with a SQL back end supporting 50+ users
Ditto!
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
As I often say in these circumstances, it's not the tool that is used that is usually the problem, but the tool that uses it.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
I think he refers to the fact that in the old times access used file locks.
If I recall correctly Access is using record locks since 2007.
Might be wrong about that though, I try to avoid Access.
|
|
|
|
|
I have worked with Access since v2 and it has never been single user if set up correctly. It always creates a locking file (ldb in earlier versions, laccdb in later versions) to keep track of multiple users.
If it appeared to be single user, you had not set it up correctly. Splitting the system into front and back ends, with the back end on a proper network share, and each user having a local copy of the front end was always the way to go and worked well (and presumably still does - I'm a SQL DBA now) , so long as you were aware of its limitations.
As I said earlier, if you were looking to more than 15 concurrent users, pure Access is not the way to go, but it can still be used successfully to front-end a SQL databases.
I rewrote and maintained a 50+ user order processing database for a previous employer that has Access front end to SQL back end over 10 years ago and I discovered recently that it is still in use and still working fine, handling several million pounds of orders per year.
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Chris Quinn wrote: If it appeared to be single user, you had not set it up correctly
That's always the key, isn't it?
|
|
|
|
|
Depending on how the front end is implemented, it is all too easy to delete vast chunks of data that suddenly become unrecoverable. An Access front end (used to) automatically carry out database operations such as delete without any logic.
I had a customer hit Ctrl+A then delete and wipe out most of their billing data as the data on screen was pulled from a table rather than a view. It took about 4 weeks to recover most of their data. Understandably they weren't hugely happy with this, but the application was built fairly badly (before either my predecessor or I got our hands on the code.)
The app was multi user with an MDB back end and an MDX (Access executable) front end. This kept the code hidden from the customer, but also meant we had to implement releases to update it.
So - if the GUI hasn't been implemented properly then you may be just as quick building a new front end with the correct logic layer, and a suite of APIs in the background.
Also... good luck!
|
|
|
|
|
I just posted basically the same thing, lol.
|
|
|
|
|
Thanks John
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
 John Simmons / outlaw programmer wrote: 0) Access is buggy. After you've been in the app for an extended period with a lot of objects, the editor stops working correctly, which means you have to exit Access and restart it.
If you're not trying to change the systems and just using it Access is stable.
John Simmons / outlaw programmer wrote: 1) Access is not secure.
We don't know the size or nature of the business. For a small business with only a few employees it may be sufficiently secure.
John Simmons / outlaw programmer wrote: 2) Only one person at a time can have an Access database open for editing at a time.
Only an issue if you need to make changes. For a system that doesn't require changes this isn't an issue.
John Simmons / outlaw programmer wrote: 2) Access is limited in terms of what if can do - Access 2010 specifications - 3) Access[^]
Is it meeting the needs of this company? If so then these limitations are irrelevant.
John Simmons / outlaw programmer wrote: 4) Access is not intended to be used as an enterprise database solution.
Define enterprise in this context.
John Simmons / outlaw programmer wrote: 5) Finding skilled Access developers is becoming more difficult as time goes by because the money is in SQL Server.
Iffy statement on being able to find Access programmers. A good programmer who's already used to VBA can pick up Access relatively quickly.
John Simmons / outlaw programmer wrote: 6) Access sucks.
Opinion and not defendable for a small business. Access has a lot of features that make it ideal for a 1-5 person user environment.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
