Microsoft Access: Execute a Stored Procedure and return results using DAO - Errors

Question

I am trying to execute a stored procedure in Access 2013 using VBA. On 64-bit Windows 10 with 32-bit Access 2013 (corporate standard, can't change that).

Connecting to a SQL Server 2008.

Not sure why this is so hard, but I just can't make it work!

I have a generic function named SQLExec:

Public Function SQLExec(SQL as String) As DAO.Recordset
    Dim Rs As DAO.Recordset
    Dim MyDB As DAO.DATABASE

    Debug.Print SQL

    On Error GoTo SQLExecErr

    Set MyDB = CurrentDb
    Set Rs = MyDB.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)

    Set SQLExec = Rs

CleanUp:
    Set Rs = Nothing
    Set MyDB = Nothing

Exit Function

SQLExecErr:
    Debug.Print Err.Number, Err.Description
    MsgBox "Error " & Err.Number & " in SQLExec: '" & Err.Description & ".'", vbOKOnly + vbCritical, "Error in SQLExec"

    If IsDeveloper() Then
        Stop
        Resume
    Else
        Resume CleanUp
    End If

End Function

I try to pass in a procedure name with parameters, but it always fails.

sReplaceDevice 'AA000000', 1000, 'AA000000', 'AA000001', 19"

Error:

Error 3129 in SQLExec: 'Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'..'

I tried prepending EXECUTE and PROCEDURE - same result. I tried adding the parameter IDs to the query (@param1 ='test', for example.) - still same result.

Does anyone have any suggestions? I'm dumbfounded that this should be so hard.

EDIT: This question has been marked as a duplicate of another question. Firstly, it would be nice if a link to that other question were provided. Secondly, I have searched and searched on this question and have come up with several solutions, some from StackOverflow, NONE OF WHICH WORK. Any input would be appreciated.

Where is the duplicate? I have searched Stack Overflow and found other similar questions, but those solutions don't work either. — Barry Seymour, Jan 18 '17 at 15:40
The "duplicate" banner at the top of the question contains the [link](http://stackoverflow.com/questions/40530836/invalid-sql-statement-error-when-trying-to-execute-sql-server-stored-procedure). — Gord Thompson, Jan 18 '17 at 16:15
Just a note, you can't do `Set Rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot, dbSQLPassThrough)` - where would the ODBC connect string come from? You need a PassThrough query. — Andre, Jan 18 '17 at 16:57

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

Does sReplaceDevice return a value? Or just update data? If it's just performing an update, then the problem is likely that you're trying to open it as a record set, whereas you should just be doing ExecuteNonQuery. Like this

edited May 23 '17 at 12:24

Community

1
1

answered Jan 18 '17 at 06:05

CustodianOfCode

674
3
12

sReplaceDevice performs an update AND returns a value. The procedure works, the SQL I was assembling was correct, but still it threw errors in Access. I then tried to execute a separate query to get the ID of the newly created record. Since I was querying a table with an identity column it told me I had to use 'dbSeeChanges', even though I WAS. In the end my solution was to use a query with the MAX aggregate function in order to get the ID of the new record created in the relevant table. At that point it stopped complaining about an identity column and returned the value I wanted. – Barry Seymour Jan 18 '17 at 16:06
DUH. I missed the link to the duplicate question above. My apologies. – Barry Seymour Jan 18 '17 at 16:11

Microsoft Access: Execute a Stored Procedure and return results using DAO - Errors

1 Answers1