Future of Access (2 Viewers)

[GPGeorge]

Spoiler:

IIRC, LightSwitch replaced SilverLight, and positioned it as an LOB RAD tool, but it didn't gain much traction, and MS replaced it with PowerApps. Is PA similar to LightSwitch?

I thought AWA's was a viable solution because it mimicked how we create tables, queries, forms, and reports in Desktop Access. It essentially had the same client UI. Access developers had high expectations that it would have a VBA_like language, but instead relied on event based Data Macros and was lacking the rich functionality available with VBA. So only a handful of Access developers adopted AWA and MS retired it in 2017.

So here we are again, for the 4th time, in the same dilema. First it was "Data Access Pages", then came "Access Web Databases", "Access Web Apps", and now "PowerApps". Next up, "PowerAI". . . "Hey AI, build me a web app with PowerApps that works just like Desktop Access NorthWinds2 application."

Since MS has repeatedly failed to deliver a web based version of Access that gains traction with the Access development community, why don't we as a collective put together a white paper and present it to Microsoft? I would think we can come to a consensus on a good design for an Access Web version. We could define a library of RestAPI's that mimicks vba functionality and create a framework that includes an IDE and designer wizards similar to what desktop Access has. I've been following Wayne Phillips' TwinBasic project, but it's not web based, rather vba/vb6 compatibility for desktops: https://marketplace.visualstudio.com/items?itemName=twinbasic.twinbasic

10 years too late, I'm afraid. And, to be very blunt, a project that is quite unlikely to blossom due to lack of a coherent guiding plan and leader.

I'm not being critical of anyone at all. To the contrary, I admire the ambition.

What I'm suggesting is that Wayne Phillips' TwinBasic is the result of one person's vision, drive and commitment to a long-term project with a goal of creating a revenue stream. And I dare say that last bit is also crucial. Without a hope of return, such a project will languish,even after a brilliant start.

I do not want to be argumentative. What I question is not the concept itself, but the framework in which it is proposed:

A loosely defined collective of like-minded developers attempting to replicate -- for free -- everything that Microsoft spent millions of dollars building with a dedicated team of software engineers under the guidance of some really brilliant leaders.

It would be truly magnificent if it could be done. Again, I'm not questioning that. What I'm wondering is where the leadership and resources would come from and who would be able to dedicate the time and effort required while earning a living in their full-time job.

Address that organizational problem, and then we'll have some confidence it can happen.

 

[Pat Hartman]

I think MS' goal with AWA was for it to be fully web based while mimicking how we currently create objects in Access.

"mimic" doesn't cut it. If your existing Access app cannot be directly converted, the pretend Access AWA is useless and that was what the public ruled.

Feels "young" to me because I haven't heard much about it until lately by @GPGeorge.

That's because of their pricing model. It is not geared for small businesses. It is too expensive. Therefore, it is not a replacement for Access.

We could define a library of RestAPI's that mimicks vba functionality and create a framework that includes an IDE and designer wizards similar to what desktop Access has.

Again with the "mimic". If the "new" tool "mimics" Access but does not provide a direct conversion path, it will not be accepted. If companies are going to invest the time and money to completely recreate their Access apps, they can do it today with any number of tools available. VBA doesn't run on the web and therefore none of the code in the app is convertible. There is no getting around this conversion issue. Rewrites are expensive. PERIOD. Simply calling a tool "Access" doesn't make it so. That is the lesson you should learn from Microsoft's three (or maybe four) failures.

 

[Steve R.]

Since MS has repeatedly failed to deliver a web based version of Access that gains traction with the Access development community, why don't we as a collective put together a white paper and present it to Microsoft? I

MS Access has its niche and like Cobol will continue to limp along. Nevertheless we need to recognize that technology evolves. Land lines and FAX machines are a dying technology. To a degree what we need to do is think-of-of-the-box and declare "independence" from Microsoft. What that implies is ditching the Microsoft environment. (This will also save $$$ as no licensing fees would be normally required.) Once that is accomplished open source databases can be used to deliver web based solutions to the user communities served by MS Access. If fact, this forum has precursor sub-forums, such as "Web Design and Development" that could be expanded to promote and achieve such a transition.

10 years too late, I'm afraid. And, to be very blunt, a project that is quite unlikely to blossom due to lack of a coherent guiding plan and leader.

@GPGeorge: Made a very observant call, that attempting to implement a a web based version of MS Access would be very challenging. This concern can be eventually overcome as people learn that there are other database solutions to MS Access that also free the users from the Microsoft environment. It will take time.

PS: Think of industry leaders such a Sears that have gone bankrupt or Intel which recently imploded. Anybody remember AOL? Industry leaders, such as Microsoft, tend to be very large and bureaucratic, eventfully they sputter. The users may then find that they are left out in the cold. With that in mind, people need to have a plan "B".

 

[GPGeorge]

"mimic" doesn't cut it. If your existing Access app cannot be directly converted, the pretend Access AWA is useless and that was what the public ruled.

That's because of their pricing model. It is not geared for small businesses. It is too expensive. Therefore, it is not a replacement for Access.

Again with the "mimic". If the "new" tool "mimics" Access but does not provide a direct conversion path, it will not be accepted. If companies are going to invest the time and money to completely recreate their Access apps, they can do it today with any number of tools available. VBA doesn't run on the web and therefore none of the code in the app is convertible. There is no getting around this conversion issue. Rewrites are expensive. PERIOD. Simply calling a tool "Access" doesn't make it so. That is the lesson you should learn from Microsoft's three (or maybe four) failures.

In the past, I've tried to make the point that adding a web-based component to an Access solution is not the same as replacing the Access solution with a web-based solution. One of the many reasons Access has never been recreated for the Web is precisely that. You don't need to duplicate the solution to be successful. You only need to provide components that are a) compatible with the primary solution, i.e. the Access database application and b) sompatible with remote data entry and/or reporting. In the past, I think an overabundance of optimism led too many people to see AWA's as the path to get around Access, not as a way to enhance it. It became an all or nothing proposition that was doomed for that reason.

Hybrid applications, on the other hand, should be cohesive across platforms, but they do not need to be duplicative to be useful.

I've spent several months working on a PowerApps vesion of Northwind Developer and documenting the process in videos on YouTube. In only one of those videos have I addressed interface differences deriving from the different design principles. Most of those videos document managing data for the unbound, stateless web environment.

I have concluded two main things, and a lot of little things.

1. PowerApps is far more capable than some would like to have you believe. I compare that to the oft-repeated complaint that SQL Server DBAs dismiss Access as too basic to be useful. At the risk of offending some, I think that is largely an artifact of not having spent much time examining the possibilities.
2. PowerApps is not a competitor with Access and probably never will be. Reporting is one glaring hole. Complex logic in reusable, modular code is another.

To sum up. I strongly believe that PowerApps interfaces for the mobile environment can be a strong candidate for a complementary partnership with Access interfaces for the desktop environment. I don't even think PowerApps is primarily a browser-based play for such hybrids. If you need a full-blown web app, build that.

 

[BlueSpruce]

"mimic" doesn't cut it. If your existing Access app cannot be directly converted, the pretend Access AWA is useless and that was what the public ruled.

That's because of their pricing model. It is not geared for small businesses. It is too expensive. Therefore, it is not a replacement for Access.

Again with the "mimic". If the "new" tool "mimics" Access but does not provide a direct conversion path, it will not be accepted. If companies are going to invest the time and money to completely recreate their Access apps, they can do it today with any number of tools available. VBA doesn't run on the web and therefore none of the code in the app is convertible. There is no getting around this conversion issue. Rewrites are expensive. PERIOD. Simply calling a tool "Access" doesn't make it so. That is the lesson you should learn from Microsoft's three (or maybe four) failures.

Are you thinking of a utility that converts vba code to a web development language? A desktop Access app cannot be entirely converted to a web app. I think a web app can reproduce to a certain extent Access vba functionality, such as executing event driven code and support vba_like built in functions,. However, web apps operate as stateless unbound objects and it's not going to let you manipulate filesystem objects, automate other desktop applications, or directly interact with Windows. Some basic functionality, like uploading/downloading files, creating pdf's, and sending emails are available. what Access really needs to evolve to is to support hybrid applications, where desktop Access clients can reliably work with remote backends.

 

[Pat Hartman]

What is this obsession with running in a browser? You don't need Access to run in a browser. You only need Access to be able to effectively connect to data sources across a WAN the same way that web apps do. Access is perfectly capable of doing this NOW. The issue is the connection is so slow it is like watching paint dry.

WHY do you think people should be willing to give up all the things that Access does and which a web app can never do? What is wrong with simply fixing the ONE limitation Access has and that is the speed of the connection to the remote database? I'm not saying this is a trivial problem to solve but in the greater scheme of things it is a pretty focused task and so maybe someone can figure out how to do it.

Yes, distribution of updates is easier with a browser but somehow those of us who create professional level Access apps manage to make this happen without disruption for our clients.

 

[AccessBlaster]

It's almost like nobody saw this new fangled interwebs thing coming.

 

[BlueSpruce]

In the past, I've tried to make the point that adding a web-based component to an Access solution is not the same as replacing the Access solution with a web-based solution. One of the many reasons Access has never been recreated for the Web is precisely that. You don't need to duplicate the solution to be successful. You only need to provide components that are a) compatible with the primary solution, i.e. the Access database application and b) sompatible with remote data entry and/or reporting. In the past, I think an overabundance of optimism led too many people to see AWA's as the path to get around Access, not as a way to enhance it. It became an all or nothing proposition that was doomed for that reason.

Hybrid applications, on the other hand, should be cohesive across platforms, but they do not need to be duplicative to be useful.

I've spent several months working on a PowerApps vesion of Northwind Developer and documenting the process in videos on YouTube. In only one of those videos have I addressed interface differences deriving from the different design principles. Most of those videos document managing data for the unbound, stateless web environment.

I have concluded two main things, and a lot of little things.

1. PowerApps is far more capable than some would like to have you believe. I compare that to the oft-repeated complaint that SQL Server DBAs dismiss Access as too basic to be useful. At the risk of offending some, I think that is largely an artifact of not having spent much time examining the possibilities.
2. PowerApps is not a competitor with Access and probably never will be. Reporting is one glaring hole. Complex logic in reusable, modular code is another.

To sum up. I strongly believe that PowerApps interfaces for the mobile environment can be a strong candidate for a complementary partnership with Access interfaces for the desktop environment. I don't even think PowerApps is primarily a browser-based play for such hybrids. If you need a full-blown web app, build that.

Perhaps we could submit a white paper to MS that provides a market analysis of current Access applications, a product plan, positioning, pricing, and sales projections? I bet if MS would've charged a minimal amount for desktop Access runtimes, users would've still paid for them. You can't expect MS to continue providing things for free, we've been spoiled in that aspect.

 

[Pat Hartman]

The problem with MS is that it tends to concentrate on its corporate clients. It gets some opinions from the MVP group also. My "Access" clients have ranged from huge companies like United Technologies (Pratt & Whitney, Sikorsky, Hamilton Sundstrand, etc), medium large companies like Readers' Digest and down to mom and pop operations with no employees. In all the large corporations I have consulted for, there is huge pressure to not use Access and for all the wrong reasons. But somehow creating seriously flawed spreadsheets doesn't seem to bother the It folks.

Strangely enough, there is no multi-million dollar, multi-year COBOL application that I ever designed or built that could not have been reproduced using Access for less than 10% of the cost and time. Access simply works extremely well for a certain class of business applications. It certainly is not a universal solution but if you have the right type of problem to solve, nothing can beat Access. The biggest problem with Access is not actually scalability, that can easily be handled by SQL Server or other RDBMS but is its clumsiness as a multi-developer platform to support parallel development by multiple developers at one time because it uses a single container for all objects.

 

[CJ_London]

You can't expect MS to continue providing things for free, we've been spoiled in that aspect.

In terms of developing- how is it free? You either pay for a lifetime licence or by subscription. As far as free runtime is concerned, how much do you pay for a browser?

 

