Append query sort problem, but only when called from a within a module

[markcrobinson]

Scratching my head.



I have an append query with a multi-level sort on two fields that works fine when I run the query normally.
When I run the query in a vb program with the docmd.openquery ("Queryname") the sort doesn't work - table isn't in the correct order.
When I run the Query in the Immediate window, it works fine.

My DoCmd.Openquery command follows a docmd.runsql "UPDATE" command on the same table.
Thinking that maybe it was a timing issue, I stuck a MsgBox between the docmd.runsql and the docmd.runquery and all works find.

Best guess is the docmd.openquery is running before the docmd.runsql completes.
Is this possible?
If so, Is there a better way to insert a delay than put up a msgbox?

[June7]

You use RunSQL to execute an UPDATE, why not RunSQL for INSERT?

Why should sort order of INSERT even be an issue? Sort in SELECT queries.

[markcrobinson]

There is a meaning behind my madness. I like the RunSQL format, and usually use it but this last Append query is a complex one that will change frequently. The Query GUI is an asset here.
I'm preparing a file for export; taking the import, massaging it, then sending it with the Append query, in the proper order, to the Output table.

[June7]

Change in what way? How does a saved query object provide flexibility? I don't programmatically run action query objects, I use CurrentDb.Execute on an SQL string I build in code.

[markcrobinson]

I really appreciate your feedback and will consider your suggestions. It sounds like you are well above my pay grade. I do frequently run action query objects programatically but don't understand why this one isn't working without the delay.

[June7]

Neither do I and would have to do some testing.

If you want to provide db, follow instructions at bottom of my post.

[Micron]

table isn't in the correct order.

Not trying to be snippy here, but who cares? You rarely ever have to worry about how a table sorts its records - nor should you. A table has oft been compared to a bucket of marbles, i.e there is no sort or guarantee of a sort. Not even when it uses autonumbers. Sorting is what queries do, and forms based on them will abide by the sort.

Only once or twice in 20 years did I ever have to worry about a sort in a table but I can't recall why. That was controlled via a number field that the table was sorted on, but those were program support tables, not regular record keeping tables. Maybe you have a valid case as to why the records must be entered in a particular order. If so, I'd be interested to know what that is. I'll speculate here that the designers of Access considered the analogy I gave when they decided not to control the order of an append query - assuming that the behaviour is normal and I'm not off my rocker.

EDIT:

Best guess is the docmd.openquery is running before the docmd.runsql completes. Is this possible?

MY money is on No, not possible.