ODBC Error opening report in preview mode in Access with SQL Server data source (1 Viewer)

Warden

New member
Local time
Today, 09:13
Joined
Aug 22, 2019
Messages
2
I am tasked with converting an old MS Access Application to a MS Access front end and SQL Server back end. I have several reports (all labels) which are giving me an SQL Server ODBC error when I open them in preview mode but which open fine in report mode.

This command fails:
DoCmd.OpenReport Me.cmbReportSelection, acPreview, , whereStr


This command works
DoCmd.OpenReport Me.cmbReportSelection, acViewReport, , whereStr

until I switch to preview mode then it fails with the same error as if I opened it in preview mode.

Error message received:
Microsoft Access
ODBC -- call failed

[Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique (#169)

Sometimes, MS Access crashes completely after clicking ok on the error, sometimes, it allows me to continue.

Environment:
MS Access 2013 (accdb format)
SQL Server 2016


I have put enough displays in to determine its not likely the error message is correct unless Access is doing something after I provide the data source, where condition, filter and order properties. Given that the code works if I open it in acViewReport instead of acPreview, I am not sure its anything I am doing.

The code also seems to work in a version of the database that connects to an MS Access backend and in the original system's .mdb format.

it also doesn't matter if there is a where condition, filter on or order on set. The open report is being called from a report selection menu that include options for a variety of filter, sort, and group criteria that can be used for a number of reports but its only the 3 reports that are "labels" that seem to have this problem.

I have other “label” reports in the system which do not show this problem but are not called through the same process, nor do they use select statements as the record source, they use a query. I tried converting the select statement on the broken reports to a query but it didn’t change the outcome. I have also tried moving the select to a view on the server and it also does not impact the result.

The code flow is:
from the report selection form, select a report from a pull down, fill in any desired filter, sort and group criteria, click on a command button to preview the report.

The command button code processed the filter condition options to assemble a where condition for the report "whereStr" variable

The individual report on-load functions call a common function which handles sort options and a few other things and then the load function takes care of setting the filter and order by properties of the report.

The RecordSource of the reports is a Select Statement. one example is below

Code:
SQL
SELECT tblAllFacilities.FacLicenseNumber, tblAllFacilities.FacMedicareNumber, tblAllFacilities.FacName, tblAllFacilities.FacName2, [tblHHA/Hospice Facilities].[Mail Address 1], [tblHHA/Hospice Facilities].[Mail City], tblAllFacilities.FacCity, [tblHHA/Hospice Facilities].[Mail State], [tblHHA/Hospice Facilities].[Mail Zip], tblAllFacilities.FacNotActiveDate, [tblHHA/Hospice Facilities].PhoneNumber, [tblHHA/Hospice Facilities].[Contact Lst Nme], [tblHHA/Hospice Facilities].[Contact Fst Nme], [tblHHA/Hospice Facilities].[Contact Phone], [tblHHA/Hospice Facilities].[Contact Ext], tblAllFacilities.FacAdminName, tblAllFacilities.FacAdminLast, tblAllFacilities.FacAdminTitle, tblAllFacilities.ToBeDeleted, tblAllFacilities.[Facility Type], tblAllFacilities.FacStatusCode
FROM [tblHHA/Hospice Facilities] RIGHT JOIN tblAllFacilities ON [tblHHA/Hospice Facilities].[670FacIDLink] = tblAllFacilities.FacID
WHERE (((tblAllFacilities.FacNotActiveDate) Is Null) AND ((tblAllFacilities.ToBeDeleted)=False) AND ((tblAllFacilities.FacStatusCode)="Active") AND ((tblAllFacilities.FacClosedDate) Is Null));

I am expecting the label reports to come up in preview with the correct filter and sort applied.

What I am getting is the ODBC error saying I have a duplicate Order by column unless I change the mode the report is opened in. Then it works till I switch to preview mode.
 

isladogs

CID VIP
Local time
Today, 17:13
Joined
Jan 14, 2017
Messages
16,388
It should be acViewPreview not acPreview

Also...welcome to AWF.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:13
Joined
Oct 29, 2018
Messages
19,164
Hi. Welcome to AWF! My only guess is maybe your OnLoad code might have something to do with the problem. Just as a test, have you tried disabling it just to see if the error goes away?
 

Warden

New member
Local time
Today, 09:13
Joined
Aug 22, 2019
Messages
2
SOLVED

It turns out that the 3 label reports in question had grouping criteria defined that were just doing sorts yet the code was setting the orderon property to implement the sorts. Not something I was expecting for mailing labels.

I also found that MS Access was not sending the same select to SQL server when the report was opened in report view and print preview modes.

First select is from going in in report mode and contains the order by specified in the group by part of the report definition
*
SELECT "FacName" ,"FacAdminName" ,"FacAdminLast" ,"FacAdminTitle" ,"FacAddress" ,"FacCity" ,"FacState" ,"FacZip" FROM "dbo"."tblAllFacilities" WHERE (((("FacNotActiveDate" IS NULL ) AND ("FacClosedDate" IS NULL ) ) AND ("FacStatusCode" = 'Active' ) ) AND ("ToBeDeleted" = 0) ) ORDER BY "dbo"."tblAllFacilities"."FacName" ,"dbo"."tblAllFacilities"."FacName2"
Go
*
The 2nd selection is when I switched from report to preview mode and it made a 2nd call which included both the grouping order on fields and the report orderby field list.
*
SELECT "FacName" ,"FacAdminName" ,"FacAdminLast" ,"FacAdminTitle" ,"FacAddress" ,"FacCity" ,"FacState" ,"FacZip" FROM "dbo"."tblAllFacilities" WHERE (((("FacNotActiveDate" IS NULL ) AND ("FacClosedDate" IS NULL ) ) AND ("FacStatusCode" = 'Active' ) ) AND ("ToBeDeleted" = 0) ) ORDER BY "dbo"."tblAllFacilities"."FacName" ,"dbo"."tblAllFacilities"."FacName2" ,"dbo"."tblAllFacilities"."FacName"
go
*
interesting that it re-queried the database switching from report view to print preview mode
 

shabbirug

New member
Local time
Today, 19:13
Joined
Apr 17, 2021
Messages
1
Solved: I have removed sorting and grouping from Report and its worked. also search this: Error (ODBC--call failed) when you open a table that is linked to a SQL Database instance - Office | Microsoft Docs
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:13
Joined
Oct 29, 2018
Messages
19,164
Solved: I have removed sorting and grouping from Report and its worked. also search this: Error (ODBC--call failed) when you open a table that is linked to a SQL Database instance - Office | Microsoft Docs
Hi. Welcome to AWF!
 

Users who are viewing this thread

Top Bottom