set orderby property for a table

[DataMiner]

How do I set the orderby property for a table using visual basic? Access help seems to indicate that it's possible, but offers no examples. Orderby doesn't seem to be available as a property of anything other than forms and reports.

Using access 2002, BTW

 

[ghudson]

I doubt you can do it for a table. Indexing sort of does it but I would not guarantee the results. Users should not see the tables so use a query or SQL to sort your record source.

 

[DataMiner]

Oh, I finally figured it out. It's just

MyTableDef.properties("orderby")="myfield desc"

Works fine.

 

[Rich]

Why would you want to though?

 

[modest]

This thread was dead, but I'm glad it's here because I needed it.

Though you're right, users should use a query...but if you did a select * on a table and opened it up as editable... it'd be the same thing as viewing the table. You can still change the data and what not, just not as much control over field orientation.

I specifically need this because the users that use my table are responsible enough that they know not to touch anything. And if they do, it's not hard to get it back.

Thanks for the thread though
-modest

 

[modest]

This was working... and now the property cannot be found ?

Any ideas?

Dim table As DAO.TableDef
Set table = db.TableDefs("REPORT")
table.Properties("orderby") = "Terminal"

Terminal is a valid field in my REPORT table. Not only was this working correctly before, but I have tried "order by" and I dont think I need to specify it as ascending because ascending is the default (but I dont know the code to do it anyhow.. possibly asc).

But because the problem is that the "orderby" property can't be found... there could be a reference I had that I'm not referencing anymore. If anyone knows, please inform me =)

Thanks,
Modest

 

[modest]

*Update*

If I do:
table("REPORT").Properties("orderby") = "Terminal"
I get "Item not found in this collection." error




ARGH

I've now tried
table.Properties(OrderBy) = "[Terminal]"
and get "Item not found in this collection" .. which is a step up from property not found... removing the quotes got rid of that problem. But Item not found is not a good thing... by the way I tried "Terminal" "[Terminal]" and [Terminal]


After doing more debugging... I'm sure the probleme exists in the .Properties("orderby")
I know I need quotes because without the quotes, OrderBy returns "" and sets the property to null... which wouldn't give an error. My question is where can the "orderby" property be located...what reference must I include?

 

[RoyVidar]

Since the property isn't set, you'll need to create and append it (see createproperty in the help files) - you are getting the 3270, right?

dim prp as dao.property
set prp = currentdb.tabledefs("yourtable").createproperty("orderby",dbtext,"Terminal")
currentdb.tabledefs("yourtable").properties.append prp
set prp=nothing

But I don't know why you're doing this, one is encouraged to use queries if sort order is important...

 

[modest]

The comment below was a re-post of this. There seems to be another db error =)

 

[modest]

I don't think an error number was listed.

I know I "should" be doing this with queries, but as stated before, this is how I feel more comfortable doing it (It's conditional and less messy).. and I'm not worried about table-security or data discrepancies.

What I'm more concerned with is the fact that this would work one minute and then not the next. I mean how does a property just "up and leave"? I did make one change to that table by adding two fields.... but I dont see why this would be the effect.


-I will try and create it again, though
thank you for your reply

 

[RoyVidar]

So - did I guess right or wrong on the error

Since I don't use DAO, I'm not entirely sure, but my guess is that you have probably saved the table without anything in the orderby property, and need to "recreate". Just test for 3270, else create.

 

[modest]

I'm still not sure yet... I'm fiddling with the same database... the only difference is I didn't add those two fields to the table and everything is working fine.

I'm going to try and recreate the field and then re-examine my 30 queries and see where there might be a fault. If that doesn't work, I'll try recreating the property as you suggested.

Thanx,
Modest

 

[modest]

Went through and checked all my queries... I basically rebuilt all the tables and went through the query line by line (SQL View and Query Viewer).

The addition of the two fields that I had created would have no affect on this... I've Rebuilt and Repaired the database, I even used the code you had suggested... but alas, I'm still getting an error.

There is no error number, I just get the evil "Item not found in this collection." Which may mean my table (which does exist and is populated) or my field name... (which does exist and is somewhat populated - there are some nulls).

So again. I am back to the beginning and don't feel like retyping everything AGAIN.

EDIT :: DISREGARD THIS COMMENT!!!

Roy, you're amazing.. the only reason it wasn't working was because I copied your code into my function so that I could just manipulate it to fit my needs (I have different field names/table names then what i posted).

Well i didn't delete one of the lines... so it wasn't finding that item It was undercover and just blended in with the rest of my code... well IT WORKS NOW!!!






--- i still dont understand why this property would be deleted when i never created it in the first place, but many thanks.

now I have to put in error checking to see if that property already exists. is .properties() an array? like if i put in .properties(0) would that return a name?

 

[RoyVidar]

Thank you!

I don't know why it's deleted, perhaps my above guess?

To find out if a given property exists within a collection there's at least two ways, the "polite" or brute force.

brute force would be the above approach (or something like the below)

on error resume next
table("REPORT").Properties("orderby") = "Terminal"
if err.number <> 0 then
err.clear
' create and populate the property

polite could be

set td = db.tabledefs("yourtable")
for each prp in td.properties
' to see which properties exists, you could to a debug.print prp.name
if prp.name = "<your property>" then
blnFound= true
exit for
end if
next prp
if blnFound then
' populate property
else
' create and populate property
end if

There's probably also an index, but I'm not sure I'd rely on it.

In either case, I think the end result would be the same, if the property exists, you can set it, if not you'll need to create it. Which method to use - preferences. I use both methods depending on the challenge at hand. For instance in stead of looping a large collection, I'd rather do brute force.

 

[modest]

yeah the onerror approach is always there =) ... i just like to minimize errors before they occur. I think I'm going to start making an error log too. Just to see what kind of problems users are encountering