Inconsistent Add SQL statement

[Terry Lawson]

I'm getting inconsistent (i.e. not always repeatable) errors when (in VBA) I try to add data to a previously emptied table.

The SQL statement includes three ascending sorts into a table which is not indexed and works OK when run as a stand alone Query.

When run in VBA as a Docmd.RunSQL(SQLtext) the resultant table is not always sorted by the three ascending fields.

I use the same table both before and after the add SQL statement by using the "set rsWorkTable=currentdb.openrecordset (''Table'')" method/action to update the data in Table1.

I'm certain that I always empty Table before the SQL and doing "set rsWorkTable = nothing" after each appropriate set command.

Suggestions?

Regards

Terry Lawson

 

[SOS]

Just for you to note - tables are not stored in any specific order. If you want an order to things use a QUERY with an Order By clause for whatever you need it to be. It is a common error for people to make thinking that they can use a table and expect the data to be in a particular order. Setting an order on the table doesn't work either because that is only good for the VIEW that you get when looking at the table. When looking at the table you really are not looking at the table, but instead are looking at a "behind-the-scenes" query.

 

[Terry Lawson]

Thanks

Here is the SQL Statement:

SQLText = "INSERT INTO Worktable1 ( field17, field19, Field1, field2, field3, field4, " & _
" field18, field6, field7, field10, field11 ) " & _
" SELECT SFHGSTMA.[Gen Section], SFHGSTMA.[Gen Page], " & _
" SFHGSTMA.[Product Title], 'SSX-' & [SFHG Code] AS Expr1, " & _
" SFHGSTMA.Format, SFHGSTMA.[Product Description], SFHGSTMA.[Sell Price], " & _
" SFHGSTMA.Weight, SFHGSTMA.[Vat Code], SFHGSTMA.[New?], SFHGSTMA.[SFHG Supplier] " & _
" FROM SFHGSTMA " & _
" WHERE (((SFHGSTMA.Genfair) = True) And ( SFHGSTMA.[Discontinued?]) = False)) " & _
" ORDER BY SFHGSTMA.[Gen Page], " & _
" SFHGSTMA.[Product Title], SFHGSTMA.Format; "
DoCmd.RunSQL (SQLText)

Already contains the Order By clause as suggested

Works a treat when run as a stand alone query, but not as VBA.

Any further thoughts?

Regards

Terry Lawson