Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Read a Table's OrderBy Property

Status
Not open for further replies.

dftjsn

Programmer
Feb 25, 2002
43
US
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")
Debug.Print tdf.Properties("ORDERBY")

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
 
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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top