Table or Query as the recordsource for a form (1 Viewer)

speedball

Registered User.
Local time
Today, 14:15
Joined
Oct 21, 2009
Messages
44
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
 

boblarson

Smeghead
Local time
Today, 07:15
Joined
Jan 12, 2001
Messages
32,059
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.


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.
 

speedball

Registered User.
Local time
Today, 14:15
Joined
Oct 21, 2009
Messages
44
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
 
Last edited:

boblarson

Smeghead
Local time
Today, 07:15
Joined
Jan 12, 2001
Messages
32,059
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.
 

speedball

Registered User.
Local time
Today, 14:15
Joined
Oct 21, 2009
Messages
44
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

Code:
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)

Code:
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,ExternalDueDate)"
    ExternalDateOrdering = "IIf(IsNull(ClientDueDate),ExternalDueDate,ClientDueDate)"
'    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.)
 

speedball

Registered User.
Local time
Today, 14:15
Joined
Oct 21, 2009
Messages
44
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:

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
 

Users who are viewing this thread

Top Bottom