addressing performance issues

[The_Doc_Man]

9. There is never a good reason to use domain functions. Learn SQL.

Never say Never. Say "Hardly Ever" and reserve the "Hardly" for those limited cases in which the thing you were looking up wasn't in the table or query that you had open at the moment. AND it represents a singular I.e. non-repeating lookup, like something you might do in a Form_Open or Form_Load event but not in a Form_Current event. At which point there might be a reason to allow a domain function to creep in as a matter of ease of coding, but also because if you didn't use the domain function, you would have to open a recordset anyway (because remember I said this thing isn't in the table/query your form is bound to.)

 

[Pat Hartman]

You don't want to use domain function in queries that return more than a single record or in any VBA code loops. Each domain function runs a separate query so if your query returns 10,000 records, each domain function it contains would run 10,000 times!!!!!

In my early days when all I knew was what I saw in samples, I had a code loop that processed 300,000 records. It was converting data from the old system to the new and the loop did three dLookup()'s to convert old codes to new values. It took 4 hours to run. I was also a little new to SQL but I thought about it and decided to change the domain functions to left joins. The update loop took 20 minutes. I thought some more and converted the whole shebang to an append query and then it took 1.5 minutes. THATS why we don't use domain functions inside loops and also why we use action queries rather than VBA loops to update/insert bunches of records.

 

[The_Doc_Man]

As an addendum to post #21, I completely concur with Pat that a Domain function in a query is a performance issue waiting to happen. My earlier comment was oriented to form contexts.

 

[DickyP]

As an addendum to post #21, I completely concur with Pat that a Domain function in a query is a performance issue waiting to happen. My earlier comment was oriented to form contexts.

If you read the documentation that used to come with Access, that was the design purpose of domain functions so Doc Man is absolutely correct. I don't think it ever occurred to me to use one in query!