Hello

I'm hoping for some advice about whether I should be using a table or query to hold the underlying data for a form in my database. Any thoughts much appreciated.

My database is split between a BE and FE.

There is a form in the FE which has a table in the BE as its recordsource. When the form is opened the recordsource criteria is amended based on the default settings on some combo boxes on the form. This is done using VBA code when the form is opened. When the combo boxes are changed on the the recordsource criteria updates and the form updates.

This all works but it very slow. I'm wondering whether I should be using a query as the recordsource for the form instead of a table. (Am I right in thinking that even though I'm changing the recordsource criteria, I'm still ultimately looking at the table?)

I have tried switching to a query that it based on the table (i.e. the query is based on the table, and the form looks at the query) but haven't noticed any improvement in speed, (though I'm not sure if I've done something wrong).

I've seen quite a few websites with advice about speeding up databases, and will be going through and making changes to mine based on these recommendations. But just want to make sure that I'm using the right overall approach with tables and querys first.

Any suggestions welcome.

Thanks
Speedball

Hello

I'm hoping for some advice about whether I should be using a table or query to hold the underlying data for a form in my database. Any thoughts much appreciated.
A table holds data. A query DISPLAYS data from a table or from formulas. A query does not store data.


I'm wondering whether I should be using a query as the recordsource for the form instead of a table. (Am I right in thinking that even though I'm changing the recordsource criteria, I'm still ultimately looking at the table?)

Normally I suggest using a query (not necessarily a saved query but one defined in the record source area where the table name currently resides). Click on the ellipsis (...) which appears when you click into the form's recordsource property.


[quote]I have tried switching to a query that it based on the table (i.e. the query is based on the table, and the form looks at the query) but haven't noticed any improvement in speed, (though I'm not sure if I've done something wrong).
It isn't likely going to help unless you limit the number of records that the query is pulling. Just an FYI for you - when you look at a table (open it up) you are not seeing the actual table. You are actually opening a query (albeit a system one) which returns the data from the actual table. You never see the actual table - ever.

Thank you for your reply Bob

I thought that my original structure was looking directly at the table, but it seems that I may have already been doing what you're suggesting, though without realising it.

In my VBA code for the when the form opens or is updated the following code is included:

Me.RecordSource = _
"SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE " & ClientNameSelected & " " & _
"AND " & DirectorsNameSelected & " " & _
"AND " & ClientStatusSelected & " " & _
"ORDER BY " & Ordering & " "

Which presumably is what it means to define the query in the recordsource?

Doesn't a query by its nature limit the number of records that are being pulled? Or will I only see a speed improvement if I first copy only some of the records from my main table to a smaller table, and then base the query on that smaller table?

Also, is there any speed advantage or disadvantage of using a saved query over a query stored in the record source?

Is a saved query just one that is created directly in Access in the same way as you created tables?


Thanks

I thought that my original structure was looking directly at the table, but it seems that I may have already been doing what you're suggesting, though without realising it.
Well, not quite but somewhat. You are setting the form's recordsource in code, not in the recordsource property where I was talking about.


In my VBA code for the when the form opens or is updated the following code is included:

Me.RecordSource = _
"SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE " & ClientNameSelected & " " & _
"AND " & DirectorsNameSelected & " " & _
"AND " & ClientStatusSelected & " " & _
"ORDER BY " & Ordering & " "

Just curious but how are those variables built? Can you post the entire code for the open event?


Doesn't a query by its nature limit the number of records that are being pulled?
Only if it has a Where clause with criteria.

Or will I only see a speed improvement if I first copy only some of the records from my main table to a smaller table, and then base the query on that smaller table?

I wouldn't go there unless it is absolutely necessary.

Also, is there any speed advantage or disadvantage of using a saved query over a query stored in the record source?
Given the speed of today's computers, not normally unless you have a very large set of data in your table(s) that the query is using. And I'm not sure how that stacks up - it has been discussed elsewhere on the site here but I tend to forget things that get too into the inner workings of Access.


Is a saved query just one that is created directly in Access in the same way as you created tables?
Yes, that is what is meant by a saved query.

Thanks for coming back to me again, Bob.

Regarding changing the recordsource in code vs changing the recordsource property - I have combo boxes on the form, which when changed alter the results on the form by changing the recordsource (as below). Is there a way to do this directly in the recordsource property, and would this be faster than via code?

Here is the code for the open event


Private Sub Form_Open(Cancel As Integer)

Dim TimeCheck As Double
TimeCheck = TimeMilli

