Table creation/ export mixed up issue.

[mattkorguk]

Hi All,
I have a little puzzler here.
I'm creating and building a local table using 30+ queries, all in vb. On my development machine it all works fine. It's now been reported by 2 users that the output has become jumbled up! (Each row is added by going through the vb code, added one line at a time and then exported to Excel.)
If you run a compact and repair on their local database front end it works fine for the 1st extract and the next one is mixed?! :banghead: Even the local table is mixed so it's not Excels fault.
Is there a filter option they may have selected somewhere? They do not view the built table, just click a button to export the report to Excel.
Any suggestions?
Cheers
Matt
Version in use: Me - Full 2003, them Full 2010.

 

[spikepl]

jumbled up? Mixed? Use terms with well-defined meaning not requiring guess work.

 

[mattkorguk]

OK, sorry. Here is a sample of the code, the table is created and a number of append queries add rows to the table, should be pretty straight forward. When the created table is opened, the rows are not in the same order as stated in the code.

DoCmd.OpenQuery "qryReport-TabCreate", acViewNormal 'Create Table and append row 1
    DoCmd.OpenQuery "qryReport-TabMAadd", acViewNormal ' Append row 2
    DoCmd.OpenQuery "qryReport-TabLGM3", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMDocAdd", acViewNormal ' Append row 3
    DoCmd.OpenQuery "qryReport-TabLGM4", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMJustAdd", acViewNormal 'Append row 4
    DoCmd.OpenQuery "qryReport-TabLGM13", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMBinderAdd", acViewNormal
    DoCmd.OpenQuery "qryReport-TabLGM5", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMCIAdd", acViewNormal
    DoCmd.OpenQuery "qryReport-TabLGM7", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMIOAdd", acViewNormal

Does that help?

 

[Minty]

The order of the rows in the table is undetermined, unless you add a primary key or date that you can sort on.

 

[mattkorguk]

Thanks for that Minty, is that just 2010? All seems fine in 2003?!
Anyway, I've now added this which has solved the issue.

strSQLautonumber = "ALTER TABLE _TabReportTemp ADD [TabNumber] AUTOINCREMENT(1, 1)"
    
DoCmd.OpenQuery "qryReport-TabCreate", acViewNormal
    
    [B]DoCmd.RunSQL strSQLautonumber[/B]
    
    DoCmd.OpenQuery "qryReport-TabMAadd", acViewNormal
    DoCmd.OpenQuery "qryReport-TabLGM3", acViewNormal
    DoCmd.OpenQuery "qryReport-TabMDocAdd", acViewNormal
    DoCmd.OpenQuery "qryReport-TabLGM4", acViewNormal

 

[Minty]

It will depend on what indexes, if any, you have set, but without doing what you have done (Autonumber Field) you cannot guarantee any display order. 2003 seems to pay more attention to the order records are added than 2010, but it cannot and should not be relied on.