Sort Problem

[Don Brizendine]

I have the following in a program in an ADP Access
database:

strSQL = "INSERT INTO dbo.DMFile
SELECT LAST_NAME, FIRST_NAME, LABEL_NAME,
COMPANY_NAME, ATTENTION_LINE, MAIL_STOP, ADDRESS_1,
ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, POSTAL_CODE,
COUNTRY_CODE, KEYCODE
FROM dbo.RESULTS
ORDER BY POSTAL_CODE"

cmd.CommandText = strSQL
cmd.Execute

If the # of records in dbo.RESULTS is not too large (say
5000 records or so), this works fine. However, after a
certain # of records (say 8,000 or so - not really sure
what the magic cut off is), the ORDER BY fails to sort the
records correctly.

If I run this same INSERT statement in SQL Query Analyzer,
it runs fine no matter what the number of records in
RESULTS. Can anybody give me an idea why this is happening
and how to get around it? I've tried creating an SP with
the same statement, but it fails to sort correctly, too.
I've also changed the default max records to 0 so all will
be returned in Access, but that had no effect either.

Any ideas?

Thanks! - Don

 

[Guest]

I figured it out! You must use "TOP 100 PERCENT" in the
Insert Query for ORDER BY to work correctly.

 

[Daran Johnson]

Don,

Why are you ordering before inserting?

-Daran