If Not UserGroupChecker("Level 2") = "Yes" Then
RespAssCombo.Value = ShortUserName(CurrentUser)
StatsResponsibilityCombo = ShortUserName(CurrentUser)
Else
End If

Call FilterOptions

TimeCheck = TimeMilli - TimeCheck
Debug.Print "B2 Old", Format(TimeCheck, "#,##0.000")
TimeCheck = TimeMilli

End Sub


And here is most of the code for the FilterOptions function (the full code exceeds the character limit of a post)


Function FilterOptions() As String
Dim TimeCheck As Double
TimeCheck = TimeMilli

' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C1", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli

Dim ClientNameSelected As String
Dim DirectorsNameSelected As String
Dim TaskCategorySelected As String
Dim TaskDateSelected As String
Dim RespAssSelected As String
Dim ResponsibilitySelected As String
Dim AssignedToSelected As String
Dim EarliestStartSelected As String
Dim ClientDeadlineSelected As String
Dim ExternalDeadlineSelected As String
Dim StatusSelected As String
Dim ProgressSelected As String
Dim WaitingForSelected As String
Dim BillableSelected As String
Dim InvoicedSelected As String
Dim ClientStatusSelected As String
Dim Ordering As String
Dim ClientDateOrdering As String
Dim ExternalDateOrdering As String
Dim ProgressOrder As String
Dim DateOverdue As Date
Dim Date1Week As Date
Dim Date2Weeks As Date
Dim Date3Weeks As Date
Dim Date4Weeks As Date
Dim Date5Weeks As Date
Dim Date6Weeks As Date
Dim Date7Weeks As Date
Dim Date8Weeks As Date
Dim Date9Weeks As Date
Dim Date10Weeks As Date
Dim Date11Weeks As Date
Dim Date12Weeks As Date

DateOverdue = Format(Date, "MM/DD/YYYY")
Date1Week = Format(DateAdd("d", 7, Date), "MM/DD/YYYY")
Date2Weeks = Format(DateAdd("d", 14, Date), "MM/DD/YYYY")
Date3Weeks = Format(DateAdd("d", 21, Date), "MM/DD/YYYY")
Date4Weeks = Format(DateAdd("d", 28, Date), "MM/DD/YYYY")
Date5Weeks = Format(DateAdd("d", 35, Date), "MM/DD/YYYY")
Date6Weeks = Format(DateAdd("d", 42, Date), "MM/DD/YYYY")
Date7Weeks = Format(DateAdd("d", 49, Date), "MM/DD/YYYY")
Date8Weeks = Format(DateAdd("d", 56, Date), "MM/DD/YYYY")
Date9Weeks = Format(DateAdd("d", 63, Date), "MM/DD/YYYY")
Date10Weeks = Format(DateAdd("d", 70, Date), "MM/DD/YYYY")
Date11Weeks = Format(DateAdd("d", 77, Date), "MM/DD/YYYY")
Date12Weeks = Format(DateAdd("d", 84, Date), "MM/DD/YYYY")
Dim g, h, i, j, k, l, m, n, o As String

' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C2", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli

