Solved Plea for help - overflow error (1 Viewer)

[howling_muffin]

I'm trying to use a generic function to execute sql queries in my Access database, but I'm really stuck on the overflow error that keeps coming up.

Public Function ExecuteParameters(sql As String, ParamArray Params() As Variant) As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim inputParam As Variant
    cmd.ActiveConnection = Procs.getConn
    cmd.CommandText = sql
    For Each inputParam In Params
      Set inputParam = cmd.CreateParameter(, GetParameterType(inputParam), 1, Len(Nz(inputParam)), inputParam)
        cmd.Parameters.Append inputParam
    Next inputParam
    
    cmd.CommandType = adCmdText
    Set ExecuteParameters = cmd.Execute()
    Set cmd = Nothing
End Function

Here's where I call it:

ExecuteParameters("SELECT * FROM ? WHERE ? = ?", "Students", "StudentID", 32)

Each time I run this, I get an overflow error on the line Set ExecuteParameters = cmd.Execute(). For the past three days I've tried everything I can think of with no success. Does anyone have any idea why this error has decided to take up residence on this line? Much appreciation for anyone who can point me in the right direction.

 

[theDBguy]

Hi. Welcome to AWF!

I am not very familiar with ADO, but the first thing I noticed was you're trying to "execute" a SELECT query. Could you please try using an ACTION query just to see if it works or not? If not, then I guess we can start to look closer at the code.

 

[howling_muffin]

Hi, thanks for your response
It's actually a DELETE query but while I was trying to figure out where the error is coming from, I changed it to SELECT and then forgot to change it back before copying the code into the post. The results are the same in both cases

 

[theDBguy]

Hi, thanks for your response
It's actually a DELETE query but while I was trying to figure out where the error is coming from, I changed it to SELECT and then forgot to change it back before copying the code into the post. The results are the same in both cases

Okay, to the code examination then. Again, I'm a bit clueless with ADO, so if this is not helpful at all, please let know, and I'll stop trying.

So, using DAO as a reference, I was thinking "parameters" are those that the Database Engine needs help interpreting or resolving. So, looking at your sample code, I hope you're not actually referring to the Table's name as a parameter, are you? If so, is that allowed in ADO?

 

[howling_muffin]

Okay, to the code examination then. Again, I'm a bit clueless with ADO, so if this is not helpful at all, please let know, and I'll stop trying.

So, using DAO as a reference, I was thinking "parameters" are those that the Database Engine needs help interpreting or resolving. So, looking at your sample code, I hope you're not actually referring to the Table's name as a parameter, are you? If so, is that allowed in ADO?

You're right on first guess, and I'm eternally grateful I replaced the table and column references with strings, and the code runs!

 

[The_Doc_Man]

Just as a "dumb" idea that MIGHT actually work, try enclosing your number 32 in quotes IN THE CALL STRING. Don't try to do anything about it in your SQL string. I'm wondering about argument passage in that context. And I don't guarantee that it will work, but it's a simple attempt so won't cost you much.

 

[theDBguy]

You're right on first guess, and I'm eternally grateful I replaced the table and column references with strings, and the code runs!

Hi. Glad to hear you got it sorted out. Good luck with your project.