I need to run a couple of SQL Server procedures from Access Front-End. I have almost no experience with calling SQL procedures from Access. I created Public Subs to run those procedures (in 2 separate DB's), and a command button on form, which calls those subs.
In our network, access to SQL Databases is allowed through Domain Groups. I.e. A Domain Group is defined, and users added to it. The access to every database in SQL Server and user rights there are determined for Domain Groups.
On SQL Server, I granted Execute permission for Domain Group I'm listed in to both databases. And the group has RW rights in both databases too.
When running the Sub in Access FE, I get the error <[Microsoft][ODBC SQL Driver][SQL Server] Cannot find the object "dummy_AppStruktur1" because it does not exist or you do not have permissions.>
The table dummy_AppStruktur1 exists;
At start I didn't determine UID in connection string, but it looks like I had permissions to access DB, because an attempt to do anything with dummy_AppStruktur1 is deep in SQL procedure (but probably 1st time where the procedure tries to delete something or write into something). After I got error message, I tried also adding UID of my regular or admin accounts - with same result.
Follows an example of Sub where I get the error
Code:
Public Sub UpdateProdStruProductStructure(parProd As String)
Dim connection As Object: Set connection = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
Dim strCommand As String
strCommand = "EXEC MySqlProcedure '" & parProd & "', 'AdditionalParameterText'"
With connection
.ConnectionString = "DRIVER=SQL Server;Server=ServerName\SqlInstance;UID=MyUID;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=MySqlDatabase" // "UID=MyUID;" was added later -and with same result
.CommandTimeout = 0
.Open
End With
Set rs = connection.Execute(strCommand) // Here I got the Error!
connection.Close: Set rs = Nothing: Set connection = Nothing
MsgBox "MySqlDatabase is updated!"
End Sub
Directly on SQL Server, I can run those procedures without any problems!
Obviously I'm missing something here. Any advice, please!