Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question From Excel, open a query in Access that is already running

    I have both Excel 2010 and Access 2010 already running on my Windows 7 PC. The worksheet has hundreds of rows and many columns eg, Surname (column A), Given name (col B), etc. The database has an existing query that asks for two parameters – Surname and Given name – as input to retrieve matching records. Because I use that query many times throughout the day to check on different names from the worksheet, manually typing in the names each time becomes a chore (and prone to typing errors) so I would like to automate that process.

    I came up with the macro below in Excel to read the name values from the active row, switch over to the Access database (which is already running), open the existing query, and feed the values into the parameters. It seems to work (the hourglass cursor appears for the same number of seconds as if I had run the query manually, and the macro ends without error) but the problem is that Access doesn’t show any results (it is still running but doesn’t open the query datasheet window). Stepping through the macro line by line, it seems that after switching over to the running instance of Access, the query and parameters are not being processed in that instance of Access yet it steps through to the end of the macro without giving an error. Thanks in advance.

    Code:
    Sub test()
    Dim PatSurname As String, PatFirstName As String
    Dim db As DAO.Database, qry As DAO.QueryDef, rs As DAO.Recordset
    
    Sheets("UniqNames").Activate
    PatSurname = Selection.End(xlToLeft).Value
    PatFirstName = Selection.End(xlToLeft).Offset(0, 1).Value
    
    AppActivate "Reporting Server"  'this is the db appname that appears in Task Manager
    Set db = CurrentDb
    Set qry = db.QueryDefs("Retrieve DVA Client details via FacilID & MRN or Name ~PCD")
    
    With qry
        .Parameters("[First Name?]") = PatFirstName
        .Parameters("[Surname?]") = PatSurname
    End With
    
    Set rs = qry.OpenRecordset
    
    Set db = Nothing
    Set qry = Nothing
    Set rs = Nothing
    End Sub

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Do you really want to open the query in that instance of Access rather than, say, showing the results in Excel?

    Also, cross-posted here: http://stackoverflow.com/questions/2...lready-running
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    233
    Thanks
    0
    Thanked 22 Times in 21 Posts
    Quote Originally Posted by Mike.10 View Post
    I have both Excel 2010 and Access 2010 already running on my Windows 7 PC. The worksheet has hundreds of rows and many columns eg, Surname (column A), Given name (col B), etc. The database has an existing query that asks for two parameters – Surname and Given name – as input to retrieve matching records. Because I use that query many times throughout the day to check on different names from the worksheet, manually typing in the names each time becomes a chore (and prone to typing errors) so I would like to automate that process.

    I came up with the macro below in Excel to read the name values from the active row, switch over to the Access database (which is already running), open the existing query, and feed the values into the parameters. It seems to work (the hourglass cursor appears for the same number of seconds as if I had run the query manually, and the macro ends without error) but the problem is that Access doesn’t show any results (it is still running but doesn’t open the query datasheet window). Stepping through the macro line by line, it seems that after switching over to the running instance of Access, the query and parameters are not being processed in that instance of Access yet it steps through to the end of the macro without giving an error. Thanks in advance.

    Code:
    Sub test()
    Dim PatSurname As String, PatFirstName As String
    Dim db As DAO.Database, qry As DAO.QueryDef, rs As DAO.Recordset
    
    Sheets("UniqNames").Activate
    PatSurname = Selection.End(xlToLeft).Value
    PatFirstName = Selection.End(xlToLeft).Offset(0, 1).Value
    
    AppActivate "Reporting Server"  'this is the db appname that appears in Task Manager
    Set db = CurrentDb
    Set qry = db.QueryDefs("Retrieve DVA Client details via FacilID & MRN or Name ~PCD")
    
    With qry
        .Parameters("[First Name?]") = PatFirstName
        .Parameters("[Surname?]") = PatSurname
    End With
    
    Set rs = qry.OpenRecordset
    
    Set db = Nothing
    Set qry = Nothing
    Set rs = Nothing
    End Sub
    It's not clear what you're trying to do here, or rather, it's not clear what you think you're trying to do!!

    When you use DAO to talk to a database from VBA in Excel (or Word or whatever) you are communicating with the underlying database engine directly to perform operations against the data contained in the database. This has nothing to do with Access or its user interface! The same code would run successfully on a machine that didn't even have Access installed (provided the DAO libraries were registered on that machine).

    You're not running any code that affects the Access user interface at all, which is why you're not seeing anything happening in your instance of Access.

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I just thought opening the query in that instance of Access is the closest way to how I would do it manually, with Excel and Access running in two windows side by side on the screen. Is there a better way?

  5. #5
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jeremybarker View Post
    It's not clear what you're trying to do here, or rather, it's not clear what you think you're trying to do!!

    When you use DAO to talk to a database from VBA in Excel (or Word or whatever) you are communicating with the underlying database engine directly to perform operations against the data contained in the database. This has nothing to do with Access or its user interface! The same code would run successfully on a machine that didn't even have Access installed (provided the DAO libraries were registered on that machine).

    You're not running any code that affects the Access user interface at all, which is why you're not seeing anything happening in your instance of Access.
    I'm relatively new to VBA coding so yeah, I'm sure there are better ways. I have Excel and Access opened in two windows side by side on the screen. What I'm trying to do is by running a macro, it passes the name values in Excel's active row over to the opened instance of Access to run the specified query and feed the name values into the awaiting parameters.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What information do you want back and how would you like to view it (or what do you need to do with it)?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    What information do you want back and how would you like to view it (or what do you need to do with it)?
    Hi Rory, I would like Access to run the existing query, which if it finds a matching name, retrieves that person's transaction history in datasheet view and if not, returns a blank datasheet (just as if I had run that query in Access manually). No need for Access to pass any information back to Excel.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can I ask why you want to run the query in Access rather than doing it all in Excel?
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    Can I ask why you want to run the query in Access rather than doing it all in Excel?
    Well, since the Access database is already opened, I thought getting it to run the query would involve the least amount of coding, and besides, the database has other queries that I need to run afterwards to check on other things.

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    OK then - you basically need:
    Code:
        Dim appAc As Access.Application
        
        Set appAc = GetObject(, "Access.Application")
        
        With appAc.DoCmd
            .SetParameter "[First Name?]", PatFirstName
            .SetParameter "[Surname?]", PatSurname
            .OpenQuery "Retrieve DVA Client details via FacilID & MRN or Name ~PCD"
        End With
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    Mike.10 (2014-10-23)

  12. #11
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks very much Rory. I'll give that a go next week (when I'm back at work).

    Cheers
    Mike

  13. #12
    New Lounger
    Join Date
    Oct 2014
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Rory

    Upon testing your code, I encountered this error message :-

    “Reporting Server cannot find the name ‘JOHN’ you entered in the expression.” (JOHN is the first name)

    Googling around, it appears that the DoCmd.SetParameter method requires string values to be surrounded by 4 double-quote characters as in """" & PatFirstName & """". With that amendment, your code runs successfully, but only if the query was not already opened. When the query is already opened in Access, running the code again to check on a different next name has no observable effect (no processing time, no error, and query still shows the results of the previous run).

    As a work-around, I added extra code to close and reopen the same query but it is not as fast nor as elegant as just being able to refresh the query while it is opened (like by pressing the F5 key) and then feeding the next set of names to the parameters. Any ideas? I played around with DoCmd.RefreshRecord and .Requery without luck. Thanks for taking the time to help.

    Here is your code with my amendments :-

    Code:
    Dim PatSurname As String, PatFirstName As String, QryName As String
    Dim appAC As Access.Application
    
    Sheets("UniqNames").Activate
    PatSurname = Range("A" & ActiveCell.Row).Value
    PatFirstName = Range("B" & ActiveCell.Row).Value
    QryName = "Retrieve Client details"
    
    Set appAC = GetObject(,"Access.Application")
    With appAC.DoCmd
        .SetParameter "[First Name?]", """" & PatFirstName & """"
        .SetParameter "[Surname?]", """" & PatSurname & """"
    End With
    
    If SysCmd(acSysCmdGetObjectState, acQuery, QryName) = acObjStateOpen Then
        DoCmd.Close acQuery, QryName
        DoCmd.OpenQuery QryName
    Else
        DoCmd.OpenQuery QryName
    End If
    
    Set appAC = Nothing

Tags for this Thread

Posting Permissions

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