VB.net to Access vs straight Access querying

Hi

I have a VB.net project where I use the following VB.net code to query an Access database.
As the tables in question get longer, the queries get a lot slower. Should I rather be doing all my queries in Access and then query the result? I tried doing make table queries in Access and then using a select statement on the VB side and this took pretty long too.

Sub GetUnPaidInvoiceAmounts()

        Try

            Dim oSupplier As String
            oSupplier = Me.ComboBox_Supplier.Text
            Dim sSQL_Supplier As String = ""

            If oSupplier = "[ALL]" Or oSupplier = "" Then
                'do nothing
            Else
                oSupplier = Me.ComboBox_Supplier.Text
                sSQL_Supplier = " And Suppliers.SupplierName = '" & SQLConvert(oSupplier) & "'"
            End If


            Dim oFrom As String = Me.DateTimePicker_From.Value.ToString
            Dim oTo As String = Me.DateTimePicker_To.Value.ToString
            Dim sDateCriteria As String = " And (Transactions.[Transaction Date] >= #" & oFrom & "# And Transactions.[Transaction Date] <= #" & oTo & "#)"


            Dim sSQL As String
            sSQL = "SELECT Transactions.ID, Suppliers.SupplierName, Transactions.Reference, Transactions.[Transaction Date], Transactions.Amount, IIf(DCount('Amount','Transactions','[Reference] = ''' & [Reference] & '''' & ' And ' & '[Transaction Type] = " & TransT_Payment & "' & ' And ' & '[DR_CR] = ''Dr''')=0,[Transactions].[Amount],Transactions.Amount-DSum('Amount','Transactions','[Reference] = ''' & [Reference] & '''' & ' And ' & '[Transaction Type] = " & TransT_Payment & "' & ' And ' & '[DR_CR] = ''Dr''')) As Due "
            sSQL = sSQL & "FROM Suppliers INNER JOIN Transactions ON Suppliers.SupplierID = Transactions.SupplierID "
            sSQL = sSQL & "WHERE ("
            sSQL = sSQL & "DSum('Amount','Transactions','[Reference] = ''' & [Reference] & '''' & ' And ' & '[Transaction Type] = " & TransT_Payment & "' & ' And ' & '[DR_CR] = ''Dr''') < Amount"
            sSQL = sSQL & " And [Transactions].[Transaction Type]=" & TransT_Invoice & ""
            sSQL = sSQL & " And [Transactions].[DR_CR]='Cr'"
            sSQL = sSQL & " And Void = False"
            sSQL = sSQL & sSQL_Supplier
            sSQL = sSQL & sDateCriteria
            sSQL = sSQL & ")"  'worked
            sSQL = sSQL & "Or ("
            sSQL = sSQL & "IsNull(DSum('Amount','Transactions','[Reference] = ''' & [Reference] & '''' & ' And ' & '[Transaction Type] = " & TransT_Payment & "' & ' And ' & '[DR_CR] = ''Dr'''))"
            sSQL = sSQL & " And [Transactions].[Transaction Type]=" & TransT_Invoice & ""
            sSQL = sSQL & " And [Transactions].[DR_CR]='Cr'"
            sSQL = sSQL & " And Void = False"
            sSQL = sSQL & sSQL_Supplier
            sSQL = sSQL & sDateCriteria
            sSQL = sSQL & ")"
      

            Dim connection As New OleDbConnection(ConnectionString)
            Dim dataadapter As New OleDbDataAdapter(sSQL, connection)
            Dim ds As New DataSet()
            connection.Open()
            dataadapter.Fill(ds, "Suppliers_table")
            connection.Close()
            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "Suppliers_table"

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Select allOpen in new window