bogus SQL error message

[AlanS]

In Access 97, I have an append query involving 1 local table plus 3 tables linked in from SQL Server 2000 (the SQL View of the query is shown below). I also have a macro set up to clear several local tables (including the local table involved here) and then run a number of queries, including this append query. When the macro gets to this query, it always blows up with the error message:

[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)

In fact, the query has only a single field in the order by list, and that field is specified only once. Sometimes, I have been able to get around this problem by manually running this and all subsequent queries after the macro blows up, but now even that doesn't work.

What is causing this error and how can I get around it?

INSERT INTO KronosMostRecentActivity ( ActivityCode, ActivityStartDate, SSN, InitialVisitDate )
SELECT dbo_Activities.ActivityCode, dbo_Activities.ActivityStartDate, KronosT1.SSN, KronosT1.InitialVisitDate
FROM ((KronosT1 INNER JOIN dbo_ApplCert ON (KronosT1.InitialVisitDate = dbo_ApplCert.InitialVisitDate) AND (KronosT1.SSN = dbo_ApplCert.SSN)) INNER JOIN dbo_Enrollment ON (dbo_ApplCert.InitialVisitDate = dbo_Enrollment.InitialVisitDate) AND (dbo_ApplCert.SSN = dbo_Enrollment.SSN) AND (KronosT1.EnrollmentID = dbo_Enrollment.EnrollmentID)) INNER JOIN dbo_Activities ON dbo_Enrollment.EnrollmentID = dbo_Activities.EnrollmentID
ORDER BY dbo_Activities.ActivityStartDate DESC;

 

[Kodo]

you wouldn't happen to be using the Kronos Time keeping software would you?

 

[AlanS]

Yes, we are. Actually, another department is setting it up, and I have to extract from our administrative system data for use by Kronos. The selection criteria are complex and ever changing, hence this macro.

 

[Kodo]

I hope you don't mind me picking your brain. Our facility may be moving to the new SQL2000 version soon. Have you used the previous version that ran off 97? if so, does the SQL 2000 database resolve the many to many relationships that were so poorly created for employee Notes and employee Hour? Reason being is I spent the better part of 6 months developing a database that uses their ACM db for data extraction for certain time policies that they put in place. Let me tell you, having that many to many on those two tables was a huge mistake and made my life a miserable hell for a long time. I'm seriously hoping that they are using a one to many on those two tables now. Have you any insight into this?

aside from my question, have you updated to MDAC2.8 ?

 

[AlanS]

I'm afraid I can't be of much help to you. We only used a previous version of SQL Server very indirectly, by linking Access databases to some of its tables for reporting purposes. The actual server was set up an maintained by a third party.

I also have only the most cursory understanding of how Kronos is organized, as it was purchased by, and is being managed by, a different department.

I don't know what MDAC2.8 is.

 

[Kodo]

Thanks for the info anyway

http://www.microsoft.com/downloads/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

it contains the core data components including SQL server drivers and might resolve your issue.