Intersystems Cache and MS Access Passthrough

Edit:

May have found the issue but not the solution.

"SELECT * FROM wmhISTORYdETAIL" runs as a passthrough without asking for the DNS.

but

'SELECT Count([wmhISTORYdETAIL].[HistHMNumber] AS CountOfHistHMNumber FROM [wmhISTORYdETAIL] WHERE ((([wmhISTORYdETAIL].[HistMovType])='Receipt') AND (([wmhISTORYdETAIL].[HistMovDate])>=Date()-1) AND (([wmhISTORYdETAIL].[HistMovDate])<Date()));'

asks for the DNS but both are linked to a table that has the password saved.

Any Ideas please?

Rob

Hi

I have created an MS Access database with a passthrough query to our Intersystems Cache WMS system. If I use "SELECT * from thetable"  as the passthough query I can use VB.NET to query the passthrough and it works fine but this dataset getting rather large so I changed it to

"Select field1, field2, filed3 from thetable" but the passthrough no longer works as it did.....it works in MS Access but not from the VB.NET app.

The VB.Net Query is:

SELECT Count([xxx].[HistHMNumber] AS CountOfHistHMNumber FROM [xxx] WHERE ((([xxx].[HistMovType])='Receipt') AND (([xxx].[HistMovDate])>=Date()-1) AND (([xxx].[HistMovDate])<Date()));

where [xxx] is the passthrough query

but now I get an ODBC error in the VB.Net app

"System.Data.OleDb.OleDbException: 'ODBC--call failed.'"

The error/issue appears to be in the SQL but if Im lift it and paste it into the MS Access database. it works?!?!

Any help would be appreciated.

Many Thanks

Rob