Results 1 to 3 of 3
  1. #1
    emanon is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8

    Very slow using link tables to SQL Server and VBA codes

    Hello. I am not sure if there is a way to improve performance. When I click on the button to query the link tables, it takes like 4 to 5 minutes to return one row. I am not sure if I am doing something wrong or if there is another trick to improve the performance. Below is a code that looks like what I have.



    Below codes are in Private Sub cmdQuery_Click()
    Dim strSQL As String



    ' Columns
    strSQL = "Select Top 10 "
    strSQL = strSQL + "P.Field1, "
    strSQL = strSQL + "R.field2, "
    strSQL = strSQL + "P.Field3 "


    ' Can't do inner join
    strSQL = strSQL + "From Table1 R, Table2 P "

    'Where Clause
    strSQL = strSQL + "Where P.= Field1'" + Nz(Me.cmbPlan.Value) + "' "
    strSQL = strSQL + "and R.Field1= P.Field1"

    'ReceiverType
    If Nz(Me.cmbRec.Value) <> "" Then
    strSQL = strSQL + "and R.Field3= '" + Nz(Me.cmbRec.Value) + "' "
    End If


    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("qryResult")
    qdf.SQL = strSQL


    DoCmd.OpenQuery ("qryResult")
    Set qdf = Nothing

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Haven't needed to use SQLServer, much, but when concatenating in VBA, you need to be using an Ampersand & and not a plus sign +, i.e.

    strSQL = strSQL + "P.Field1, "

    should be

    strSQL = strSQL & "P.Field1, "

    Be careful to include spaces in the appropriate spaces withing your quotes.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Are both fields in that you can't join indexed ?
    And how many records are in each table?

    Finally why can't you use a join, is it mixed data types ?

    Edit: Actually that SQL has an error in the where clause, I'm surprised it runs.

    What do you get if you debug.print the strSQL ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 11-24-2019, 08:24 AM
  2. Replies: 2
    Last Post: 06-08-2017, 07:32 AM
  3. Replies: 9
    Last Post: 05-30-2017, 01:49 PM
  4. Link tables to server name, not mapped drive letter in Win 7
    By sleake in forum Import/Export Data
    Replies: 8
    Last Post: 11-16-2012, 10:48 AM
  5. Replies: 1
    Last Post: 07-17-2010, 08:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums