Alternative solution to DCount and DLookup with SQL Server Backend
One of the major issues we have encountered with Access is the use of DLookup and DCount when using SQL Server tables. We recently worked on migrating a pure Access solution to SQL server and encountered delays on the loading of several forms. This was due to the use of DLookup and DCount in the VBA code.
We then came up with a solution to quickly resolve the multiple instances with a couple of functions. We were guided by another solution provide by Allen Browne who designed the Extended DLookup here in this link.
Allen’s solution improves the performance of the DLookup by:
- Including a sort order to ensure you get the result you need.
- Cleaning up after itself.
- Correctly differentiates a Null and a zero-length string.
- Overall improvement in performance.
We have now taken this one step further to work specifically with SQL tables or views, these will not work with Access local tables as we are specifically using an ADO connection.
You will find the script here.
If you have an instance that requires the use of DSum then you can easily adapt the DCount function to give you the required result.
After applying this solution we found a dramatic improvement in the performance of the forms loading and the design helps us to apply this solution to multiple projects. I hope this solution is helpful to you and if you have any other issues that we can help you with then please reach out to us at accessexperts.com.