[GPGeorge]

What is this obsession with running in a browser? You don't need Access to run in a browser. You only need Access to be able to effectively connect to data sources across a WAN the same way that web apps do. Access is perfectly capable of doing this NOW. The issue is the connection is so slow it is like watching paint dry.

WHY do you think people should be willing to give up all the things that Access does and which a web app can never do? What is wrong with simply fixing the ONE limitation Access has and that is the speed of the connection to the remote database? I'm not saying this is a trivial problem to solve but in the greater scheme of things it is a pretty focused task and so maybe someone can figure out how to do it.

Yes, distribution of updates is easier with a browser but somehow those of us who create professional level Access apps manage to make this happen without disruption for our clients.

I agree. The obsession of running in a browser is, in my opinion, somewhat of a side issue.

That focus remains on an either/or choice and that is a false choice.

Back in 2008 or 2009, I was part of a development team. We were a couple of Access developers and a web developer. I created the Access interface and the web developer created the web interface. The other Access developer provided support from time to time.

The back end was cloud based so that both interfaces could share the data. At the time, we didn't have a way to host the website internally, so it was on a web host.

The key design pattern was that the Access interface did

• all of the ETL for test results from test equipment
• heavy data manipulation and aggregation of those test results
• other management tasks, such as maintaining client records and some information related to billing for test services

The web interface provided

