Query works in Jet 3.5.11 and not Jet 4.0

[ERDaniels]

I am using a query in Jet 3.5.11 on an IIS 4.0 and NT server that displays data from two Access 97 tables. This same query when running on Jet 4.0 produces a 80005004 "Unspecified Error". What can be causing this problem? My Query = "Select complete.dbUserID, names.dbName, complete.dbCmptDt, names.dbCode FROM names, complete WHERE names.dbCode LIKE '%" & SearchCode & "%' AND names.dbUserID = complete.dbUserID ORDER BY dbCode, dbName;"

 

[cheerio]

Try:

Select complete.dbUserID, names.dbName, complete.dbCmptDt, names.dbCode
FROM names
JOIN complete
ON names.dbUserID = complete.dbUserID
WHERE names.dbCode LIKE '%" & SearchCode & "%'
ORDER BY dbCode, dbName;

Hiding the join conditions in the where clause is creating a crossjoin of every combination of rows in the one table with every combination of rows in the other and leaving the WHERE clause to sort it out. If the WHERE clause then acts on the wild card search first you could cause the jet engine to fall over.

Using a JOIN ON construction will limit the number of rows to be scanned by the wild card search. This is important because no search beginning with a wild card is going to be efficient at the best of times.

Ken

 

[ERDaniels]

Thanks, Cheerio. After receiving your response, I rewrote my query as an Inner Join and tested it on the Jet 3.5.11 server. It worked fine. Then I copied it to the Jet 4.0 and received the same 'Unspecified Error' as before. Any additional ideas?

 

[Quehay]

Why do you have the mix of single & double quotation marks?

LIKE '%" &
rather than LIKE "%" &
OR LIKE '%' &

The join syntax should work with the WHERE clause (ANSI 89 vs. ANSI 92--it does in 2K anyway).

 

[cheerio]

Try a different track - have you looked at KB Q183060

Ken

 

[RSGB]

Hi

Another tack is the version of Jet 4.0 which caused me mucho problems - check:

MSKB "ACC2000: Access Quits When You Add Data Through a Query After You Upgrade to Jet 4.0 Service Pack 5"

Updating Jet 4 to version SP6 fixed this for me...

Cheers

RSGB