' Set the SQL string for ClientName
If (IsNull(ClientNameCombo.Value) Or ClientNameCombo.Value = "All") Then
ClientNameSelected = "1 = 1"
Else
ClientNameSelected = "BusinessName = '" & ClientNameCombo.Value & "'"
End If
' Set the SQL string for DirectorsName
If (IsNull(DirectorsNameCombo.Value) Or DirectorsNameCombo.Value = "All") Then
DirectorsNameSelected = "1 = 1"
Else
DirectorsNameSelected = "ClientName = '" & DirectorsNameCombo.Value & "'"
End If
' Set the SQL string for TaskCategory
If (IsNull(TaskCategoryCombo.Value) Or TaskCategoryCombo.Value = "All") Then
TaskCategorySelected = "1 = 1"
Else
TaskCategorySelected = "TaskCategory = '" & TaskCategoryCombo.Value & "'"
End If
' Set the SQL string for TaskDate
If (IsNull(TaskDateCombo.Value) Or TaskDateCombo.Value = "All" Or TaskDateCombo.Value = "Select date") Then
TaskDateSelected = "1 = 1"
Else
TaskDateSelected = "TaskDate = #" & Format(TaskDateCombo.Value, "DD-MMM-YY") & "#"
End If
' Set the SQL string for ResponsibilityAndAssignedTo
If (IsNull(RespAssCombo.Value) Or RespAssCombo.Value = "All") Then
RespAssSelected = "1 = 1"
Else
RespAssSelected = "(Responsibility = '" & RespAssCombo.Value & "' Or " & _
"AssignedTo = '" & RespAssCombo.Value & "' Or " & _
"WaitingFor = '" & RespAssCombo.Value & "')"
End If
' Set the SQL string for Responsibility
If (IsNull(ResponsibilityCombo.Value) Or ResponsibilityCombo.Value = "All") Then
ResponsibilitySelected = "1 = 1"
Else
ResponsibilitySelected = "Responsibility = '" & ResponsibilityCombo.Value & "'"
End If
' Set the SQL string for AssignedTo
If (IsNull(AssignedToCombo.Value) Or AssignedToCombo.Value = "All") Then
AssignedToSelected = "1 = 1"
Else
AssignedToSelected = "AssignedTo = '" & AssignedToCombo.Value & "'"
End If
' Set the SQL string for EarliestStart
If (IsNull(EarliestStartCombo.Value) Or EarliestStartCombo.Value = "All") Then
EarliestStartSelected = "1 = 1"
Else
If EarliestStartCombo.Value = "Ready" Then
EarliestStartSelected = "EarliestStartDate <= #" & Format(Date, "MM-DD-YY") & "#"
Else
End If
End If
' Set the SQL string for ClientDeadline
If (IsNull(ClientDeadlineCombo.Value) Or ClientDeadlineCombo.Value = "All") Then
ClientDeadlineSelected = "1 = 1"
Else
If ClientDeadlineCombo.Value = "Overdue" Then
ClientDeadlineSelected = "ClientDueDate <= #" & DateOverdue & "#"
Else
If ClientDeadlineCombo.Value = "1 week" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date1Week & "#"
Else
If ClientDeadlineCombo.Value = "2 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date2Weeks & "#"
Else
If ClientDeadlineCombo.Value = "4 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date4Weeks & "#"
Else
If ClientDeadlineCombo.Value = "8 weeks" Then
ClientDeadlineSelected = "ClientDueDate <= #" & Date8Weeks & "#"
Else
End If
End If
End If
End If
End If
End If
' Set the SQL string for ExternalDeadline
If (IsNull(ExternalDeadlineCombo.Value) Or ExternalDeadlineCombo.Value = "All") Then
ExternalDeadlineSelected = "1 = 1"
Else
If ExternalDeadlineCombo.Value = "Overdue" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & DateOverdue & "#"
Else
If ExternalDeadlineCombo.Value = "1 week" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date1Week & "#"
Else
If ExternalDeadlineCombo.Value = "2 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date2Weeks & "#"
Else
If ExternalDeadlineCombo.Value = "4 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date4Weeks & "#"
Else
If ExternalDeadlineCombo.Value = "8 weeks" Then
ExternalDeadlineSelected = "ExternalDueDate <= #" & Date8Weeks & "#"
Else
End If
End If
End If
End If
End If
End If
' Set the SQL string for Status
If (IsNull(StatusCombo.Value) Or StatusCombo.Value = "All") Then
StatusSelected = "1 = 1"
Else
StatusSelected = "Status = '" & StatusCombo.Value & "'"
End If
' Set the SQL string for Progress
If (IsNull(ProgressCombo.Value) Or ProgressCombo.Value = "All") Then
ProgressSelected = "1 = 1"
Else
ProgressSelected = "Progress = '" & ProgressCombo.Value & "'"
End If
' Set the SQL string for WaitingFor
If (IsNull(WaitingForCombo.Value) Or WaitingForCombo.Value = "All") Then
WaitingForSelected = "1 = 1"
Else
WaitingForSelected = "WaitingFor = '" & WaitingForCombo.Value & "'"
End If
' Set the SQL string for Billable
If (IsNull(BillableCombo.Value) Or BillableCombo.Value = "All") Then
BillableSelected = "1 = 1"
Else
If BillableCombo.Value = "Yes" Then
BillableSelected = "Billable = 'Yes'"
Else
BillableSelected = "Billable = 'No'"
End If
End If
' Set the SQL string for Invoiced
If (IsNull(InvoicedCombo.Value) Or InvoicedCombo.Value = "All") Then
InvoicedSelected = "1 = 1"
Else
InvoicedSelected = "Invoiced = '" & InvoicedCombo.Value & "'"
End If
' Set the SQL string for ClientStatusSelected
ClientStatusSelected = "NOT ClientStatus = 'Old client'"
' Set the ordering
ClientDateOrdering = "IIf(IsNull(ExternalDueDate),ClientDueDate,External DueDate)"
ExternalDateOrdering = "IIf(IsNull(ClientDueDate),ExternalDueDate,ClientDu eDate)"
' Ordering = "IIf(" & ExternalOrdering & "<" & ClientOrdering & "," & ExternalOrdering & "," & ClientOrdering & ")"
' TimeCheck = TimeMilli - TimeCheck
' Debug.Print "C3", Format(TimeCheck, "#,##0.000")
' TimeCheck = TimeMilli

