tag:blogger.com,1999:blog-2328222207349876984.post5952491739277124857..comments2024-03-02T15:36:45.785-08:00Comments on SQL Tact: Don't Hitch Your Wagon To MS AccessUnknownnoreply@blogger.comBlogger13125tag:blogger.com,1999:blog-2328222207349876984.post-36886406751308254652018-03-15T19:26:58.248-07:002018-03-15T19:26:58.248-07:00Everybody is entitled to their own opinion. I star...Everybody is entitled to their own opinion. I started using access at the age of 28, and now i'm 47 yrs old still doing access dev with mysql backend. for small/medium enterprise access fits the bill and even large ent with low budget. Access runs fine in the cloud via remote desktop.Anonymoushttps://www.blogger.com/profile/03700326714842255213noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-17261333159773901632018-02-04T14:32:18.334-08:002018-02-04T14:32:18.334-08:00Anonymous-
About once a month my team gets a call...Anonymous-<br /><br />About once a month my team gets a call from some business, some surprisingly large/important, because their Access database has stopped working, or needs to be debugged, or needs to integrate with some other software, or the one institutional worker they had maintaining it has left the company. Its real and it happens often. <br /><br />There are other rapid application development solutions out there that are far more futureproof and robust. SharePoint and SharePoint Online would do. (Yes, this is a career DBA recommending SharePoint. shudder). Flow, PowerApps. and LightSwitch are other Microsoft rapid application development tools. If you're thinking to yourself that the average non-technical office worker without significant computer skills wouldn't know how to use SharePoint or Flow and PowerApps or LightSwitch, you're right! Perhaps office workers without any programming skills shouldn't be developing mission-critical or decision-making repositories of data. Ask the US Air Force how fun it is to suddenly hit the 2 GB data file limit.<br /><br />Have you ever tried converting data out of an Access database, with every unvalidated field a 255-width Text data type, especially the dates and times? No wonder the business team has a low view of the business value that "programming" can give to a business process!<br /><br />To be clear, I'm not suggesting everyone stop using Access. I'm suggesting companies not store their mission-critical data in an applications that are best suited for student learning. Use Access as a front end if you like, but make some someone is assuring that the data is stored in "linked tables" in a real database platform.<br /><br />-Williamwhttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-59427499204769181352018-02-04T02:27:46.023-08:002018-02-04T02:27:46.023-08:00Hi
You make a strong case for structured, robust ...Hi<br /><br />You make a strong case for structured, robust and considered system development - the ideal. The reality for many small to medium sized businesses is that they require quick bespoke solutions for urgent problems, using very limited resources (how many people are forced to use Excel as a defacto database?). Speccing and building a robust system in SQL Server is often not viable (or even then accessible and useful). The valid risks that you identify are often just another risk that these businesses choose to take as a commercial reality i.e. addressing them comes at significant financial and time cost. Access is an incredibly useful tool for many of these businesses. With respect, approaching this topic with a black and white position does demonstrate that you need more appreciation for the flexibility and speed that many businesses require to survive.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-88436723079785726212016-09-09T07:34:46.299-07:002016-09-09T07:34:46.299-07:00Dear Anonymous-
Thanks for reading. While Access m...Dear Anonymous-<br />Thanks for reading. While Access may be a good rapid application development platform for an entire generation of IT professionals, it isn't the only rapid application development tool out there. I did in fact recommend that custom forms and report functionality be migrated to SharePoint and Reporting Services one by one. If SharePoint isn't an option, and a SQL Server license isn't an option but an MS Office license is already paid, then any good consultant should strongly advise their clients to reconsider their IT investment options before developing in Access, and be honest with the client about the risks and dangers involved in bare bones IT budgets. A company may also find alternatives in off the shelf software once they realize its highly unlikely that their business model/industry is not unique. Failing that, I'd recommend .NET, which is open source, and has many documented and user-friendly rapid application development tools and techniques. Yes, .NET may be outside the skillset of the everyday IT generalist - that doesn't mean those Access alternatives are not superior alternatives that won't saddle a small business with serious problems down the road.<br /><br />I didn't write this article out of some prejudice for Access or for the generation of consultants who made a living as Access developers in the late 90s and 2000s. I do work in the real world, where we encounter real clients whose businesses are in danger of ceasing operations because of over-reliance on student-grade database tools now failing them. I don't think it's fair to say that my blog is living in fantasy world - I have worked with dozens real world clients to escape the mess that some Access developer planted for them. Thanks for replying though.<br /><br />-William<br />whttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-2959700636797979032016-09-09T03:35:28.422-07:002016-09-09T03:35:28.422-07:00You don't say what to use the all important fr...You don't say what to use the all important front end? Assuming a company has an SQL Server to simply store and acces data but no web server for .net, java, php front ends and the hugege dev time and costs that come with them to create front end GUIs.<br />And if I have a short term contract for a financial institute who won't allow their data off site or to netwoek into then my only option is to use Excel or Access a the quick and dirty goto tool. They get the job done and everyone is happy.<br />This is just one sxample of a 'real world' scenario and not fantasy 'enterprise world' that alll looks fine and dandy when reading about it on a blog!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-46294555361258778452015-12-21T13:00:57.055-08:002015-12-21T13:00:57.055-08:00Au Kay Wah-
I don't disagree at all, but rapid...Au Kay Wah-<br />I don't disagree at all, but rapid application development with such limitations and lack of scalability aren't appropriate in most professional, multi-user settings. Access is very good at what it does, and like many I learned from it while in school, but it should probably stay there, especially if any of the data will be contained inside an Access file.<br />-Williamwhttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-81799489671816055432015-12-21T05:19:58.808-08:002015-12-21T05:19:58.808-08:00It is not apple to apple when you compare SQL Serv...It is not apple to apple when you compare SQL Server with Access. Access is a rapid application development tool. No all solutions need the enterprise scale.Au Kay Wahhttps://www.blogger.com/profile/05759313901231916474noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-53786741032043799602015-11-25T02:31:56.318-08:002015-11-25T02:31:56.318-08:00This article is a nice one about MS Access not to ...This article is a nice one about MS Access not to use but it is far away of beeing a complete picture. Critical Data belongs in "a real Database solution" such MS SQL. That is common sense and does not speak against the use of Access as it can be used as a FE to MS SQL. So its up to the programmers/skilled users decsion where to store the data. <br />There are a lot of pros using Access I miss beeing mentioned here. At the end its a trade-off wether to use it and if you use it to what extend.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-65262529678400477302014-04-19T21:30:41.287-07:002014-04-19T21:30:41.287-07:00Anonymous commenter: While I am not to be confused...Anonymous commenter: While I am not to be confused for a SharePoint consultant in any way, but I believe the limit for rows in a list is much, much higher. <br />30,000,000 items per list, according to this link: http://technet.microsoft.com/en-us/library/cc262787(v=office.15).aspx <br />You may be thinking of 5000 as the limit for a list view or a query result set, but those values is only a default, and can be configurable by an administrator. <br /><br />I definitely recommend storing table data in a SQL Server, not SharePoint, and any Access-replacement solution would contain a "combination of Microsoft SQL Server and Microsoft SharePoint." SharePoint could be used to replace Access forms and provide "a quick and easy way for developers to create data entry forms that run in your web browser."<br /><br />Thanks for commenting!<br /><br />-Williamwhttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-56189220287200712302014-04-19T21:02:42.505-07:002014-04-19T21:02:42.505-07:00One thing to note, however, SharePoint lists are l...One thing to note, however, SharePoint lists are limited to 5,000 rows :(. So, enterprise solutions still may not work with SharePoint.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-15052970423711148412014-02-05T07:59:07.351-08:002014-02-05T07:59:07.351-08:00Anonymous from Feb 5-
I have to add that the VBA ...Anonymous from Feb 5-<br /><br />I have to add that the VBA dev platform is comparatively immature in terms of source control, testing, deployment and release management compared to modern alternatives. There is also a considerable opportunity cost in staffing the skillset to develop for VBA in Access. <br /><br />I have no doubt that you can develop something configurable and robust in Access when using SQL Server as the back end, I've seen some good examples, but lifecycle has got to be a factor for the IT decision makers. <br /><br />In my consulting work, like I wrote above, I've seen dependence on the Access mindset and the Access VBA skillset really become a logistical and financial bottleneck for businesses. That was the crux of my blog post. I certainly mean nothing personal by that.<br /><br />-Williamwhttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-42928414453553738842014-02-05T05:44:44.940-08:002014-02-05T05:44:44.940-08:00Totally agree that access [Jet database] is a rubb...Totally agree that access [Jet database] is a rubbish database, but Access is a very good front end client for SQL Server. It's a highly configurable VBA front end with good data controls and tools already built.. I use an access front end client that is so highly customised you can't tell what it is, all coded in vba and not a single access table. Integrated version management and updating for local client front-end Access Application. My horse and cart are Access and SQL Server. They are hitched.<br /><br />Yes [Access Data Projects] are the correct way to use Access, though later office versions do not use updated forms of .adp you can still use the principle of no data in the client.<br /><br />[ADP] firles and [Access Runtime] was a fantastic combo from 2003...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-20042376325176315102014-01-10T09:36:15.365-08:002014-01-10T09:36:15.365-08:00I concur with your assessment about using the data...I concur with your assessment about using the database engine within Access...<br /><br />Does Access still support Access Data Projects (.adp)? A few years ago I found these to be useful for rapid prototyping.. You had the ability to use Access for Forms/Reports - and the engine was the sql server instance you bound the project to.<br /><br />I still wouldn't use at the Enterprise (they have issues as well) - but I did find them amazingly useful... Anonymousnoreply@blogger.com