Retrieve sort order from mdb or accdb

02-09-10, 02:01 PM

Marco

I use ado to open access databases (mdb and accdb)

In access I sort the database on a few columns and then save this.
When I then open it in my application using ado, the sort order dissapears.

What do I need to do, to acquire the saved sort information form the .mdb
files?

02-09-10, 02:28 PM

Ralph

"Marco" <Marco> wrote in message
news:aaad
> I use ado to open access databases (mdb and accdb)
>
> In access I sort the database on a few columns and then save this.
> When I then open it in my application using ado, the sort order
dissapears.
>
> What do I need to do, to acquire the saved sort information form the .mdb
> files?
>

That is because MSAccess doesn't actually change the underlying data in the
database. Think of MSAccess as being able to apply or attach additional
"metadata" to create "views".

When you open a Table in MSAccess and apply 'table formats' such as a
vertical sort, or column order - this information is saved separately. This
is the same as when you create specific formats for data in columns - like
date formats. This information is saved to database base in a separate
section for use by MSAccess. The underlying database however is NOT
changed.

-ralph

02-09-10, 03:34 PM

Richard Mueller [MVP]

02-09-10, 04:46 PM

Bob Barrows

Marco wrote:
> I use ado to open access databases (mdb and accdb)
>
> In access I sort the database on a few columns and then save this.
> When I then open it in my application using ado, the sort order
> dissapears.
>
> What do I need to do, to acquire the saved sort information form the
> .mdb files?

Your confusion is coming from failing to realize that Access is a
front-end tool for working with Jet databases. That Sort property is
saved in the .mdb file, but it is considered to be an "Access-specified"
property and is only accessible when using Access to view the data in
the database. When you open a table in Access, behind the scenes, Access
is constructing a query to retrieve the data from that table using the
Sort property to construct an ORDER BY clause.

With ADO (or DAO), you have to construct the ORDER BY clause yourself.

02-10-10, 09:34 AM

Marco

Thanks for the answers,

I should have explained myself better. I know about the two parts (db/view)
of mdb/accdb files.

I should have asked if it is posible with ado to retrieve the 'view'
information, specificly the sort order, saved in an mdb file, and how.

And if not what other options I have to retrieve the sort order from the
'view' saved in an mdb/accdb file.

02-10-10, 01:30 PM

Ralph

"Marco" <Marco> wrote in message
news:824f
> Thanks for the answers,
>
> I should have explained myself better. I know about the two parts
(db/view)
> of mdb/accdb files.
>
> I should have asked if it is posible with ado to retrieve the 'view'
> information, specificly the sort order, saved in an mdb file, and how.
>
> And if not what other options I have to retrieve the sort order from the
> 'view' saved in an mdb/accdb file.
>

Not with ADO.

Nothing short of Automation, or hacking your way through the proprietary
undocumented bits of "view" information.

It isn't clear why you would need to do this. But if it is merely to insure
that an outside process 'sees' the same 'view' a MSAccess project 'sees' you
might consider creating a MSAccess Query. The MSAccess Project and an
outside process (with ADO) would both use the same Query, ie, you can use a
Query object in lieu of a Table object in both places.

-ralph

02-10-10, 01:44 PM

Bob Barrows

Some Access-specified properties are accessible using ADOX. I don't remember
if Sort is one of them so you'll have to look it up in the ADO
documentation: http://msdn2.microsoft.com/en-us/library/ms675532.aspx
IIRC, one of the appendices in the ADO Programmers Guide node contains a
list of properties exposed to ADOX ... or maybe you need to look under the
OLE DB node ... I can't remember.

If not ADOX, then you might get somewhere by using DAO and checking the
exposed properties of the tabledef object. If you are writing a server-based
application, you need to be aware that DAO is very old technology and is
single-threaded, and its use will impair scalability of a web application,
for example, by making that application thread-bound.

Marco wrote:
[..]

Similar Threads
Sales order processing item inquiry windows - sort order Hi, Since I have updated to version 10 (from version 9.0) the document are not showing in any specific order. In version 9 they were sorted by document date. For example...
Possibly new sort method/idea: Skybuck's two third ping pong sort algorithm :) Order(N^2), steps approximation: (N^2) div 2 Hello, While examining 0 to range-1 multiplication with a prime number and sorting them based on the output's lower half digits to evaluate if lower half is indeed unique I...
Pls. reply Sort Data and copy to next coulmn when sort order chang Hello I am entering date wise data into worksheet for every date of data I have new worksheet now I want to sort all worksheet together as per column â€œAâ€ and then after...
Selecting a report's sort order & sort fields at runtime Hi Experts, I would like to have opinions, advices or pointers on how to achieve the subject matter. App is in A2K, the report has quite a few columns. There are several...
Moving a database from Sort order 44 to Sort order 52 Can anyone point me in the right direction on the best way to take a SQL 6.5 sort order 44 database and move it and the data to a SQL 7.0 sort order 52 database ? The 6.5 to...