Select Case Me.SortOptionGroup.Value
Case 1: Ordering = "ClientName ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC"
Case 2: Ordering = "ClientName ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC"
Case 3: Ordering = "ClientName ASC, " & ExternalDateOrdering & " ASC"
Case 23: Ordering = "ClientName ASC, Progress ASC, " & ExternalDateOrdering & " ASC"
Case 24: Ordering = "ClientName ASC, Progress DESC, " & ExternalDateOrdering & " ASC"
Case 4: Ordering = "TaskCategory ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 5: Ordering = "TaskCategory ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 6: Ordering = "TaskCategory ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 25: Ordering = "TaskCategory ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 26: Ordering = "TaskCategory ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 7: Ordering = "TaskDate ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 8: Ordering = "TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 27: Ordering = "TaskDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 28: Ordering = "TaskDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 9: Ordering = "Responsibility ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 10: Ordering = "Responsibility ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 11: Ordering = "Responsibility ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 29: Ordering = "Responsibility ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 30: Ordering = "Responsibility ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 12: Ordering = "EarliestStartDate ASC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 13: Ordering = "EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 31: Ordering = "EarliestStartDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 32: Ordering = "EarliestStartDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 14: Ordering = "MGDueDate ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 15: Ordering = "MGDueDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 33: Ordering = "MGDueDate ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 34: Ordering = "MGDueDate ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 16: Ordering = ClientDateOrdering & " ASC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 17: Ordering = ClientDateOrdering & " ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 35: Ordering = ClientDateOrdering & " ASC, Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 36: Ordering = ClientDateOrdering & " ASC, Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 18: Ordering = ExternalDateOrdering & " ASC, TaskDate ASC, ClientName ASC"
Case 19: Ordering = ExternalDateOrdering & " ASC, EarliestStartDate ASC, ClientName ASC"
Case 37: Ordering = ExternalDateOrdering & " ASC, Progress ASC, ClientName ASC"
Case 38: Ordering = ExternalDateOrdering & " ASC, Progress DESC, ClientName ASC"
Case 20: Ordering = "Progress DESC, TaskDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 21: Ordering = "Progress DESC, EarliestStartDate ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 22: Ordering = "Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 39: Ordering = "Progress ASC, " & ExternalDateOrdering & " ASC, ClientName ASC"
Case 40: Ordering = "Progress DESC, " & ExternalDateOrdering & " ASC, ClientName ASC"
End Select

Me.RecordSource = _
"SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE " & ClientNameSelected & " " & _
"AND " & DirectorsNameSelected & " " & _
"AND " & TaskCategorySelected & " " & _
"AND " & TaskDateSelected & " " & _
"AND " & RespAssSelected & " " & _
"AND " & ResponsibilitySelected & " " & _
"AND " & AssignedToSelected & " " & _
"AND " & WaitingForSelected & " " & _
"AND " & EarliestStartSelected & " " & _
"AND " & ClientDeadlineSelected & " " & _
"AND " & ExternalDeadlineSelected & " " & _
"AND " & StatusSelected & " " & _
"AND " & ProgressSelected & " " & _
"AND " & BillableSelected & " " & _
"AND " & InvoicedSelected & " " & _
"AND " & ClientStatusSelected & " " & _
"ORDER BY " & Ordering & " "
FilterOptions = Me.RecordSource

DoCmd.Requery
Me.Refresh
End Function




Any comments / improvements / critisms will be more than welcomed!

(If it helps to understand the code, my database is used to track the progress of different tasks for a number of different clients.)

Hello again Bob

Sorry to post again before allowing you to respond to my post below, but I've had another thought.

I've got to thinking about switching to using a saved query as the recordsource for my form, and I think that it will have some advantages.

Currently I'm calculating some information directly on the form as it isn't in the underlying table. So having a saved query where I can calculate some information to show on the form would speed this up, I think?

The following code seems to work as a means of changing the SQL of the query in the same way as I was previously changing the recordsource code:


Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("WorkOutstandingQuery")
strSQL = _
"SELECT * " & _
"FROM WorkOutstanding " & _
"WHERE etc;"
qdf.SQL = strSQL


Can you see any problems with this approach?

Thanks again
Speedball