By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

MS Access Frontend/SQL Backend

P: 18
I have been developing commercial Access dbs’ for many years now, some are 100% Access and others have an Access frontend and SQL backend. The latter are connected via ODBC and run on a local PC/server environment which works a treat. But if the server is hosted in the cloud and the frontends on local PCs’, connecting in this manner is just not workable for many reasons.

To work around this, we have been looking at utilising record sets and stored procedures called from the Access frontend and processed by SQL at the backend. This works very well and we are able to create complicated applications with which to add, modify and delete records at very acceptable speeds.

The SQL db is hosted on a dedicated server with an ISP and the frontend runs on Access runtimes, keeping licensing and other costs to a minimum.

This all works very well, it is cost effective and offers an alternative to a true Internet based application and it can be run at various regional office locations with a central storage db on the server.

The question that I have is, why is there very little mention of utilising this solution on the Internet as it appears to be a very viable secure solution?
Nov 23 '16 #1

✓ answered by jforbes

The reasons I could think of why there is not much mention of this approach:
  • Some of the benefits Access typically would provide in ease and rapid development would be minimized. You would probably be a better witness to this than I am. But just in upsizing to SQL Server usually means scripting the creation of Tables, triggers, defaults and so on, which can take a considerable amount of time over using Access alone.
  • Access looses some of it usability and responsiveness when the latency is increased during Database reads. This has to do with the method Access uses to retrieve a Form full of data. There are better explanations on how Access populates a Form with data, but a basic one is that Access first gets a list of Keys for the Rows of the Form Dataset, then it queries the first ten records, then the second ten records, and so on until it gets them all. This procedure repeats for any SubForms. Since the Dataset isn't retrieved in one trip, the time to Load a Form with data is somewhat proportional to the distance between the FrontEnd and Backend. Also meaning, increasing Server resources would have a negligible impact on increasing responsiveness of the application when the Frontend and Backend are a considerable distance apart.
  • The learning curve for this approach is pretty steep and pretty much requires a Developer or equivalent in skillset to create an application.
  • The Developer would need to know Access, VBA, and TSQL instead of Access and VBA alone. TSQL can be challenging to start with. It would probably be just as easy to learn and implement a .NET (VB or C#) and TSQL solution with all the advantages .NET can provide over Access like Entity Framework, Drag and Drop, WIX installers/ClickOnce Deployment, full support for Third Party controls, and XAML/WPF Frontends. Lastly, the speed of getting and setting data is increased with .NET as the Dataset is retrieved or updated in one trip to the Server, which also means increasing Server resources will have a direct impact on the speed of the application.
  • A lot of people are still not ready to move their data to the Cloud.

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
The reasons I could think of why there is not much mention of this approach:
  • Some of the benefits Access typically would provide in ease and rapid development would be minimized. You would probably be a better witness to this than I am. But just in upsizing to SQL Server usually means scripting the creation of Tables, triggers, defaults and so on, which can take a considerable amount of time over using Access alone.
  • Access looses some of it usability and responsiveness when the latency is increased during Database reads. This has to do with the method Access uses to retrieve a Form full of data. There are better explanations on how Access populates a Form with data, but a basic one is that Access first gets a list of Keys for the Rows of the Form Dataset, then it queries the first ten records, then the second ten records, and so on until it gets them all. This procedure repeats for any SubForms. Since the Dataset isn't retrieved in one trip, the time to Load a Form with data is somewhat proportional to the distance between the FrontEnd and Backend. Also meaning, increasing Server resources would have a negligible impact on increasing responsiveness of the application when the Frontend and Backend are a considerable distance apart.
  • The learning curve for this approach is pretty steep and pretty much requires a Developer or equivalent in skillset to create an application.
  • The Developer would need to know Access, VBA, and TSQL instead of Access and VBA alone. TSQL can be challenging to start with. It would probably be just as easy to learn and implement a .NET (VB or C#) and TSQL solution with all the advantages .NET can provide over Access like Entity Framework, Drag and Drop, WIX installers/ClickOnce Deployment, full support for Third Party controls, and XAML/WPF Frontends. Lastly, the speed of getting and setting data is increased with .NET as the Dataset is retrieved or updated in one trip to the Server, which also means increasing Server resources will have a direct impact on the speed of the application.
  • A lot of people are still not ready to move their data to the Cloud.
Nov 24 '16 #2

P: 18
Jforbes,

thanks for the response and apologies for taking so long flagging your answer as being the best, but I got assigned to another project.

Your response is very informative and helpful and I am now back on this project which is progressing well apart from one issue that I can’t get over and perhaps you can assist us with it again? (Rest of question moved to proper place in a new thread - Local Data Not Editable in Form -NeoPa Admin).
Apr 14 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.