{"id":1284,"date":"2020-07-23T00:58:02","date_gmt":"2020-07-23T05:58:02","guid":{"rendered":"http:\/\/accessusergroups.org\/sql-server-with-access\/?p=1284"},"modified":"2020-07-23T00:58:02","modified_gmt":"2020-07-23T05:58:02","slug":"alternative-solution-to-dcount-and-dlookup-with-sql-server-backend","status":"publish","type":"post","link":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/","title":{"rendered":"Alternative solution to DCount and DLookup with SQL Server Backend"},"content":{"rendered":"
One of the major issues we have encountered with Access is the use of DLookup and DCount when using SQL Server tables.\u00a0 We recently worked on migrating a pure Access solution to SQL server and encountered delays on the loading of several forms.\u00a0 This was due to the use of DLookup and DCount in the VBA code.<\/p>\n
We then came up with a solution to quickly resolve the multiple instances with a couple of functions.\u00a0 We were guided by another solution provide by Allen Browne who designed the Extended DLookup here in this link<\/a>.<\/p>\n Allen\u2019s solution improves the performance of the DLookup by:<\/p>\n 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.<\/p>\n You will find the script here<\/a>.<\/p>\n 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.<\/p>\n 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.\u00a0 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<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":" One of the major issues we have encountered with Access is the use of DLookup and DCount when using SQL Server tables.\u00a0 We recently worked on migrating a pure Access solution to SQL server and encountered delays on the loading of several forms.\u00a0 This was due to the use of DLookup and DCount in the […]<\/p>\n","protected":false},"author":103,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"mc4wp_mailchimp_campaign":[],"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1284","post","type-post","status-publish","format-standard","hentry","category-blog-posts"],"acf":[],"yoast_head":"\n\n