Consulting

Results 1 to 4 of 4

Thread: Best Practice Advice

  1. #1

    Best Practice Advice

    When writing SQL statements in VBA procedures that will use them to open recordsets via ADO, any advice on syntax.

    For example, If I create a query in Access and copy the Resulting SQL to my VBA procedure it may look something like this:

    [VBA]sSQL = "SELECT A.[xxx], B.[yyy], A.[zzz] " & _
    "FROM tblAlpha A INNER JOIN tblBeta B ON A.[ID] = B.[ID];" [/VBA]

    Any advantage over using the Join statement as compared to something like this:

    [VBA]sSQL = "SELECT A.[xxx], B.[yyy], A.[zzz] " & _
    "FROM tblAlpha A, tblBeta B " & _
    "WHERE A.[ID] = B.[ID];"
    [/VBA]

    I'm looking at building a routine to construct the needed SQL based on some User selections/actions and was hoping to nail down a syntax that would be easy to logically construct based on the state of the user interface when requested.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Why not just run the two queries and look at the query plan?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Not sure what you mean by "Query Plan".

    I can build a query in Access with the Query Designer and it will build the INNER JOIN sql. I can then go change the SQL to resemble the latter example above with the WHERE clause. Both return identical results.

    I guess my question was one of efficiency. On larger databases is there an impact on performance with one versus the other? I haven't created a large DB to test it on. Maybe I'll go do that.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Sorry, I was assuming you were using SQL server not Access.

    I knocked up a similar query in SQL Server, and checked the execution plans, and they were identical. I admit, I expected the JOIN to be more efficient, but in my data, no difference.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •