|
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).
|
|
|
|
|
I worked on Access databases for some 12+ years building business applications using Access.
The way I would answer the question is that it is not so much that Access itself is an issue, although it certainly has its issues, it's more to do with the quality of person you will be able to hire who is willing to work on Access.
There is a bigger pool of experienced developers and DBAs able to support MsSQL and Oracle etc than Access. Access tends to attract the hobbyist(I use the words "tends to" with caution as there will be outliers and some brilliant DBAs and developers using Access).
Statistically speaking you have a much higher chance of a decent quality database if the database is one that requires a good understanding of data and databases in order to set it up - Access does not fall into that category.
Oracle and MsSQL etc will deter anyone wanting a quick setup - Access actively encourages and attracts users who are completely ignorant of databases and want a 'quick fix'.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Before offering any advice, let's get some details:
- How many users?
- Is the user base distributed, or all on one site and/or network?
- How many tables?
- How many records in the main table(s)?
- How large is the compacted ACCDB?
- How many screens?
- Finally: What is it going to cost to rewrite the application, including on-going maintenance?
As many have stated, Access (like all tools) is appropriate in some venues. It's entirely possible that the application should NOT be rewritten in another technology. The answers to the above questions will help answer the real question:
Is a rewrite worth it to the customer?
The technology used in any application is far from the most important thing. Focus first on the customer's needs, then their wants, then find a solution.
|
|
|
|
|
Access does not Suck!!!
I have been a .Net stack developer since .Net came out and vb, pascal, cobol, even cold fusion, java, etc. before that. Its more about the right tool for the job. Done right ACCESS is totally awesome, but it does have its limits. Like 2GB table size. 25 max simultaneous users. With the right size computers, yes it will support 25 users and record level locking.
You just can't argue to move to SQL and .NET platform without first defining the project. Regardless of perception, SQL and .NET will cost more to support in the long run. Access done right should not cost anything unless development is on going. Maintenance should be a function of the way it was designed, that is to say old data purged at some point. If it can not be for whatever reason, that may be an argument to step up to SQL. But you could still use Access as the front end and just link to the SQL tables.
Lots to consider, good luck.
|
|
|
|
|
I think when it comes to cost it's important to factor in technical debt and the cost of a poorly designed database.
In my experience Access actively encourages poor database design as it is so easy to set up badly.
The point you make about Access being used as a front end is a good one - it's the equivalent of a poor man's WinForms setup(that is it doesn't necessarily require someone with software development experience to set it up) although it does carry with it some problems such as VBA.
I think until Access has something like transaction logging I would not consider it as a database platform on which to base a medium sized upwards business.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Kevin, as with all projects, determine the clients needs, then see if the existing solution meets those needs. Changing a solution just because the current solution 'sucks' in your opinion (and many others I realize) is not a real answer. If it is a small office and Access is doing it's job with minimal issues, then why force them through the change? You could suggest an upgrade path to them of migrating the backend to MS SQL (or Express) or another database (speed, stability) and then going after the front end. We use a custom app that is Access based and are currently migrating to MS SQL as the backend. We've gotten around the updating issue by forcing a copy of the front end to each user when they start the app. That way they always have the latest code and no user is more than a day away from an update or fix (or a simple exit and restart). Personally, if I had my choice, I'd convert the front end to a rich web app with the SQL backend. Eventually we'll get there, but cost is always an issue.
|
|
|
|
|
If they aren't feeling any pain you're not likely to get them off Access. The pain points I've seen in the past are patches that screw it up, locking behaviors that affect other users, and network slowdowns due to Access' habit of pulling records down to work on them. That behavior may have been mitigated by now, I don't know.
The only practical way to approach this in a business environment is figure out where their pain points are. If there aren't any, you're kinda stuck. 
|
|
|
|
|
Here's a thought. Rewrite the UI/logic against the current Access database, but use generic data objects such as oledb which will work with Access, SQL Server, MySQL, etc. There's a real advantage to being able to being able to run an application against more than one type of database.
I've done this for many years with the majority of my company's business apps/modules which can run against Access or SQL Server depending on the user's needs.
A real benefit of this approach is the ability to grab (downsize/ftp) a sql-based client's database for troubleshooting and quick response. (vs. getting a dba involved)
"Go forth into the source" - Neal Morse
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
