1

I am attempting to run a query in access from excel, and then have those results pulled into the excel document using ADO in VBA. Unfortunately, I cannot figure out how to run the access query such that data in the active cell of the excel sheet is used as a criteria in the access query.

I am running Excel and Access 2007. I've included what code I have so far below. Thanks in advance for your help. 

Sub testdb()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Set con = New ADODB.Connection
With con
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open "H:\WBC\Lukas\STOP.accdb"
End With
con.Execute "HPRSearch"
   'the criteria field is 'Input', and I need to pull it from the active cell on the Excel Sheet
End Sub
Community
  • 1
  • 1
user3780550
  • 11
  • 1
  • 1
  • 3

1 Answers1

4

The first thing you need to do is set up your parametereized query in Access. So, say, Query1 is (where ID is an integer):

SELECT ID FROM Table1 WHERE ID = [MyID];

The brackets around [MyID], if it doesn't resolve to a field name, will be considered a Parameter. Now, say, we want to bring back the record with ID 1. Set up your code in Excel:

Sub testdb()

    Dim con As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim rs As ADODB.Recordset

    Set con = New ADODB.Connection
    Set cmd = New ADODB.Command

    With con
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .Open "H:\WBC\Lukas\STOP.accdb"
    End With

    With cmd
        .ActiveConnection = con
        .CommandText = "Query1"
        .CommandType = adCmdStoredProc

        .Parameters.Append cmd.CreateParameter("MyID", adInteger, adParamInput)
        .Parameters("MyID") = 1
    End With

    Set rs = New ADODB.Recordset
    rs.Open cmd

    Do Until rs.EOF
        Debug.Print rs.Fields("ID").Value
        rs.MoveNext
    Loop

    rs.Close
    con.Close

    Set cmd = Nothing
    Set rs = Nothing
    Set prm = Nothing
    Set con = Nothing

End Sub

This reference adInteger found in this line

.Parameters.Append cmd.CreateParameter("MyID", adInteger, adParamInput)

should be replaced with the proper constant that represents the variable type (see here: http://www.w3schools.com/ado/met_comm_createparameter.asp) of the Parameter in your query. In your case, you would set the Parameter value that's represented in this line

.Parameters("MyID") = 1

with the value from your cell.

And that's it. So you create the Connection, create a Command object (which is essentially a reference to your Access query), set the Command object's properties, including the parameter, then have the results brought back in a recordset. Then loop through the recordset and do what you want with the values.

VBlades
  • 2,241
  • 1
  • 12
  • 8
  • Thanks for your help. Unfortunately, I'm still running into an error on the .Parameters.Append line. Excel tells me that the "Parameter object is improperly defined. Inconsistent or incomplete information was provided. Any idea why that might occur? – user3780550 Jun 27 '14 at 13:43
  • See this page: http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0e7c-errors.html. Are you specifying a size for your variable type? – VBlades Jun 27 '14 at 14:07
  • Yup, that was the problem. Thanks! Last question. That first line you present, does the SELECT ID line, does that go in the Excel macro or the Access Query? I'm getting an additional error now (Item cannot be found) and I imagine it's somehow related to that. – user3780550 Jun 27 '14 at 14:40
  • That's your Access query. – VBlades Jun 27 '14 at 21:14
  • This wound up working perfectly on my machine. Sorry for the lateness, but thanks a ton. There was just one other problem. I changed the path to reference the actual database (the one here was just a test one on my machine, the actual one is on a server that all employees have access too). However, I now get a seemingly random error rs.Open cmd line, saying that the function 'FormatPercent' is not defined. I have absolutely no idea where that's coming from, as I have defined no such function. – user3780550 Jul 02 '14 at 19:42
  • @user3780550: Do a search in the database that works for FormatPercent (search through the whole project as opposed to just certain modules). Find out where it is and copy that function to the other database. It's maybe not used in your query, but somewhere along the line of the queries it is based on, sounds like it is being used. So long as you copy the function over, though, you may not care where it's being invoked, so long as it runs. Lol. – VBlades Jul 02 '14 at 20:08
  • This is a very useful post! The link to w3cshools on create parameter changed to [http://www.w3schools.com/asp/met_comm_createparameter.asp](http://www.w3schools.com/asp/met_comm_createparameter.asp) – chapter3 Jul 09 '15 at 03:09