Microsoft Access 2016 And using Interface.execSP to run SQL procedure

Question

I have an issue with an Access-based program. We connect the access database to a SQL database, then store all the raw data in SQL, and use Access for the forms, reports, etc. to interface with the database. The SQL tables are linked to the Access database, but I have some places where we've done "SQL optimizations" but having Access call a SQL procedure to run updates, insert data into tables, etc.

This has worked fine up through Access 2007, and I believe Access 2010. But we've updated a couple of computers to Access 2016 and suddenly it's not working. we are getting the generic "Runtime Error 91: Object variable or with block variable not set."

Here is the code we are using:

Public Function callStoredProcedure(strProcText As String)

If myDB.boolUsingSQLServer = False Then
    MsgBox "Error, cannot call stored procedure when connected to MS Access database.", vbCritical, "Call Stored Procedure"
Else
    myDB.Interface.execSP (strProcText)
End If

End Function

We do a check to make sure it is connected to the SQL database, then if so run the procedure. We are doing things like creating a new record in our contact database, so we can then start entering the data; and another to auto-set some default values. Examples of these procedure calls we are passing are:

"prInsterContact 1, 'New'"

"prCreateMisc 1"

Anyone had issues with Access 2016 and using "Interface.execSP"? I've exhausted my ideas on how to fix, I've tried to double check what reference libraries I'm using, I've tried altering how I call the stored procedure. None of it seems to work. I've tried doing Google searches for similar issues, but don't seem to have much luck finding anything regarding Access and Interface.execSP.

Thank you in advance for any help, this has been driving me nuts for a while now.

Answer 1

On what line of the code does the error occur?

I don't know what Interface.execSP is but I'm wondering if this is a custom COM library. Have you looked at the References in your Access project? Did you switch to the 64-bit version of Microsoft Access 2016?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

That code does not look like standard Access code.

You have to expand on the “who” or “how” or “what” code created the myDB object?

Where is this object coming from?
Interfce.exeSP has NEVER been associated with standard Access applications.

The code you have posted is NOT standard VBA + AccDB code.

Perhaps this was an ADP project at one time?
 
So you have to provide additional details as to what myDB.Interface.execSP is all about – but this does not look to be a standard Access VBA code or library of code.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Answer 3

Sorry, I wasn't aware of that, I was able to dig around and find the procedure in a custom DLL file that was created a while back the code for the procedure is:

Public Function execSP(strProc As String)

Shell "MySoftware.exe /storedProcedure " & connString & Space(75 - Len(strProc)) & strProc, vbNormalFocus

End Function

In this the ConnString is a reference used throughout the Access program to point to an ODBC connection, to connect to the SQL database. So it looks like it is trying to use a Shell command to run this. Could that be the issue, that we can't use the Shell call? Or maybe the specifics of the call need changed?

Thank you for any help.