Tracking down an invalid procedure call error in a SQL VBA execute command

Question

Thanks for opening this. I've been wracking my brain for the past couple of days trying to make heads of tails of this error (Run-time error '5': Invalid procedure call) error that has blocked me at every turn.

Essentially, my goal is to run and execute a SQL command to find the largest number in a given category and store it into a variable for later use. I've tried different ways to go about it and no matter how I do it (either in VBA using a DMAX or through SQL) I run into the same error whenever I try to execute the command. I've gotten some help working through it but I think there's some deeper issue that I am not understanding with VBA.

Here is the code:

Public Function GetMaxValue(Child As String)

    Dim RAC As DAO.RecordSet
    Dim Prefix As String
    Dim MaxVal As Long
    Dim SearchString As String

    NewPrefix = Child
    SearchString = "SELECT MAX([SalesValue]) FROM [SalesTable] WHERE [Prefix] = '" & NewPrefix & "';"

    Set RAC = CurrentDb.OpenRecordset(SearchString)

    If RAC.Fields.Count = 1 Then
          MaxVal = RAC.Fields(0)
    End If

    RAC.Close
    Set RAC = Nothing

End Function

It breaks whenever I hit the line that reads Set RAC = CurrentDb... with:

Invalid procedure call error

Please let me know if anyone has any idea what produces this error. I've searched everywhere for a possible explanation and I can't find anything that would cause my code to break whenever I try to run a max function. I even made sure that the SalesValue was a Number field in the underlying Access table and that everything was spelled correctly.

Thanks!

Put a debug breakpoint on that line and copy us the value that RAC gets when the error is thrown. — Jacob H
– Jacob H, Commented Aug 25, 2017 at 16:38
Can you run a simple select query using that same function? Try dropping the terminating semicolon. — Tim Williams
– Tim Williams, Commented Aug 25, 2017 at 16:41
debug.print you SearchString and paste it into a query sql window and run it. See what happens? — Minty
– Minty, Commented Aug 25, 2017 at 16:41
The part WHERE [Prefix] = '" & NewPrefix & "';" is bad. You can get a syntax error in your query that way and get the mentioned error. — Vojtěch Dohnal
– Vojtěch Dohnal, Commented Aug 25, 2017 at 16:44

Parfait · Accepted Answer · 2017-08-25 21:40:26Z

2

You can get the error

Invalid Procedure Call

on line

CurrentDb.OpenRecordset(SearchString)

just because your SearchString does not contain a valid SQL query.

Never use string concatenation to pass parameters to a query. This is bad: WHERE [Prefix] = '" & NewPrefix & "';"

See this answer and parametrize your query.

See this documentation.

First create a query definition:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = dbs.CreateQueryDef("qrySearchQuery")
Application.RefreshDatabaseWindow

strSQL = "PARAMETERS NewPrefix TEXT"
strSQL = strSQL & "SELECT MAX([SalesValue]) FROM [SalesTable] "
strSQL = strSQL & "WHERE [Prefix] = [NewPrefix];"
qdf.SQL = strSQL

qdf.Close
Set qdf = Nothing

Then you can call it:

NewPrefix = Child

Dim rst As DAO.Recordset
Set qfd = dbs.QueryDefs("qrySearchQuery")
qdf.Parameters("NewPrefix") = NewPrefix
Set rst = qdf.OpenRecordset()

edited Aug 25, 2017 at 21:40

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered Aug 25, 2017 at 16:48

Vojtěch Dohnal

8,1123 gold badges53 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

Kudos for properly parameterized SQL. String-concatenated queries are such a plague!

jahamj Over a year ago

@Vojtěch Dohnal - thank you very much for your help! If I understand correctly, the parameter described in the documentation and example eliminates the need for concatenating the value directly into the query definition, right? That makes sense; I thought the string concatenation was the approved way of doing this but I guess I was wrong. One quick question about the sample code though - in the line "Set qdf = dbs.CreateQueryDef("qrySearchQuery") is this supposed to be pulling value from something else?

jahamj Over a year ago

So when I run it, I trigger an error on the line "Set qdf = dbs.CreateQueryDef("qrySearchQuery"). I'm struggling to understand what this line does; is it necessary to define this up here if you do the same thing below?

Gustav · Accepted Answer · 2017-08-25 18:06:32Z

0

You could use DMax:

Public Function GetMaxValue(Child As String)

    GetMaxValue = DMax("[SalesValue]", "[SalesTable]", "[Prefix] = '" & Child & "'")

End Function

answered Aug 25, 2017 at 18:06

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

1 Comment

jahamj Over a year ago

Hi @Gustav - Thank you for your reply. It's the strangest thing, but I tried this approach earlier today and I had the same error that I have now with the SQL. I think it's something with the string concatenation but it was driving me absolutely nuts so I went in another direction with it (SQL instead of DMax) just because I couldn't get it to work no matter how much I tweaked it I don't understand why it doesn't work to be honest though..

Collectives™ on Stack Overflow

Tracking down an invalid procedure call error in a SQL VBA execute command

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related