Hi,
When insert data using OLEDB Jet provider, the data are inserted properly, but the order in which I have inserted is not kept as is. Instead it's automatically changing the order.
How to make the inserted records in the same order ??
+2
A:
I think that have to do with Primary Key. Let's say your PK is text based, then MS Access will sort it alphabetically. If you want to have control of the Order use an AutoNumber as PK.
gsharp
2010-03-25 08:45:11
+3
A:
You shouldn't be worrying about the order data is stored in a table. Order is a presentation layer issue and when you want a particular order, specify a sort order (ORDER BY in SQL).
If you're using table datasheets in your application and expecting a desired sort order, then you should be using a form with a sort order defined (either in the form properties or in the underlying recordsource) or a query datasheet in place of the table datasheet.
Access also allows you to set a sort order in a table datasheet and save it with the table, but this is not advisable. Table datasheets are convenient, but you should not depend on them for anything but a quick-and-dirty view of the data.
David-W-Fenton
2010-03-25 21:55:18
Well for a Access Database I agree with you, you don't have to worry about the order of the Data. But when it comes to a real DBMS the order can matter -> Clustered Index.
gsharp
2010-03-26 06:54:48
You might want to read up on Jet/ACE's PKs, which use a clustered index. The PK is the only index that can be clustered, though. Your comment about "real DMBS's" is wrong, as in relational theory, tables don't have an order. In some edge cases, you may find that knowing about the physical storage order in your data table can help you enhance performance, but it's not supposed to be that from a theoretical standpoint.
David-W-Fenton
2010-03-26 18:14:15
+1
A:
Within a database, if you need records to be returned in a certain order you need to specify an 'ORDER BY' clause in your read code:
SELECT Field1, Field2 FROM Table ORDER BY Field1
Anything else has no guarantee of working going forwards.
eftpotrm
2010-03-26 00:50:21
Thanks for the answer, but I am using OLE DB connection through C#.Net, I am exporting data (which is already in sorted order) to ms access, in which the sort order automatically setup and my initial sorted data was not there as such. However, I found a workaround, insert the records to MS Access first using some temp names, and then use select into query with order by of the fields that needs to be sorted to a desired table. This gives some relief but it's a dual work with performance hit. As of now No other go for me.
KSH
2010-03-26 20:47:16
If that's the situation you could do it as well by sticking an Autonumber column on the table then reading from a query that explicityly orders by that view, which may prove simpler.
eftpotrm
2010-03-27 20:51:11