Results 1 to 4 of 4
  1. #1
    Bleekscheetje is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4

    DoCmd.OpenQuery really necessary?

    Hi,



    I'm using Queries to get the datarecords that I want to use in my VBA code.
    For this query to display only up-to-date data, I always use "DoCmd.OpenQuery" followed with the datagathering/editing that needs to be done, and ending with "DoCmd.Close"

    Now my question: is it really necessary to open the query each time to be able to use it's data (and to be 100% sure that it does not contain an old recordset that might need to be refreshed first)?

    I prefer it when the user doesn't to see this Query open and close when he triggers the sub to execute.

    As an example some bits and pieces of my login script:
    Code:
    Set db = CurrentDb
    
    Password = InputBox("Login Password?")
    
    DoCmd.OpenQuery ("QLogin")
    Set rs2 = db.OpenRecordset("QLogin")
        If Password = rs2.Fields("UPW") Then
    '...
    DoCmd.Close

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,767
    You don't need the DoCmd.Openquery.

    You do need to
    Dim db as DAO.Recordset
    Dim rs2 as DAO.Recordset
    Dim Password as string
    ...
    Set rs2 = db.OpenRecordset("QLogin")
    If Password = rs2.Fields("UPW") Then...

    You may want to review this info.

    Good luck.

  3. #3
    Bleekscheetje is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2015
    Posts
    4
    Thank you orange for your reply!
    The link you posted is extremely usefull. As most of the stuff you find while googleing require the reader to already have some pretty advanced knowledge of VBA coding.
    First thing I read there is that it is much better to use SQL instead of vba recordsets but as a beginner I rather learn to walk first before I try to run ;-)

    Back on topic:
    The query I use in my code compares field UserName in a set of records to a private function that returns the Windows UserName that is logged in.
    I'm worried that when someone else (other windows user and thus a different recordset as a result from the query) opens the file and runs the code without actively running the query, VBA will use data that is not 'up to date' as the query's results hasn't been refreshed yet if you understand what I mean.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I have built a few applications using Access. None of these applications have DoCmd.OpenQuery in them. I cannot recall ever using DoCmd.OpenQuery, even during development time. Your use of DoCmd.OpenQuery in your example is not doing anything to assist the execution of subsequent statements.

    As mentioned in post #2, you need to do a little research about recordsets. I use DAO a lot. I use DAO with SQL a lot. I use DAO with Query objects and additional SQL a lot. You might want to start by looking into DAO and MoveLast, FindFirst, and RecordCount. I use RecordCount to make sure my recordset retrieved records and I use it before trying to MoveLast.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-30-2015, 12:56 PM
  2. DoCmd.OpenQuery with Parameters?
    By mwhcrew in forum Queries
    Replies: 5
    Last Post: 10-10-2014, 01:49 AM
  3. docmd.OpenQuery produces error 2001
    By ultimateguy in forum Programming
    Replies: 4
    Last Post: 08-09-2014, 10:16 PM
  4. Replies: 3
    Last Post: 12-07-2012, 04:26 PM
  5. OpenQuery and Requery
    By Daryl2106 in forum Access
    Replies: 8
    Last Post: 11-24-2011, 08:40 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