Set a tableDef Property

[DC CS]

Hi all,


Using Access 365 and trying to set a table property.
I have created a table with "CREATE TABLE..." sql statement. Now I need to set the "Order By" property of the newly created table to the sort order. I am using the below code:

Dim table As DAO.TableDef
Set table = Dbs.TableDefs("Tbl_Conversion")
table.Properties("orderby") = "Empname, FCDNumber"

When this code runs, I get an error message "Property not Found" or "Object doesn`t support this property or method|".

Any suggestions?

Thanks
Deon

[Minty]

Tables don't have a sort order, they are unsorted buckets that randomly hold data.
Queries can have a sort order and if you need to see data in a specific order you should be using a query.

Access will, by default, sort a table on the Primary Key if it has one, but don't rely on this as it's not guaranteed.

[CJ_London]

Some properties are created automatically when an object such as a table is created. Others don't exist until they are used. which explains the 'property not found' message. So you need to create them first. As to whether 'orderby' is a valid property name, I wouldn't like to say. check the properties of an existing table to see what it is called.

And it may be that even if you create the property, it may be it cannot be modified with code.

Either way, it won't be applied to a viewed table unless you click the filter button at the bottom of the datasheet, or also set the order by on load property to yes.

Finally, opening a query/form/report based on the table will not apply the filter since the filter only applies when opening the table directly. You either need to set the sql to order as required, or set the orderby and order by on load properties of the query/form/report

In summary, as a means of managing order, it has very limited use

[DC CS]

Thanks all. What the intent is that I need to export the data to Excel in a specific sort order, but instead I opened the Excel file after the export action and sorted the data with code.

Thanks
Deon

[Minty]

You can achieve the same, probably with less steps, just use a sorted query as the source for the Excel export.

[Micron]

You can achieve the same, probably with less steps, just use a sorted query as the source for the Excel export.

I agree, and would suggest that tables don't have a sort order - fields have the sort order?