I am trying to update an Access table through ADO. I want the records added to the Access table to be in the same order as my source cursor, by "Unit_ID." The records added to the Access table are not in the same order as my source cursor when I use INSERT INTO hsbCursor FROM MEMVAR to prepare my source cursor. However if I use SELECT hsbCursor and APPEND FROM DBF("hsbVisual") commands, the Access table is in the correct order.

The main reason I want to use the INSERT INTO command is because I have several Double Integer fields in my hsbCursor that are initialized to a 0.0000 value. I really need them to be .Null. so they will show as empty if they are indeed empty instead of 0.0000 in my final Access table.

The steps I take are below:

I first CREATE CURSOR as follows:

CREATE CURSOR HSBCursor (Lab_Ord_ID C(25) NULL, Sampled_By C(40) NULL,
Report_Ord C(25) NULL,Syringe_ID  C(20) NULL, Lab_Sample_ID C(25) NULL,
Unit_ID C(25) NULL, Sample_dt T NULL, Received_dt T NULL,
dielectric_breakdown_1mm B(4) NULL, dielectric_breakdown_2mm B(4) NULL,
 dielectric_breakdown B(4) NULL, 
..... and several more fields )

..then I run my first SELECT statement

SELECT ALLTRIM(STR(VAL(Upsino))) AS Unit_ID,Sampled_By C(40) NULL, 
      Report_Ord C(25) NULL, Syringe_ID  C(20) NULL, Lab_Sample_ID C(25) NULL
        .... several more fields

      FROM Visual 
	INTO CURSOR hsbVisual ;
	ORDER BY Unit_ID ASC, Sample_dt DESC

..then I SET NULL ON and update my hsbCursor.

SET NULL ON
  SCAN
    SCATTER MEMVAR
    INSERT INTO HSBCursor FROM MEMVAR  
    RELEASE ALL  
  ENDSCAN
SET NULL OFF

  I run several more SELECT statements and continue to update
several fields in the hsbCursor from various tables using SCATTER....
and GATHER MEMVAR commands.

...then I use ADO to update my Access table. I want the table to be
in Unit_ID order as my intial cursor is.

#DEFINE adOpenKeyset 1
#DEFINE adLockOptimistic 3
	
#DEFINE DATA_SOURCE lcDataSource

cnn = CREATEOBJECT( "ADODB.Connection" )
rst = CREATEOBJECT( "ADODB.Recordset" )


*  Open the connection
cnn.Open("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + DATA_SOURCE + ";")


*  Open for Updating recordset
rst.Open ("SELECT * FROM [Sample Upload]", cnn, adOpenKeyset, adLockOptimistic)

SELECT HSBCursor
GO TOP

SCAN
	* Add a new Record to Recordset
	rst.AddNew()
      WITH rst
        .Fields( "Unit_ID" ).Value = ALLTRIM(HSBCursor.Unit_ID)					
        .Fields( "Report_Order_ID" ).Value = ALLTRIM(HSBCursor.Report_Ord)				
        .Fields( "Syringe_ID" ).Value = ALLTRIM(HSBCursor.Syringe_ID)				
        .Fields( "Lab_Sample_ID" ).Value = ALLTRIM(HSBCursor.Lab_Sample_ID)
	..... several more fields
      ENDWITH

      rst.Update()
ENDSCAN

Keep in mind when I prepare my source "hsbCursor" using APPEND FROM DBF() command the Access table ends up in the correct order. However if I use INSERT INTO to prepare my source, the final Access table doesn't stay in Unit_Id order as my source is.

Thank you for your help. I am totally baffled.