User rights problem when running SQL Server procedure from Access FE

[ArviLaanemets]

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

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!

[ranman256]

are you able to add the table as an external linked table? does it allow?

[ArviLaanemets]

are you able to add the table as an external linked table? does it allow?

Link the table to Access FE? I'll try tomorrow. I have some doubts now!

The 1st procedure is called from DB, not linked to FE at all. The second procedure is called from linked DB, but script was stopped without reaching the 2nd procedure. And tables in linked DB aren't directly linked to FE - linked are views based on those tables.
More detailed overview:
The database linked to FE links various documents with queried products, clients purchasing those products, components purchased to produce those products, and suppliers delivering those components. All those (except links to documents) are read from our ERP database, but the ERP database doesn't contain a structured table of product components (product structure is calculated on fly) which is usable with Access FE, so I have created a SQL database where such table is calculated for various users of several DB's (Ths DB was created almost 10 years ago). The full refreshing of this database takes several hours, and is run once per day (nightly). My new DB reads the structure of all registered products from this DB, but in case there is some new product registered, for which there is no structure calculated, currently user has to wait a day to continue with task.

I created a new procedure for product structure database, which calculates structure for single product (product number is passed as parameter). And another procedure for my currently developed database to update this single product structure from product structure database. The idea is, both those procedures are called from Access FE (from Command Button event), and current product number in active row of form is passed as parameter. Updating structure for single product takes a second or some seconds only, what is acceptable for users when they need to refresh info about product.

[NoellaG]

Have you tried to set up a DNS file with the ODBC connection? There you can already test if the connection string works.

[ArviLaanemets]

The case is solved! I had to replace all TRUNCATE TABLE's with DELETE FROM in SQL Procedure!

[NoellaG]

Thx for sharing the solution.
To perform bulk operations you need ADMINISTER BULK OPERATIONS rights. This is a server-wide role, but db_owners have the ADMINISTER DATABASE BULK OPERATIONS right on that database.
If you need to delete a lot of records, DELETE from can be very slow because it needs to write to the LOG file, so if you make your account db_owner of the database where you want to delete from, the proc should work.
What puzzles me is that, directly on SQL server, you do have the rights to do this (db_owner?). Do you log on with a different account from Access? I'm just curious and would like to know the precise reason this happened.
Using sp_who2 can show you who is logged in.

[ArviLaanemets]

TWhat puzzles me is that, directly on SQL server, you do have the rights to do this (db_owner?). Do you log on with a different account from Access? I'm just curious and would like to know the precise reason this happened.
Using sp_who2 can show you who is logged in.

All our IT specialists have a regular account, and separate admin user account with (some) administrator rights (depending on his/her work character). I used my admin account when logging into SQL Server, and my regular account when working with DB FE - so this explains the different behaviour.

I'll leave currently all as it is (using DELETE instead TRUNCATE) - I don't think giving all users of database having OWNER rights is a good idea!