Queries in c# and access vba producing different results

Question

I'm in the process of writing a c#/.net4.0 program to replace an access vba module. I'm getting different results from running the same queries.

vba query:

dbs.Execute "INSERT INTO tmpTable ( L1, D1, L2, UserID, FmN ) " & _
  "SELECT Table1.Field1, Table2.Field1, " & _
  "Sum(Table2.Field2) AS SumOfNoWork, '" & stringUser & "' AS Expr1, 'rampup' AS Expr2 " & _
  "FROM Table1 INNER JOIN Table2 ON Table1.Field2 = Table2.Field3 " & _
  "WHERE (((Table2.Field4) = 6)) " & _
  "GROUP BY Table1.Field1, Table2.Field1, '" & stringUser & "', 'rampup';"

c# query:

string exec1 = "INSERT INTO tmpTable ( L1, D1, L2, UserID, FmN ) " + 
  "SELECT Table1.Field1, Table2.Field1, " +
  "Sum(Table2.Field2) AS SumOfNoWork, '" + stringUser + "' AS Expr1, 'rampup' AS Expr2 " +
  "FROM Table1 INNER JOIN Table2 ON Table1.Field2 = Table2.Field3 " +
  "WHERE (((Table2.Field4) = 6)) " +
  "GROUP BY Table1.Field1, Table2.Field1, '" & stringUser & "', 'rampup';";

using (OleDbConnection conn = new OleDbConnection(strconn)) 
{ conn.Open(); OleDbCommand comm = new OleDbCommand(exec1, conn);  
  comm.ExecuteNonQuery(); conn.Close(); }

I compared the results after running both of them. Looking at tempTable from the insert query, fields D1 and FmN are the same, but L1 and L2 differ between the c# and vba version in 11 of the 100 rows. Also, they are inserted in a different order.

There aren't any exceptions in the c# version, but could this be a result of the access expression service? Any reasons why the rows would be inserted in a different order and some different between vba and ace.oledb.12.0?

Edit: I found something I hadn't noticed earlier - L1 and L2 are long integer types, D1 is datetime, userid and fmn are strings. After executing these, the rows inserted into the tmpTable are in completely different orders with respect to datetime D1. If I sort the table by D1 and compare the results from the two, all of the data is the same except in 11 of 100 rows, where L1 and L2 are different. After looking closer, the data is the same except that for these 11 rows, the datetimes are the same so they don't sort correctly.

The question is: Why are the queries inserting the rows in different orders with respect to D1 between executing the same query in Access/vba versus c#/.net/ace.oledb.12.0?

Just: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=pathtodb.mdb. Could that have something to do with it? — Alex
shouldn't there be an extra space in the C# query right before the WHERE clause? — user2140173
Yes, typo while pasting it into here. The actual c# query has this. — Alex
In this fragment ... 'rampup' AS Expr2 ... Expr2 is an alias for the expression 'rampup'. Is that what you meant by "named fields"? If so, aliasing is implemented by the db engine and an alias can work regardless of whether the query is executed from c# or Access VBA. — HansUp

D Stanley D Stanley · Accepted Answer · 2015-01-14T19:42:56

Any reasons why the rows would be inserted in a different order and some different between vba and ace.oledb.12.0?

The order of data returned by an OLEDB query is generally not guaranteed unless you specify it with an ORDER BY clause. Since Access queries itself directly, it likely does preserve physical record order. Since you can't specify an order when executing an INSERT INTO ... SELECT there's not a way to force the order of inserts.

As for the different results, I would look at one particular result and dig deeper, run a non-grouping query in each system and see if you get the same records back or if there is a difference in translation.

As a side note, grouping by constant values ('" & stringUser & "', 'rampup') is unneccessary, but it shouldn't change your results.

Queries in c# and access vba producing different results

1 Answers