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.