OrderBy Property in table.

I have a table call "tblTab". In the table's properties, there is a propertiy call "Order By". In a module, I would like to use vb to set the sorting sequence of the fields 'Color' and 'SeqNo' in an Ascending order.

Any help is much appreciate.

perennial
LVL 1
perennialAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rockiroadsCommented:
At what point do u want the ordering?
When u define a recordset, u could perhaps use it in there

SELECT * FROM tblTab ORDER BY Color, SeqNo

Leigh PurvisDatabase DeveloperCommented:
In a module?
So you're opening the table somehow?  (Likely into a recordset).

IMHO you should never count or even use the table's ordering property.
It's just another thing to forget about and cause problems.
Use a query (or SQL Statement) when opening the table data to specify your order of records.
perennialAuthor Commented:
This is how the tblTab is created. I am not sure where to set the sorting.

Function CreatetblHCTemp(strTName As String)
On Error GoTo ErrorHandler

  Dim db As DAO.Database
  Dim td As DAO.TableDef
  Dim fld As DAO.Field
  Dim pt As DAO.Property

    Set db = CurrentDb()
    'Create table
    Set td = db.CreateTableDef(strTName)
    'Create fields
    With td
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("ItemNo", dbLong)
      .Fields.Append .CreateField("Size", dbText, 5)
      .Fields.Append .CreateField("Source", dbText, 5)
      .Fields.Append .CreateField("SowDat", dbDate)
      .Fields.Append .CreateField("IndexNo", dbLong)
      .Fields.Append .CreateField("Location", dbText, 10)
      .Fields.Append .CreateField("EventType", dbText, 5)
      .Fields.Append .CreateField("Qty ", dbLong)
      .Fields.Append .CreateField("HarvDat", dbDate)
      .Fields.Append .CreateField("Correct", dbText, 5)
      .Fields.Append .CreateField("SMSTK", dbBoolean)
      .Fields.Append .CreateField("TMSTK", dbBoolean)
    End With
    'Append table
    db.TableDefs.Append td
    ' set the format of "SMSTK" and "TMSTK" to a check box
    For Each td In CurrentDb.TableDefs
        If td.Name = strTName Then
            Set pt = td.Fields("SMSTK").CreateProperty("DisplayControl", dbInteger, 106)
            td.Fields("SMSTK").Properties.Append pt
            Set pt = td.Fields("TMSTK").CreateProperty("DisplayControl", dbInteger, 106)
            td.Fields("TMSTK").Properties.Append pt
            Exit For
        End If
    Next td
   
 Set fld = Nothing
 Set td = Nothing
 Set db = Nothing
 Set pt = Nothing

Exit Function

ErrorHandler:
    MsgBox "Error number " & Err.Number & " occurred in DebugOnError()." _
        & vbCrLf & vbCrLf & Err.Description, vbExclamation
    Err.Clear

End Function
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

perennialAuthor Commented:
LPurvis,

Your are correct, after appending the recordset.

I need to sort the data in the table because the table is going to be link to a printing software which it doesn't have that capability. The printer software can not link to a query, it must be link to a table (not 100% sure will check). It would be much easier if I can organize the date before printing it.

perennial
Leigh PurvisDatabase DeveloperCommented:
Well - my point is that you shouldn't be trying to.
The indexes you place on the table will take precidence for sorting.  
Tables are for storage of data only - not viewing.
If you want to have predictable ordering or contents then you need to query the data.
rockiroadsCommented:
I knew it had to be more complicated than my first feeble effort!

You could try this in your creation


            Set pt = td.Fields("Size").CreateProperty("OrderBy", dbText, 106)
            pt.Value = "Color"
            td.Properties.Append pt


Not sure if u can also do

            pt.Value = "Color, Seqno"

jefftwilleyCommented:
Seems to me as if you're just creating the table here. The sorting would take place when you populate the table with data wouldn't it? How are you accomplishing that?
J
Leigh PurvisDatabase DeveloperCommented:
I see your predicament.

Does the printing software attempt to print out every single record in your table?
Is that not a bit much?  (Or are we only talking a reasonably low number of records?)

I still don't believe the OrderBy property of a table is the way to go.
Would the external table even access the data in such a way that this is even honoured?
I'd expect any indexes to win every time.  (And would in fact be dissappointed if they didn't).

Since you're generating this table (presumably for the express purpose of your external data access) then could you not create a unique ordered index on the fields you're interested in?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
perennialAuthor Commented:
rockiroads,

Let me give it a shot.


LPurvis,

Yes, the software will print every record in the table. Each print job will be less then one thousand pages. The table will appear in the printing software exactly as I set it. I will also try to create the unique order index and see how it come out.

perennial
perennialAuthor Commented:
LPurvis,

Thank you very much for the advice...the indexing is working great.

perennial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.