Excel ADOB SQL Recordsets, ORDER BY works on one xlsx file but not on others? -

why order work on xlsx files , not on others?

i build sql statements (using adob) move data closed workbooks (i need speed , use sql sort data) active workbook. below 2 strings use , both work associated files.

   mysql = "select * [" & sourcesheet$ & "$" & sourcerange$ & "] order " & targetsortcolumn & ";"   'this works data dictionary.     mysql = "select * [" & sourcesheet$ & "$" & sourcerange$ & "];"                                     'this works cost file. 

i cannot find fundamental differences between 2 files. trying copy worksheet each closed workbook current workbook , sort order by.

i jump error trap, when using order on cost file. when hit second statement below triggers error trap.

set con = createobject("adodb.connection") set data = createobject("adodb.recordset") 

i have used debugger check , parameters passed procedure correct each file. pass in strings , use strings build sql statements there no mismatch there.

i have opened both files , resaved them ensure no basic problem files. using excel 2010.

the worksheets in both files have correct names passed subroutine correctly.

both worksheets have data starting @ cell(1,1).

remember 2 sql statements work correctly respective files.

why can not use order on files?

i have agitated little grey cells vigorously on problem, guidance appreciated.

i got it, don't know why, perhaps null cells crash order by clause?

i added where clause removed rows column code <> null use order by code clause without difficulty.

mysql = "select * [" & sourcesheet$ & "$" & sourcerange$ & "]          code <> null          order code; 

that solved issue. not understand why order not working in case empty values. expected them @ top of sort order.

the where clause blindingly fast compared vba equivalent.

anyway, using where clause sql works excellency! little grey cells @ peace.

craigm