ODBC Query anomaly: incorrect sort sometimes

[Kronix]

I'm merging a couple of ODBC tables (Invoice and Customer in a Firebird database) using an Access query which sorts by date first and then customer number (both descending), and then exports the table to an Excel file. I have noticed that sometimes the sorting is off. For example, if I want a list from the last three months (September 16 to January 16), the first entries should be from January 16, but the first 40 entries are instead from December 31, then it will jump Forward to January 16 (where it should have correctly started) and continue sorting correctly all the way down, except the 40 entries from December 31 are not where they should have been. Where the 40 entries should have been, it just immediately jumps to the next record after the 40.

It's as if it were correct except that 40 entries from December 31 where cut out and pasted to the beginning. And it's not all of the entries from December 31, just a block of 40 taken out of the middle of that day. It's not that it's somehow sorting by customer number first either, because the customer numbers in the failing 40 should also appear further down.

The majority of the time the Excel table is completely correct. The anomaly only appears about 1 in 15 times the export is run.

What can be the culprit here? Can ODBC connections result in incomplete transmissions of data for Access to work with?

 

[Galaxiom]

I'm merging a couple of ODBC tables (Invoice and Customer in a Firebird database) using an Access query which sorts by date first and then customer number (both descending), and then exports the table to an Excel file.

Maybe you used the wrong word by mistake here but the symptoms you describe are certainly possible with what you did say. You should be exporting a query. A table has no order.

 

[Kronix]

I am inserting the query into a temporary table. The contents of the table are erased before each insert.

The reason I am doing it that way is because the query I was using needed to give results between two dates which are in controls on the form. But the query designer gave me an error when I tried to use the BETWEEN condition with the controls values. So I wrote some VBA code and inserted into the table the SQL concatenated with the control values manually.

 

[Galaxiom]

As I said previously, tables have no order so that is your problem. You could add an order in a query against the table and export that. But that seems rather clumsy. Better to find out what is wrong with the query.

I'm not totally clear on precisely what you are doing and you have not described the errors. However note that any method of CurrentDb will throw errors if you refer to an Access object such as a form. Only Methods of the Application such as DoCmd can handle Access objects.

If this is the root of the problem you could modify your code to load the control values as query parameters.

 

[Pat Hartman]

Make sure that the controls on the form are set to have a format of Short Date. This is necessary for unbound controls if you want to use them as dates.

Whenever row order is important to you, you MUST use an order by clause in the query.

When you link to SQL Server tables, make sure that the data type of the date fields are recognized by Access. If you are using the old, default driver, the ONLY option is DateTime. All other dates will be seen as strings and will act accordingly.

As Galaxion mentioned, tables and queries are unordered sets. the query engine retrieves data in whatever order it finds convenient so it isn't unusual at all to see recordsets where the rows are not in a particular order. You must SORT them if it matters to you.