Dlookup in a remote query (1 Viewer)

Hi,
I am using Dlookup in a query in database A. I want to run this query from database B, via a VB .execute statement. But the query fails, with error #3078. I believe it's failing because of the DLookup. The table that Dlookup needs to use is, of course, in database B. My theory is that Dlookup is trying to look in database A, where the table doesn't exist.

I know several ways I can resolve this, but I'm curious. Is there a way to structure the DLookup statement to make it clear which database for it to look in?

DLookup()s do not belong in queries. The vast majority of DLookup()s can be replaced by simply expanding the query to join to the lookup table. The effect of this will be a substantial performance improvement if you have more than a few hundred rows in your tables.

Link the table from dbA to dbB.
Add the lookup table to your query.

If you insist on using DLookup(), change it to use a query rather than a table as the recordsource. The query can specify a remote table. I don't remember the exact syntax. Look it up in SQL help. You can find SQL help by opening help, opening the table of contents, and scrolling down the list to SQL.

Pat,
I would love to know a faster way to do this.

Here's the SQL that I'm using:

INSERT INTO FinalsImports_6 ( RefDes, DefectCode, TestDate, RepairsID, Qty, PartNo )
SELECT FInalsImports_4.RefDes, FInalsImports_4.DefectCode, FInalsImports_4.TestDate, CLng(DLookUp("ID","FinalsImports_2","pcanoun='" & [noun] & "' and Deliverydate=#" & DMax("Deliverydate","finalsimports_2","pcanoun='" & [noun] & "' and Deliverydate<#" & [testdate] & "#") & "#")) AS RepairsID, FInalsImports_4.Qty, nz([FirstOfComponentItemNo],0) AS Expr1
FROM FInalsImports_4 LEFT JOIN BOMOutRefDesOnly ON (FInalsImports_4.AssyItemNo = BOMOutRefDesOnly.PCAItemNo) AND (FInalsImports_4.RefDes = BOMOutRefDesOnly.RefDesOnly);

I'll try to explain what I'm trying to do:
FinalsImports_4 contains fields TestDate and PCANoun
FinalsImports_2 contains fields DeliveryDate, PCANoun, and ID. ID is the unique identifier for DeliveryDate and PCANoun.

This data is being used to calculate defect rates; however our customers are very undependable in reporting quantity of units tested, so for my denominator I am forced to use quantity of units we delivered instead.

So, I want to assign the ID to the TestDate-PCANoun that most closely matches the DeliveryDate-PCANoun. DLookup/DMax combo is my way to do this. It works, but yes, it would be nice if it worked a little faster.

Can you illustrate how I might do this faster?

BTW, i've gotten around the original problem just by creating a link in Database A to the table in Database B.

Create a "totals" query that returns the max delivery date for each noun where the deliverydate is less than the test date.

Join to the above query rather than using the nested DLookup() and DMax().

Thanks so much Pat. As usual, you are so right. I made the change and the query runs in about 1/5 the time without the Dlookups.

Just want you to know how much I appreciate your help. As a "frequent poster" here, I do so appreciate your uncanny ability to get to the heart of each question and provide a really helpful answer.