• anonymous public access (there's that sneaky word in its other meaning) to the aggregated data
• a password protected way for clients to request testing on their equipment.

Two related, highly correlated, but fundamentally different kinds of processes built on the appropriate platforms.

That has colored my thinking about this kind of application ever since.

I simply can't rationalize wanting to "replace" Access to gain the benefits of remote interactions anonymous or not.

I can rationalize complementing an onsite solution with a more appropriately designed remote solution.

Now that PowerApps is available, I see it primarily as the extension of that same hybrid design onto smart devices: phones and tablets.

 

[BlueSpruce]

there is huge pressure to not use Access and for all the wrong reasons. But somehow creating seriously flawed spreadsheets doesn't seem to bother the It folks.

Who started that mentality, Corporate IT's, MS SQL Server marketing, or their DotNet folks?

 

[CJ_London]

The issue with large corporates is budget - I’ve provided many apps to service perhaps 3 or 4 employees who need a solution now, not two years down the line at a high cost. Wouldn’t be a problem if the large corporate systems provided the detail actually required, but the cost of modifying the system to do so is too high.

Large corporate systems are not designed to meet the strategic needs of their clients

 

[BlueSpruce]

I think the problem of Access FE's working seamlessly with remote db's is that Access is file server based and uses the SMB communications protocol, which doesn't handle delays and interference well. Access architecture also involves a lot of network traffic often having to load entire tables from the BE to the FE. I have seen some optimized Access/Azure apps, but was still slow, even without using vpn connections.

It's a lot different when an unbound web app does async gets and puts to a remote db, versus a bound desktop client that requires a persistent connection that's volatile when delays and noise easily break that connection. Then odbc often has to re-establish the connection.

 

[Pat Hartman]

I simply can't rationalize wanting to "replace" Access to gain the benefits of remote interactions anonymous or not.

I can rationalize complementing an onsite solution with a more appropriately designed remote solution.

I'm in 100% agreement with you there. There are things that MUST run in a browser, there are things that can run in a browser, and there are things that CANNOT run in a browser and you need to understand the difference.

Most clients don't have 10's or 100's of thousands of dollars to rewrite a working application simply so it can run in a browser. Access is flexible enough so that fixing one very small piece (ODBC) to be more efficient over the web would give it an entirely new lease on life. The cost of converting to using a web linked database is not free if the app was designed originally using very old Access specific techniques such as forms bound to tables with local filtering but since I build all my apps with the potential of being "upsized" to SQL Server, I can usually convert an app I built in an afternoon and that is only because it takes a long time to test everything to make sure I haven't overlooked something important in the initial build.

The reason that most clients even think about wanting Access "on the web" is because they want employees to be able to work remotely. Almost never does anonymous use come into play.

 

[Pat Hartman]

Access architecture also involves a lot of network traffic often having to load entire tables from the BE to the FE.

Only a poorly designed query would require Access to request entire tables from the server. Access does its best to make every query a pass-through query. The fact that Access has to touch the query at all makes even good queries slightly slower than true pass-through queries but not enough so most people would notice or convince you to give up updateability for the sake of such a tiny speed increase.

 

[Pat Hartman]

Who started that mentality, Corporate IT's, MS SQL Server marketing, or their DotNet folks?

The MS SQL Server people are always saying that "Access" is a toy and eventually that permeates an environment so no programmer wants to learn a "toy" and be laughed at.

The issue with large corporates is budget - I’ve provided many apps to service perhaps 3 or 4 employees who need a solution now, not two years down the line at a high cost. Wouldn’t be a problem if the large corporate systems provided the detail actually required, but the cost of modifying the system to do so is too high.

Large corporate systems are not designed to meet the strategic needs of their clients

I've never technically worked for IT in a large corporation. I am always hired by some department that needs something done yesterday and IT is giving them a huge price tag and a multi-year delivery timeframe. Sometimes IT will allow me to actually use their SQL Server but they make it annoying for me by not letting me do my own DBA work in the test region so I tend to develop initially using ACE and then when I think the BE is pretty stable, I let the overly protective DBA take the BE and convert it. From then on, every minor change must be made by the DBA and if he's busy or doesn't like Access, it can take weeks to get even simple changes implemented.

What IT doesn't seem to understand is that Access can be used as an effective prototyping tool. Most mainframe applications I ever designed morph as we progress and learn more about the business and the users get more into the details of what they actually want. Access is a far easier tool to accommodate a learn as you go methodology. And then, once the client convinces IT that the "temporary" solution needs to be "permanent", IT has a working schema and code base which they can translate to their preferred production platform.

IT also doesn't understand that it is in their best interest to provide in-house Access development support to help to ensure that users who attempt to go it alone create something sort of sound instead of crap.

 

[Pat Hartman]

I think the problem of Access FE's working seamlessly with remote db's is that Access is file server based and uses the SMB communications protocol, which doesn't handle delays and interference well. Access architecture also involves a lot of network traffic often having to load entire tables from the BE to the FE. I have seen some optimized Access/Azure apps, but was still slow, even without using vpn connections.

It's a lot different when an unbound web app does async gets and puts to a remote db, versus a bound desktop client that requires a persistent connection that's volatile when delays and noise easily break that connection. Then odbc often has to re-establish the connection.

SMB has nothing to do with it. If a browser can have a usable remote connection, so can Access. The ODBC technology was developed for LAN use. Someone needs to develop a version for WAN use. But it goes a little beyond that since the parts of Access that use the ODBC connection have to be adjusted also.

 

[BlueSpruce]

Only a poorly designed query would require Access to request entire tables from the server. Access does its best to make every query a pass-through query. The fact that Access has to touch the query at all makes even good queries slightly slower than true pass-through queries but not enough so most people would notice or convince you to give up updateability for the sake of such a tiny speed increase.

I think the issue lies with how well odbc can translate queries before they're executed on the db server. One would think a PT query runs on the server exactly as it was written and stored in the Access FE, but odbc has mysterious ways of changing it, especially if it's a complex query with several joins. It's better to use server side stored procedures and let the server do the heavy lifting. Then what about all the server side lookup tables the combo boxes in Access forms needs to load?

 

[Pat Hartman]

Access doesn't touch pass through queries. They go as they are written. ODBC does have to translate standard Access queries. Only VBA functions that have SQL equivalents can be passed to the server. All others must be processed locally and that presents a problem unless the functions are in the Select clause. NO UDF's can be passed though. Same problem. Joins between local and linked tables force Access to request the entire linked table so the join can be performed locally

It's better to use server side stored procedures and let the server do the heavy lifting.

The server already does the heavy lifting if you create your queries correctly. Only the requested records are returned to the application. If you are going to do all your business logic with sp's, you should probably not even be using Access.