December 20, 2018 at 9:31 am
Hello;
I am in the place of having to reengineer a solution that was built using a .adp in 2014 and only expected to be needed for a year.... Four years later it is still being used and will continue to be used for an unspecified period of time.
The current solution uses tables, views, and stored procedures to provide its functionality. This solution is used in a widely geographically distributed area (across a province) concurrently by several hundred users - so it must perform well over a WAN (This was part of the reason for using a .adp in the first place - it was WAY faster in locations distant from the server, than the comparable .accdb file with linked tables).
At the risk of rehashing a widely discussed topic (most discussions are quite old and with much conflicting & confusing advice), I'm seeking advice/direction on the current prevailing wisdom / best practice regarding using a MS Access "database" as a front end to a SQL Server database. Specifically I'm looking for direction about the method of communicating between the front end and back end (i.e. linked tables, ADO, DAO, ACEDAO, [something else?]). I just need to be pointed in the right direction...
The front end is, for the time being, MS Access 2010 (I don't have a choice about this). The back end is SQL Server 2014. At some point in the next year or two our enterprise will be migrating to Windows 10 and the most current version of MS Office (what ever that is when we finally get there). The consideration here is a smooth migration path to the most current version of MS Access.
Thanks in advance for any advice you can provide.
December 20, 2018 at 10:21 am
Well, as they say in some parts of the world, that a bit of a sticky wicket. An upgrade to the latest version of Access forces a redesign - bummer.
In any event, how do remote users connect? Are they using remote desktop, or are they actually running the Access front-end on their local workstation? If they are using remote desktop, the deployment issue shouldn't be horrible. On the other hand, if they all need a new copy of the front-end on their workstation, it sounds like an ugly process. My advice would be to stall as long as you can - Access 2010 is quite stable, and the deployment issue has been solved. But you obviously need a long-term solution in your back pocket.
My experience with SQL Server as a back-end began in 1994, and we've always used ODBC connections. What we found was that with careful table design, and the use of indexed views in SQL Server, we got performance that was equal to that of the .ADP approach, and our development cost was significantly less. The trick is to always return as few rows as are necessary. Where we had to process a large number of records, then we used a stored procedure that was called from Access. With that kind of design we achieved sub-second response time retrieving a small number of records from tables containing several million rows.
Hope this helps, and I'm looking forward to the input from others.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 20, 2018 at 10:45 am
Wendell;
Thanks for the this information. At present, users run the Access front end from their local workstation. Our method of distributing updates is clunky but it works for us.
Our present design is very minimalistic - very light on code. Split forms (grid of records in the table - on top, fields arranged in logical workflow groupings in the form portion) by default returning a categorized subset of rows from the table, which can still be in the 10's of thousands. This approach was taken because we believed the solution would only be needed for about a year.
I'm sure I could redesign the forms to only return a single row or a very small subset of rows. I'm pretty sure that could be done with the current .adp based solution but that wouldn't address the eventual Office [Version next] problem.
To be clear, is your approach to use a .accdb file with DAO (linked tables?)? I've also read about the use of passthrough queries. Do you use those? I'm a little bit out of my depth with Access/VBA. Most of my work is on the SQL Server side. I have no trouble with views, stored procedures, functions and the like.
December 20, 2018 at 11:35 am
Yes, that is the approach we've used in nearly all the applications we've developed - use ODBC linked tables. DAO is actually a method you can use to manipulate table data in VBA, and we occasionally used that, but in most cases, Access forms automatically save changes once you move to a different record, or close the form. Thus we only occasionally used DAO to manipulate table data. We do occasionally use pass-through queries where we want to find a specific record or subset of records without displaying a large recordset in a form. If you want to display a record in a form and you don't specify a query to limit the number of records returned, the first step with ODBC is to return the entire recordset, and that's something you don't want to do to get good performance. Here is a link to an old article by Luke Chung that talks about strategies with linked tables -Significantly Improve the Performance of ... Linked Tables - there are lots of other useful articles at FMS as well, especially if you are considering SQL Azure. Hope this helps some.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 20, 2018 at 12:42 pm
Here's another link at FMS that is more general and is also a well-balanced perspective on the various back-end approaches:
When and How to Upsize Microsoft Access Databases ...
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 21, 2018 at 7:47 am
We also have an application presently using Access with SQL Server. We use the Access runtime on a Windows terminal server, and each user has a folder with their own copy of the front-end which connects using ODBC linked tables and pass-through queries. We also have local & remote users all across the US - Users at the home office simply log-in to the term server; remote users connect via vpn to the term server. This has worked well for us since 2009.
January 13, 2023 at 4:45 am
This was removed by the editor as SPAM
January 13, 2023 at 4:52 am
This was removed by the editor as SPAM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply