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

Topic

New Reply

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

Reply | Quote

Replies

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/26457953/from-excel-open-a-query-in-access-that-is-already-running

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

What information do you want back and how would you like to view it (or what do you need to do with it)?

Reply | Quote

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.

Reply | Quote

Can I ask why you want to run the query in Access rather than doing it all in Excel?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Thanks very much Rory. I’ll give that a go next week (when I’m back at work).

Cheers
Mike

Reply | Quote

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

Reply | Quote