Link to home
Start Free TrialLog in
Avatar of colinasad
colinasadFlag for United Kingdom of Great Britain and Northern Ireland

asked on

How to execute a SQL Server Stored Procedure from an Access front-end?

Over the past 10 years I have developed several applications using SQL Server as the back-end database with Access "projects" (.ADP) as the front-end applications.

Now that the Access ADP Projects are no longer supported by Microsoft I am trying to upgrade one of my smaller projects to SQL Server 2016 and Access 2016. So far I have managed to get my "linked tables" to work OK but I am having problems executing SQL Server Stored Procedures from the Access front-end.

I have attached a VBA sub procedure that has served me well over the years for achieving this with my .ADP Projects. I call it from many places in my VBA code, passing in the name of the Stored Procedures along with an arrary of parameters that have been populated.
Even though this code compiles OK in my new ACCDB application, I get run-time errors suggesting the Tables cannot be updated.
I have attached a screenshot of one of the error messages.
(Looks like I can only attacch one file at a time, so I will raise a second comment with the error message file.)

I have tried to make sense of suggestions on other web-sites but have still not managed to find a solution.
Can somehow hold my hand through changing this sub procedure (or my Stored Procedures) to make it work?

Many thanks.
SP_VBA_CallingProcedure.docx
Avatar of colinasad
colinasad
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Here is the error message mentioned in my first post.
SP_ErrorMessage.docx
It seems like your stored procedure is referencing a table or query that doesn't exist. Check your stored procedure to make sure you're address the correct table or query.
Avatar of Kelvin Sparks
You haven't posted your VBA, only the message. The error related to the name of the stored proc. Firstly check that it exists and that the user that Access is connecting as has the Execute grant assigned.

Kelvin
If it's linked, often Access includes the schema name, like "dbo.USP_InsertIntoLogFile".
Thanks for the prompt responses.

Kelvin : I think I did attach the VBA procedure I use with my very first post.
I probably could have tidied it up a bit; it is peppered with comments to describe what is happening and I use a local intPosn variable to trace how far I get through the procedure before "HandleError" is invoked.

From the error message I attached with my second posting, you can see the procedure crashes when intPosn has the value 120 which means the procedure is crashing at the cmdCommand.Execute instruction.

I have stripped all the "dbo_" prefixes from the linked Tables and Views, and I can see the contents of the Table OK when I open a Form in my Access front-end.

When I have added "dbo." as a prefix to my Stored Procedure name (as they appear in SQL Server) I get a slightly different error message. I am certainly no expert (hence my questiions here) but my gut-feeling is that I am getting something wrong with the way I am trying to connect to the SQL Server database at this stage.

Thanks for any further thoughts.
Colin.
Yes, I suspect you're right.

I store the connection string as a constant in my Access databases.

Here my standard code for running a stored proc (without any parameters).

Function RunSP(strSP)
On Error GoTo Err_RunSP

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command


Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect
cnn.Open

Set cmd = New ADODB.Command

    With cmd
        .ActiveConnection = cnn
        .CommandText = strSP
        .CommandType = adCmdStoredProc
        .CommandTimeout = 0
        .Execute
    End With
Set cmd = Nothing
Set cnn = Nothing
           
Exit_RunSP:
    Exit Function
   
Err_RunSP:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_RunSP
           
End Function
Thanks again for a prompt response, Kelvin.

Just so I'm clear - your code is from an ".ACCDB" Access front-end connecting to a SQL-Server database through Linked Tables, rather than from an ".ADP" Access Project with a more direct link to the SQL-Server database?

Assuming it is, I think we are homing in on the cause of my problem.
My piece of code contains a call to another sub procedure called "OpenDBConnection" (at intPosn = 30).
I think my "OpenDBConnection" procedure is still trying to behave like an Access .ADP Project - the important part of that sub procedure is :

' Just use the Access Project CurrentProject Object - simpler
Set cnConnection = CurrentProject.Connection

Is there a way I can find the Connection String from within an Access .ACCDB application?
I guess I could use a hard-code string like you seem to do.
What does your sConnect connection string look like?

Thanks again.
Colin.
Correct, apart from one client using an apd, all my accdb databases now connect to SQL Server. I use dsn-less connects rather than the dsn's and linking, use that code I supplied to execute SPs (both ones that return data and those that don't), and a simple "one-liner" to return a result from a SQL Function.

Kelvin
Thanks again, Kelvin.

I am using a .DSN connection with Linked Tables because I read about that first. I have not explored the DSN-less option yet.

Presumably I should still be able to execute my SQL-Server Stored Procedures and Functions with a DSN connection.

Can anyone tell me where I look to find the Connection String I should be using in my VBA code?
Or what the structure of such a Connection String should be?

Thanks.
Colin.
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks for stick with me on this, Kelvin.

I'm sure we are getting closer to solving my connection but it is still not working for me.

Using the long Connection String method as you suggest (I can see in my code that I used to use a similar method until I discovered that
"Set cnConnection = CurrentProject.Connection" was a simpler piece of code that worked).

I have a separate sub procedure named "OpenDBConnection" and I have recoded it as below (I have left my Server and Database names in full - I don't think there is too high a security risk I want to make sure I am getting the syntax and punctuation absolutely correct).

Set cnConnection = New ADODB.Connection
 cnConnection.ConnectionString = "Driver={SQL Server Native Client 11.0};Server=ASADDELL2017\SQLExpress;Database=FamilyDB;Trusted_Connection=True"
cnConnection.Open

However, that sub procedure is now crashing with the run-time error message :

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specifed

Are there some "References" that need to be made available or some other Access or SQL Server settings you can think of?

I am running Access 2016 and SQL Server 2016 on a new 64-bit Windows 10 laptop (but when I look in Task Manager my Access, SQL Server and Firefox tasks all have "(32 bit)" after their names.

Any further suggestions greatly appreciated.

Regards.
Colin.
Made another short step forward myself.

Looking at the ODBC values in my Windows Adminsitrative Tasks, I found that I appear to have the driver :
"SQL Server Native Client 11.0".

Using that in my Connection String brought up a different error message about an invalid value for the attribute "Trusted_Connection".
Am pursuing that now.

Colin.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kelvin did the main work - helping with the database connection string.
I merely debugged a couple of further minor issues.
Many thanks.
That's what I get when relying on my ancient memory. Trusted_Connection=Yes is the correct syntax. Well done, and happy to have assisted.

Here's one that you'll find hard to get the answer for, but has made us a bit of a "hero" with our customers. DLookups can be very slow using ODBC and SQL Server - especially against large databases. I found this that allows you to use a User Defined Function in SQL Server to retrn that value from a Function. This is an Access Function that retrieves a value from a SQL Server named  dbo.SQL_Calc_Commission. I'm pasing in 5 parameters and getting one value back. For one customer, it replaced a 18 second delay for a DLookup (I know that they're no-no's) with a SQL function that returned the value in less than a second)

Public Function CalcCommission(p_Val As Double, p_Quant As Long, p_Curr As String, p_Type As String, P_Under As String) As Double

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command


Set cnn = New ADODB.Connection
cnn.ConnectionString = sConnect
cnn.Open

CalcCommission = cnn.Execute("SELECT dbo.SQL_Calc_Commission(" & p_Val & "," & p_Quant & ",'" & p_Curr & "','" & p_Type & "','" & P_Under & "')")(0)

cnn.Close
Set cnn = Nothing


End Function

All the best with your conversion.

Kelvin