Table Properties – “Order By” Field 1

[Goondu]

Hi,

I have search this site but nothing comes close to what I needed.
Below is the closest code I found.

Private Sub setTableDescription(strSourceFile As String, strTableName As String, strDescription As String)
Dim ws As New DAO.DBEngine
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim prop As DAO.Property

Set db = ws.OpenDatabase(strSourceFile)
Set tbl = db.TableDefs(strTableName)
Set prop = tbl.Properties("Description")
prop.Value = strDescription

Set ws = Nothing
End Sub

But the code cannot find the “Order By” of the table properties.

To get I mean, open a table in design mode, right click on top of the design header, click the Properties. Under the “General” Tab, there is an “Order By” field.

I need to use the “Order By” to sort by Ascending automatically to a column.

I had tried ADO, still no luck.

Does anyone help?

 

[fredericofonseca]

with ado.

Private Sub Form_Load()
Dim dbconn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim nrows As Long
Set dbconn = New ADODB.Connection
dbconn.CursorLocation = adUseClient
dbconn.Mode = adModeReadWrite
dbconnstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\vbcode\hibernian\hibernian_paxus.mdb;User Id=admin;Password=;"
dbconn.Open dbconnstring
' Here we sort the table using plain SQL order by
ssql = "select REF_REFERED_BY, REF_ENTITY_ID,"
ssql = ssql + " REF_ENTITY_TYPE, REF_ENTITY_GROUP "
ssql = ssql + " from Programrelation order by REF_REFERED_BY, REF_ENTITY_ID;"
Set rs = New ADODB.Recordset
rs.Open ssql, dbconn
While Not rs.BOF And Not rs.EOF
Debug.Print rs.Fields(0).Value
Wend
' Now we sort the existing recordset data using a different order field.
rs.Sort = "REF_ENTITY_ID"
While Not rs.BOF And Not rs.EOF
Debug.Print rs.Fields(0).Value
Wend
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Goondu]

Thanks for replying Frederico.

It appears that i need to open my recordset using SQL string order by ItemID.

rstT1.open "Table1".... to rstT1.open SQLT1
rstT2.open "Table2".... to rstT2.open SQLT2

However, that not what i needed. I'm refering to the Table Properties under the General Tab - Order By. I need to code it by Ascending because i have a code that create this table
and than by another open recordset which will update other columns. The problem is the ItemID is not in order. I do not want to re-code to do a loop. It takes too long to recycle all records.

There is one possible way by using ADOX.Index. But i'm not sure how.

Anyone got a solution? Thanks

 

[fredericofonseca]

The menu option you are speaking of only issues a "sort" to the current recordset, identical to the one I mentioned.


What are you trying to do exactly? its less than clear, and without more details we can not provide you with a correct answer.

If you are working with a recordset (ADO) and you wish to use a new ordering without reissuing the SQL again, then the second method I mentioned is the way to do it.

If you are inserting records into a table, then the order of the records ON the table are of no concern to you, as the order of retrieval of records is based on the "ORDER BY" clause on the SQL. If not is used then the order of retrieval is random.


If you wish to retrieve the INDEXES information, e.g. columns, order type (asc or desc), then thats another issue, and is not what you asked in the first place.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Goondu]

>The menu option you are speaking of only issues a "sort" >to the current recordset, identical to the one I >mentioned.
>What are you trying to do exactly? its less than clear, >and without more details we can not provide you with a >correct answer.

Hi Frederico,

I might have confused you about the properties. There are two properties from the table.

1. Where you can see the Description, Created date, Modified Date, Owner,…so on

2. Where you are at design mode of the table, right click at the header – select Properties – Header Name – Table Properties – General Tab is where the Order By where I am trying to insert a text by VB. The properties are Subdatasheet Name, Link Child Fields, Link Master Fields, Subdatasheet Height, Subdatasheet Expanded…..

>If you are working with a recordset (ADO) and you wish to >use a new ordering without reissuing the SQL again, then >the second method I mentioned is the way to do it.

Yes.
Error at rs.Sort – Current provider does not support the necessary interfaces for sorting or filtering.

Here is the code I have modified; I am using A2K, WinXP. Access MDB file.

Dim dbconn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim dbconnstring As String
Dim ssql As String

Set dbconn = CurrentProject.Connection
'Here we sort the table using plain SQL order by
ssql = "SELECT ItemID FROM Table1 ORDER BY ItemID"
Debug.Print ssql
Set rs = New ADODB.Recordset
rs.Open ssql, dbconn, adOpenKeyset, adLockOptimistic
While Not rs.BOF And Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Wend
rs.MoveFirst
' Now we sort the existing recordset data using a different order field.
rs.Sort = "ItemID"
While Not rs.BOF And Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Wend

>If you are inserting records into a table, then the order >of the records ON the table are of no concern to you, as >the order of retrieval of records is based on the "ORDER >BY" clause on the SQL. If not is used then the order of >retrieval is random.

Yes, if I use SQL clause.

>If you wish to retrieve the INDEXES information, e.g. >columns, order type (asc or desc), then thats another >issue, and is not what you asked in the first place.

Still looking for other options, but indexing seems to work for now.

Thanks.

 

[fredericofonseca]

tbl.Properties("OrderBy") = "ItemID asc"
tbl.Properties("OrderByOn") = True
By default OrderBy is not set, and does not show if you print all properties

For i = 0 To tbl.Properties.Count - 1
Debug.Print tbl.Properties(i).Name
Next i
Set prop =

Note that setting this property will cause a report events of close and open to be executed.


Surprised with the error you are getting on the rs.sort. Using AC2003 works fine. Will try later on with AC2000

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[cmmrfrds]

Try setting these settings to use the sort.


Set rs = New ADODB.Recordset
rs.CursorLocation = asUseClient
rs.Open ssql, dbconn, adOpenStatic, adLockOptimistic

 

[Goondu]

Thanks Frederico and cmmrfrds,

AdUseClient works.

When the table is created, the “OrderBy”is empty and the property does not appear. The code will not work and error at - tbl.Properties("OrderBy") = "ItemID asc".

Seems I got to use Indexing. It appears that ADO or DAO cannot change the property values.

Thank you for helping.

 

[TheAceMan1]

How are ya Goondu . . .

If the property doesn't exist all you have to do is create it! See the [blue]CreateProperty[/blue] method for a [blue]TableDef object! . . .

See Ya! . . . . . .

 

[Goondu]

You're the Aceman,

That did the trick.

Thanks

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim fld As DAO.Field, prp As DAO.Property

On Error Resume Next
Set dbs = CurrentDb
Set tdf = dbs.TableDefs!Table1
Set fld = tdf.CreateField("ItemID", dbText)
Set prp = fld.CreateProperty("OrderBy", dbText, "ItemID")
tdf.Properties.Append prp