Results 1 to 3 of 3
  1. #1
    sneuberg is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2014
    Posts
    23

    Embedding SQL in VBA vs Using Stored Querydefs


    Somewhere I recall reading that it was very bad to put SQL in your vba code, but it was ok if it was in stored queries and executed by the vba, but I can’t remember why. I thought that putting SQL in the vba code was unavoidable in certain situations but now that I’ve learned a bit more about QueryDefs I see that I could put a lot more (perhaps all) of my SQL in stored Querydefs. I’m wondering if I should. Can anyone tell if and why it’s bad to have SQL embedded in vba and if so does this also apply to using Dcount and DLookup?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I have lots of SQL in VBA. I also have tons of DLookup in VBA. True, modifying the SQL means having to modify code as opposed to using the query builder. Not sure why that would be very bad, I see it as just a matter of preference but maybe my lack of formal training in db programming has led me astray.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    It is not bad for SQL to be in the VBA. Not in terms of anything regarding functionality of the database.

    From a testing debug point of view it is a lot easier to have defined queries in the navigation pane, rather than in sql / vb code. So one really is always better doing this.

    There are some dynamic design requirements where the need to vary the sql code is such that one must rely on the flexibility & decision logic of a vba/sql approach.

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

Similar Threads

  1. Performance of nested querydefs
    By Javier Hernández in forum Queries
    Replies: 4
    Last Post: 12-13-2014, 06:52 PM
  2. Connection and QueryDefs
    By dccjr in forum Programming
    Replies: 11
    Last Post: 01-15-2013, 09:14 PM
  3. Replies: 1
    Last Post: 02-08-2012, 04:58 PM
  4. Working with QueryDefs and ADO...
    By ETCallHome in forum Programming
    Replies: 1
    Last Post: 03-16-2011, 07:07 AM
  5. CurrentDb.QueryDefs
    By sarnata in forum Queries
    Replies: 3
    Last Post: 09-09-2010, 09:05 AM

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