Crosstab query ODBC Fails - AYS2000

[Steve B]

I have modified Duane Hookom's brilliant AYS2000 questionnaire database by
linking the tables via ODBC to SQL Server 2000. Everything so far seems to be
running OK except for the x-tab query qxtbQstnTextRspnsxCount which returns
the error "ODBC--call failed.". When the appropriate tables are local
everything works. Duane's sql for the query is:

PARAMETERS [[Forms]!fmnuReportSelect![cboSrvID]] Long;
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.SrvID)=[Forms]![fmnuReportSelect]![cboSrvID]) AND
((tblQuestions.QstnType)="Stat" Or (tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
PIVOT "Number of Responses" In ("Number of Responses");

What is happening here? I've exhausted all my searching capabilities and
can't figure it out.
Thanks for any help

 

[Duane Hookom]

Access sometimes screws with the parameter bracketing which should be:
PARAMETERS [Forms]!fmnuReportSelect![cboSrvID] Long;

HTH

 

[Steve B]

Thanks for the reply Duane. I had noticed that earlier and tried it but still
no go. I had seen several sites that indicated there can be ODBC issues with
xtab queries but none offered any solution. Here is my version (essentially
with Srv changed to Audit):

PARAMETERS [Forms]!fmnuReportSelect![cboAuditID] Long;
TRANSFORM Count(tblResponses.Response) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNo, tblQuestions.QstnText,
tblResponses.Response, tblQuestions.AuditID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.AuditID)=[Forms]![fmnuReportSelect]![cboAuditID]) AND
((tblQuestions.QstnType)="Stat" Or (tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNo, tblQuestions.QstnText,
tblResponses.Response, tblQuestions.AuditID
PIVOT "Number of Responses" In ("Number of Responses");

Thanks again

Access sometimes screws with the parameter bracketing which should be:
PARAMETERS [Forms]!fmnuReportSelect![cboSrvID] Long;

HTH

--
Duane Hookom
MS Access MVP

I have modified Duane Hookom's brilliant AYS2000 questionnaire database by
linking the tables via ODBC to SQL Server 2000. Everything so far seems to
be
running OK except for the x-tab query qxtbQstnTextRspnsxCount which
returns
the error "ODBC--call failed.". When the appropriate tables are local
everything works. Duane's sql for the query is:

PARAMETERS [[Forms]!fmnuReportSelect![cboSrvID]] Long;
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnLvl1, tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.SrvID)=[Forms]![fmnuReportSelect]![cboSrvID]) AND
((tblQuestions.QstnType)="Stat" Or (tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnLvl1,
tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
PIVOT "Number of Responses" In ("Number of Responses");

What is happening here? I've exhausted all my searching capabilities and
can't figure it out.
Thanks for any help

 

[Duane Hookom]

Are these just linked tables from SQL Server or is this an ADP? ADPs don't
support crosstabs but the linked tables should work.

I would see what happens if you remove all references to the control on the
form.

--
Duane Hookom
MS Access MVP

Thanks for the reply Duane. I had noticed that earlier and tried it but
still
no go. I had seen several sites that indicated there can be ODBC issues
with
xtab queries but none offered any solution. Here is my version
(essentially
with Srv changed to Audit):

PARAMETERS [Forms]!fmnuReportSelect![cboAuditID] Long;
TRANSFORM Count(tblResponses.Response) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnNo, tblQuestions.QstnText,
tblResponses.Response, tblQuestions.AuditID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.AuditID)=[Forms]![fmnuReportSelect]![cboAuditID])
AND
((tblQuestions.QstnType)="Stat" Or (tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnNo, tblQuestions.QstnText,
tblResponses.Response, tblQuestions.AuditID
PIVOT "Number of Responses" In ("Number of Responses");

Thanks again

Access sometimes screws with the parameter bracketing which should be:
PARAMETERS [Forms]!fmnuReportSelect![cboSrvID] Long;

HTH

--
Duane Hookom
MS Access MVP

I have modified Duane Hookom's brilliant AYS2000 questionnaire database
by
linking the tables via ODBC to SQL Server 2000. Everything so far seems
to
be
running OK except for the x-tab query qxtbQstnTextRspnsxCount which
returns
the error "ODBC--call failed.". When the appropriate tables are local
everything works. Duane's sql for the query is:

PARAMETERS [[Forms]!fmnuReportSelect![cboSrvID]] Long;
TRANSFORM Count(tblResponses.Rspns) AS CountOfRspns
SELECT tblQuestions.QstnID, tblQuestions.QstnLvl1,
tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QstnID =
tblResponses.QstnID
WHERE (((tblQuestions.SrvID)=[Forms]![fmnuReportSelect]![cboSrvID]) AND
((tblQuestions.QstnType)="Stat" Or (tblQuestions.QstnType)="Demo"))
GROUP BY tblQuestions.QstnID, tblQuestions.QstnLvl1,
tblQuestions.QstnLvl2,
tblQuestions.QstnLvl3, tblQuestions.QstnText, tblResponses.Rspns,
tblQuestions.SrvID
PIVOT "Number of Responses" In ("Number of Responses");

What is happening here? I've exhausted all my searching capabilities
and
can't figure it out.
Thanks for any help