tag:blogger.com,1999:blog-8134759511126815909.post1989115640176675054..comments2023-08-20T04:10:01.095-04:00Comments on <a href="http://rogersaccessblog.blogspot.com/">Roger's Access Blog</a>: Domain Functions DemystifiedRoger Carlsonhttp://www.blogger.com/profile/12265719129831415014noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-8134759511126815909.post-74357157406228582292012-07-20T10:38:30.043-04:002012-07-20T10:38:30.043-04:00@David
TLDR: Domain functions are really intended...@David<br /><br />TLDR: Domain functions are really intended as a shorthand for VBA (ie, no lengthy recordset declaration) <br /><br />--<br /><br />While all the VBA functions are accessible in SQL (including domain functions), one should avoid co-mingling them as much as possible: it drags performances down b/c of context switching (ie, SQL engine handing over execution to VBA engine, and back).<br /><br />In (Access-flavored) SQL, domain functions can (and should) always be replaced by joins and / or sub-queries; it is more verbose, but also much faster, while staying clear of concurrency issues.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-8134759511126815909.post-15997647442953618612012-07-19T12:49:42.158-04:002012-07-19T12:49:42.158-04:00I have recently discovered DLookup has certain nes...I have recently discovered DLookup has certain nesting limitations, meaning it doesn't like to be used at different interacting levels of a database simultaneously (key word here: simultaneously).<br /><br />For example:<br /><br />If you have DLookup embedded in a query (as either the source for a custom field or for a criterion), and then you also run a DLookup in your VB code that accesses the aforementioned query, you will, without exception, generate an error "Unknown" until you switch the query over to getting its data from a form (or subform) control instead of the embedded DLookup. I had two different DLookups inside of an IIf (Inline-If) as the criterion for one of the fields within my query.<br /><br />I have little to no idea why this causes an error. Perhaps DLookup uses some static or global variables that are state-sensitive. If you try to run it in such a way that requires Access to run DLookup again (like in a query) before the VB code instance of DLookup can return a value, it royally doesn't like that.<br /><br />Now, since it was entirely possible to replace the DLookup with direct data obtained from the control(s) on a form, this wasn't a massive inconvenience. The main problem was how much time it took me to figure out the source of this obscure error, since everything looked like it should work. Also, the error description "Unknown" is not the most explicit or helpful. :p<br /><br />I suppose this serves as a good example of the rule that "one size does not fit all". Having acquired DLookup as a powerful new hammer, we should not then start seeing the whole world as a nail.Anonymoushttps://www.blogger.com/profile/16482453562475270642noreply@blogger.com