{"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":"<p>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<p>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 <a href=\"http:\/\/allenbrowne.com\/ser-42.html\">link<\/a>.<\/p>\n<p>Allen\u2019s solution improves the performance of the DLookup by:<\/p>\n<ul>\n<li>Including a sort order to ensure you get the result you need.<\/li>\n<li>Cleaning up after itself.<\/li>\n<li>Correctly differentiates a Null and a zero-length string.<\/li>\n<li>Overall improvement in performance.<\/li>\n<\/ul>\n<p>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<p>You will find the script <a href=\"https:\/\/accessexperts.com\/blog\/2020\/07\/23\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\">here<\/a>.<\/p>\n<p>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<p>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 <a href=\"https:\/\/accessexperts.com\/\">accessexperts.com<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/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":"<!-- This site is optimized with the Yoast SEO plugin v23.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org\" \/>\n<meta property=\"og:description\" content=\"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 [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Server with Access - AccessUserGroups.org\" \/>\n<meta property=\"article:published_time\" content=\"2020-07-23T05:58:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-content\/uploads\/sites\/2\/2023\/04\/aug-share.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"675\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Susan Pyne\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Susan Pyne\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\",\"name\":\"Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org\",\"isPartOf\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#website\"},\"datePublished\":\"2020-07-23T05:58:02+00:00\",\"dateModified\":\"2020-07-23T05:58:02+00:00\",\"author\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62\"},\"breadcrumb\":{\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Alternative solution to DCount and DLookup with SQL Server Backend\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#website\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/\",\"name\":\"SQL Server with Access - AccessUserGroups.org\",\"description\":\"SQL Server with Access Virtual User Group\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62\",\"name\":\"Susan Pyne\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g\",\"caption\":\"Susan Pyne\"},\"description\":\"I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I\u2019m certified on Power BI, with more certifications coming. I'm also the chapter coordinator for AccessUserGroups.org, a worldwide organization of Access virtual chapters that meet each month online to discuss a range of topics in different languages. I LOVE working with Access and learning new technologies!\",\"url\":\"https:\/\/accessusergroups.org\/sql-server-with-access\/author\/susan-pyne\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/","og_locale":"en_US","og_type":"article","og_title":"Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org","og_description":"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 [&hellip;]","og_url":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/","og_site_name":"SQL Server with Access - AccessUserGroups.org","article_published_time":"2020-07-23T05:58:02+00:00","og_image":[{"width":1200,"height":675,"url":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-content\/uploads\/sites\/2\/2023\/04\/aug-share.png","type":"image\/png"}],"author":"Susan Pyne","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Susan Pyne","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/","name":"Alternative solution to DCount and DLookup with SQL Server Backend - SQL Server with Access - AccessUserGroups.org","isPartOf":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#website"},"datePublished":"2020-07-23T05:58:02+00:00","dateModified":"2020-07-23T05:58:02+00:00","author":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62"},"breadcrumb":{"@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/alternative-solution-to-dcount-and-dlookup-with-sql-server-backend\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/accessusergroups.org\/sql-server-with-access\/"},{"@type":"ListItem","position":2,"name":"Alternative solution to DCount and DLookup with SQL Server Backend"}]},{"@type":"WebSite","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#website","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/","name":"SQL Server with Access - AccessUserGroups.org","description":"SQL Server with Access Virtual User Group","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/accessusergroups.org\/sql-server-with-access\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/ac5d08948c70df3e3ac5e12de3272a62","name":"Susan Pyne","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/accessusergroups.org\/sql-server-with-access\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ffbaa6e6f4e682364c27b9f21ffd05b?s=96&d=mm&r=g","caption":"Susan Pyne"},"description":"I started with the firm as a Sales Assistant and over the years worked my way up to Senior Access developer, I\u2019m certified on Power BI, with more certifications coming. I'm also the chapter coordinator for AccessUserGroups.org, a worldwide organization of Access virtual chapters that meet each month online to discuss a range of topics in different languages. I LOVE working with Access and learning new technologies!","url":"https:\/\/accessusergroups.org\/sql-server-with-access\/author\/susan-pyne\/"}]}},"_links":{"self":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/1284"}],"collection":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/users\/103"}],"replies":[{"embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/comments?post=1284"}],"version-history":[{"count":1,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/1284\/revisions"}],"predecessor-version":[{"id":1285,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/posts\/1284\/revisions\/1285"}],"wp:attachment":[{"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/media?parent=1284"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/categories?post=1284"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/accessusergroups.org\/sql-server-with-access\/wp-json\/wp\/v2\/tags?post=1284"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}