How to Read a Table's OrderBy Property

[dftjsn]

I'm trying to open an ADO recordset of an Access table that is sorted/ordered by the Table's OrderBy property but I can't seem to find how to read a Table's OrderBy Property in ADO or ADOX. I can do it with DAO:

dim db as dao.database
dim tdf as dao.tabledef

set db=Currentdb()
set tdf=db("TheTable&quot
Debug.Print tdf.Properties("ORDERBY&quot

but it takes about 6 seconds to return the answer.

Does anyone know how to read a Table's OrderBy property more quickly.

Thanks!

dftjsn

 

[SQLBI]

dftjsn,

I'm struggling to imagine a reason for doing this. If your database is structured according to Normalisation rules, the order of the records within it are of absolutely no significance.

Any ordering of data should be dealt with within your querie's 'ORDER BY' clause and not your tables.



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[dftjsn]

Hi Leigh,

Thanks for your response. I realize that the default order of records in an Access table is unpredictable and the best way to deal with that is to use the OrderBy property of a query or form when you wish to display or query them. I don't want to do either - I just want to process the table using ADO in the order governed by the Tables OrderBy property. I have a lot of old tables that users will be adding some metadata rows to and then ordering the table so the metadata rows appear at the top of the table. I don't know in advance which field/column they ordered by to get those rows to appear at the top - hence the need to read the OrderBy property of the table. I could standardize and make users always order the table by a particular field/column name, but I was trying to make it more generic.

The DAO route works, it is just slow.

Thanks!

dftjsn

 

[danvlas]

No need for ADO or DAO.

MsgBox Screen.ActiveDatasheet.OrderBy

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dftjsn]

Daniel,

Thanks for the tip. Screen.ActiveDatasheet.OrderBy does allow me to access the OrderBy property provided I open the table and make it a visible ActiveDatasheet that the user can see. Since I don't really want the user to see the table, I open it, read the OrderBy and OrderOn properties, and then immediately close the table. As a result, the user doesn't see the table - just a flicker as it comes and goes. This is okay. The more difficult issue, however, is if the table I want to process is in a different access database. I don't see how to open that table programmatically and get the OrderBy property without ADO/DAO, unless I first import the table into my current Access database which I'd like to avoid.

Any suggestions?

Thanks!

dftjsn

 

[dftjsn]

As a close out ... I've gone back to using DAO to read the OrderBy and OrderByOn properties of a table. For some reason it had been taking 5 or 6 seconds for DAO to return the answers, but after compacting the database and trying again, it now returns the answers promptly.

dftjsn