ODBC Failed Error When Using INNER JOIN

[aliciap1214]

I am getting a weird "ODBC Call Failed" error when trying to do an INNER JOIN on two Microsoft Access 2003 queries. The Access queries are going against a linked SQL Server 2005 table. The two queries are very simple, and both run without issue individually:

qryMEGAOVTM_Current:

SELECT M.WkStartDate, M.Flags, CInt(NZ(M.Drops,0)) AS CurDrops, IIf(forms!frmMainMenu!TotalCurDrops=0,0,CurDrops/forms!frmMainMenu!TotalCurDrops) AS CurPOD
FROM tblMEGAOVTM AS M
WHERE (((M.WkStartDate)=forms!frmMainMenu!CurStart))
ORDER BY M.Flags;

qryMEGAOVTM_Previous:

SELECT M.WkStartDate, M.Flags, CInt(NZ(M.Drops,0)) AS PrevDrops, IIf(forms!frmMainMenu!TotalPrevDrops=0,0,[PrevDrops]/forms!frmMainMenu!TotalPrevDrops) AS PrevPOD
FROM tblMEGAOVTM AS M
WHERE (((M.WkStartDate)=forms!frmMainMenu!PrevStart))
ORDER BY M.Flags;

The issue comes up with I try to create an inner join on the two queries using the "Flags" field in the join (or even place the two queries in a new query without joining):

qryMEGAOVTM:

SELECT C.Flags, C.CurDrops, C.CurPOD, P.PrevDrops, P.PrevPOD, C.CurDrops-P.PrevDrops AS Diff
FROM qryMEGAOVTM_Current AS C INNER JOIN qryMEGAOVTM_Previous AS P ON C.Flags=P.Flags;

I get the following error:

"ODBC – call failed. (Error 3146)"

The dbo.tblMEGAOVTM table's setup is as follows:

WkStartDate (smalldatetime, null)
WkEndDate (smalldatetime, null)
Flags (varchar(2), null)
Drops (int, null)
ID (int, not null)

Sample Data:

tblMEGAOVTM
WkStartDate WkEndDate Flags Drops ID
2/2/2009 2/8/2009 TV 01 2
2/2/2009 2/8/2009 TW 01 3
2/2/2009 2/8/2009 TX 01 4
2/2/2009 2/8/2009 T5 01 5
2/2/2009 2/8/2009 15 01 6
2/2/2009 2/8/2009 17 01 7

NOTE: I have tried using pass-through queries (without the parameters of course), and it works without issue. The problem is I need to be able to use the parameters passed from the form.

I have also set all the queries so that ODBC Timeout = 0.

Any help or suggestions you may have would be greatly appreciated.

Alicia

 

[Banana]

Hi-

Not a direct answer as I'm not totally familiar with SQL Server but can tell you that if you try this article, you can get more specific error message that may be helpful in diagnosing the problem.

For the test, you need to try and open a recordset using the same query you had problem with in VBA, which the article has code for, and trap the error so you can what actually went wrong.

Post back if you need further assistance.

 

[aliciap1214]

Thank you. I'll give that a try.