Removing OrderBy from Tables and Queries in VBA

[Hmadyson]

I have an issue where people are in my access database and they look at data
through a table, sort it, and when they close the table, they are asked if
they want to save their changes, they invariably say yes.

This is slowing down my database.

I want to write code that goes through all tables, queries, forms, and
reports, and turns off the orderby or filters.

forms and reports are no problem since I can open them and close them, but
querydefs and tabledefs seems to be a different beast.

I found the OrderByOn property which is dug into the object, and I have code
to change it

If tbl.Properties("OrderByOn") Then
tbl.Properties("OrderByOn") = False
tbl.Properties("OrderBy") = " "
End If

The only thing is that I am seeing the same tables popping up each time I
run the code, so it seems that these are not properties that are really
getting saved.

Does anyone have any help for me? This is the code I want to use

Sub RemoveSortsFiltersTables()
Dim tbl As TableDef
Dim doc As Document
Dim cnt As Container
Dim qry As queryDef

On Error Resume Next
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 1) <> "~" Then
If Left(tbl.Name, 4) <> "Msys" Then
If tbl.Properties("OrderByOn") Then
tbl.Properties("OrderByOn") = False
tbl.Properties("OrderBy") = " "
End If
If tbl.Properties("FilterOn") Then
tbl.Properties("FilterOn") = False
tbl.Properties("Filter") = " "
End If
End If
End If
Next
For Each qry In CurrentDb.QueryDefs
If Left(qry.Name, 1) <> "~" Then
If Left(qry.Name, 4) <> "Msys" Then
If qry.Properties("OrderByOn") Then
qry.Properties("OrderByOn") = False
qry.Properties("OrderBy") = " "
End If
If qry.Properties("FilterOn") Then
qry.Properties("FilterOn") = False
qry.Properties("Filter") = " "
End If
End If
End If
Next
For Each cnt In CurrentDb.Containers
Select Case cnt.Name
Case "Forms", "Reports"
For Each doc In cnt.Documents
RemoveOrderByObject doc
Next
End Select
Next

Set tbl = Nothing
Set doc = Nothing
Set cnt = Nothing
End Sub

Sub RemoveOrderByObject(ByRef doc As Document)
Dim frm As Form
Dim rpt As Report

Select Case doc.Container
Case "Forms"
DoCmd.OpenForm doc.Name, acDesign
Set frm = Forms(doc.Name)
If frm.OrderByOn Then
frm.OrderByOn = False
frm.OrderBy = ""
End If
If frm.FilterOn Then
frm.FilterOn = False
frm.Filter = ""
End If
DoCmd.Close acForm, doc.Name, acSaveYes
Case "Reports"
DoCmd.OpenReport doc.Name, acDesign
Set rpt = Reports(doc.Name)
If rpt.OrderByOn Then
rpt.OrderByOn = False
rpt.OrderBy = ""
End If
If rpt.FilterOn Then
rpt.FilterOn = False
rpt.Filter = ""
End If
DoCmd.Close acReport, doc.Name, acSaveYes
End Select

Set frm = Nothing
Set rpt = Nothing
End Sub

 

[Dirk Goldgar]

I have an issue where people are in my access database and they look at
data
through a table, sort it, and when they close the table, they are asked if
they want to save their changes, they invariably say yes.

This is slowing down my database.

I want to write code that goes through all tables, queries, forms, and
reports, and turns off the orderby or filters.

forms and reports are no problem since I can open them and close them, but
querydefs and tabledefs seems to be a different beast.

I found the OrderByOn property which is dug into the object, and I have
code
to change it

If tbl.Properties("OrderByOn") Then
tbl.Properties("OrderByOn") = False
tbl.Properties("OrderBy") = " "
End If

The only thing is that I am seeing the same tables popping up each time I
run the code, so it seems that these are not properties that are really
getting saved.

Does anyone have any help for me? This is the code I want to use

Sub RemoveSortsFiltersTables()
Dim tbl As TableDef
Dim doc As Document
Dim cnt As Container
Dim qry As queryDef

On Error Resume Next
For Each tbl In CurrentDb.TableDefs
If Left(tbl.Name, 1) <> "~" Then
If Left(tbl.Name, 4) <> "Msys" Then
If tbl.Properties("OrderByOn") Then
tbl.Properties("OrderByOn") = False
tbl.Properties("OrderBy") = " "
End If
If tbl.Properties("FilterOn") Then
tbl.Properties("FilterOn") = False
tbl.Properties("Filter") = " "
End If
End If
End If
Next
For Each qry In CurrentDb.QueryDefs
If Left(qry.Name, 1) <> "~" Then
If Left(qry.Name, 4) <> "Msys" Then
If qry.Properties("OrderByOn") Then
qry.Properties("OrderByOn") = False
qry.Properties("OrderBy") = " "
End If
If qry.Properties("FilterOn") Then
qry.Properties("FilterOn") = False
qry.Properties("Filter") = " "
End If
End If
End If
Next
For Each cnt In CurrentDb.Containers
Select Case cnt.Name
Case "Forms", "Reports"
For Each doc In cnt.Documents
RemoveOrderByObject doc
Next
End Select
Next

Set tbl = Nothing
Set doc = Nothing
Set cnt = Nothing
End Sub

Sub RemoveOrderByObject(ByRef doc As Document)
Dim frm As Form
Dim rpt As Report

Select Case doc.Container
Case "Forms"
DoCmd.OpenForm doc.Name, acDesign
Set frm = Forms(doc.Name)
If frm.OrderByOn Then
frm.OrderByOn = False
frm.OrderBy = ""
End If
If frm.FilterOn Then
frm.FilterOn = False
frm.Filter = ""
End If
DoCmd.Close acForm, doc.Name, acSaveYes
Case "Reports"
DoCmd.OpenReport doc.Name, acDesign
Set rpt = Reports(doc.Name)
If rpt.OrderByOn Then
rpt.OrderByOn = False
rpt.OrderBy = ""
End If
If rpt.FilterOn Then
rpt.FilterOn = False
rpt.Filter = ""
End If
DoCmd.Close acReport, doc.Name, acSaveYes
End Select

Set frm = Nothing
Set rpt = Nothing
End Sub

I've only played with this with Access 2003, and briefly, but it seems to me
that, when I remove the Order By value from a table in design view, the
property is deleted from the TableDef. So I tried this:

CurrentDb.TableDefs("TestTable").Properties.Delete "OrderBy"

and that removed the OrderBy setting.

I don't know if it's important also to remove the OrderByOn property or not,
as in:

With CurrentDb.TableDefs("TestTable").Properties
.Delete "OrderBy"
.Delete "OrderByOn"
End With

It doesn't seem to hurt, but I'll let you do the testing. Please report
back what you find.

 

[Hmadyson]

I believe that worked. Thanks!

I've only played with this with Access 2003, and briefly, but it seems to me
that, when I remove the Order By value from a table in design view, the
property is deleted from the TableDef. So I tried this:

CurrentDb.TableDefs("TestTable").Properties.Delete "OrderBy"

and that removed the OrderBy setting.

I don't know if it's important also to remove the OrderByOn property or not,
as in:

With CurrentDb.TableDefs("TestTable").Properties
.Delete "OrderBy"
.Delete "OrderByOn"
End With

It doesn't seem to hurt, but I'll let you do the testing. Please report
back what you find.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

.