How to pass parameter from Access pass-through to SQL stored procedure (1 Viewer)

Setare

New member
Local time
Today, 04:24
Joined
Aug 13, 2020
Messages
11
Hello,

I have spent several hours trying to figure this out but have not succeeded. So, I need help please!

I have a SQL stored procedure where it deletes a row from a table based on a Username and then inserts new rows for that Username based on the Username and Department that is entered:

DELETE FROM [dbo].[UserPermissions] where Userid = @user
INSERT INTO [dbo].[UserPermissions]
SELECT @user, Divisions.division FROM (Select division from tbl_Divisions where Department = @Dept) as Divisions;


I also created an Access form with two drop-down boxes for Userid and Department and captured the user selection in a couple of global variables. Then, put a command button on the form and tried several different things in the Click_Event to pass the parameters (through the global variables) to the pass-through query.

I also tried bypassing the stored procedure altogether and instead doing the Delete and Insert statements right in the Click_Event of the form. But, nothing worked. I could get the Delete statement working fine but the INSERT statement is throwing errors.

So, after a few hours of trial and error, I decided to lower my expectations and ditch the form and just try to make the pass-through query prompt me for the parameters. When I run the stored procedure in SQL, I get prompted for the parameters. So, I just want Access to prompt me as well but can't figure out how to do so. Here is the code in my Access pass-through:

Execute [OMBudget].[dbo].Update_UserPermissions UserName, Department

My SQL stored procedure is called, "Updata_UserPermissions".

Any help is much appreciated.

Thanks,
Setareh
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:24
Joined
Jan 20, 2009
Messages
12,849
An Access Pass Through Query cannot take parameters. You need to use VBA to edit the SQL property of the querydef.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:24
Joined
May 7, 2009
Messages
19,171
you Can pass the parameter:

Code:
Dim db As DAO.Database
Set db = Currentdb
With db.CreateQuerydef("")
    .Connect = db.Tabledefs("anyLinkTableNameToMSSQLNameHere").Connect
    .SQL = "exec Updata_UserPermissions 'userNameHere', 'deptNameHere'"
    .ReturnsRecords = False 'this will avoid error 3065
    .Execute dbFailOnError
End With
 

wvmitchell

New member
Local time
Today, 02:24
Joined
Sep 4, 2020
Messages
24
Hello,

I have spent several hours trying to figure this out but have not succeeded. So, I need help please!

I have a SQL stored procedure where it deletes a row from a table based on a Username and then inserts new rows for that Username based on the Username and Department that is entered:

DELETE FROM [dbo].[UserPermissions] where Userid = @user
INSERT INTO [dbo].[UserPermissions]
SELECT @user, Divisions.division FROM (Select division from tbl_Divisions where Department = @Dept) as Divisions;


I also created an Access form with two drop-down boxes for Userid and Department and captured the user selection in a couple of global variables. Then, put a command button on the form and tried several different things in the Click_Event to pass the parameters (through the global variables) to the pass-through query.

I also tried bypassing the stored procedure altogether and instead doing the Delete and Insert statements right in the Click_Event of the form. But, nothing worked. I could get the Delete statement working fine but the INSERT statement is throwing errors.

So, after a few hours of trial and error, I decided to lower my expectations and ditch the form and just try to make the pass-through query prompt me for the parameters. When I run the stored procedure in SQL, I get prompted for the parameters. So, I just want Access to prompt me as well but can't figure out how to do so. Here is the code in my Access pass-through:

Execute [OMBudget].[dbo].Update_UserPermissions UserName, Department

My SQL stored procedure is called, "Updata_UserPermissions".

Any help is much appreciated.

Thanks,
Setareh
You could create a new Access pass-thru query "qryUpdate_UserPermissions" with the following sql:
Code:
EXEC Update_UserPermissions '', ''

and then when you click the button, you run this VBA code to set up your parameters:
Code:
CurrentDb.QueryDefs("qryUpdate_UserPermissions").SQL = "EXEC Update_UserPermissions '" & UserName & "', '" & Department & "'"

and then you open (run) the pass-thru query to execute the sp.
 

Setare

New member
Local time
Today, 04:24
Joined
Aug 13, 2020
Messages
11
Good Morning,

Thank you for your suggestion. I don't have much experience with DAO or ADO coding in VBA. I added your code and changed the Connect string but it
throws this error. I can't figure out what is wrong with the Connect string.
1599573929081.png


Here is my Connect string:

With db.CreateQueryDef("")
.Connect = db.TableDefs("ODBC;DATABASE=OurDB;UID=test1;PWD=password;DSN=OMBudget Prod SQL04;").Connect
.sql = "exec Update_UserPermissions Username, Department"
.ReturnsRecords = False 'this will avoid error 3065
.Execute dbFailOnError
End With


Thanks,
Setareh
 

wvmitchell

New member
Local time
Today, 02:24
Joined
Sep 4, 2020
Messages
24
Good Morning,

Thank you for your suggestion. I don't have much experience with DAO or ADO coding in VBA. I added your code and changed the Connect string but it
throws this error. I can't figure out what is wrong with the Connect string.
View attachment 84844

Here is my Connect string:

With db.CreateQueryDef("")
.Connect = db.TableDefs("ODBC;DATABASE=OurDB;UID=test1;PWD=password;DSN=OMBudget Prod SQL04;").Connect
.sql = "exec Update_UserPermissions Username, Department"
.ReturnsRecords = False 'this will avoid error 3065
.Execute dbFailOnError
End With


Thanks,
Setareh
Look at the replies from me and arnelgp - the sql statement needs to provide the value, not the name of the column.
this is incorrect:
"exec Update_UserPermissions Username, Department"
this is correct:
"exec Update_UserPermissions 'SomeUserName', 'SomeDepartmentName' "

Also, what was the reason you changed the connection string? You didn't mention having any problem with that in your original post.
 

Setare

New member
Local time
Today, 04:24
Joined
Aug 13, 2020
Messages
11
Good Morning,

Thank you for your suggestion. It worked! And, it was a lot simpler than I thought (of course thanks to you who knew what to do). My form's command button now works the way I had envisioned it and I am happy!

Have a great day!
Setare
 

Setare

New member
Local time
Today, 04:24
Joined
Aug 13, 2020
Messages
11
Thanks, got it working!

Thank you guys for your help. Much appreciated! This forum is a gem indeed!
 

TheSearcher

Registered User.
Local time
Today, 05:24
Joined
Jul 21, 2011
Messages
304
you Can pass the parameter:

Code:
Dim db As DAO.Database
Set db = Currentdb
With db.CreateQuerydef("")
    .Connect = db.Tabledefs("anyLinkTableNameToMSSQLNameHere").Connect
    .SQL = "exec Updata_UserPermissions 'userNameHere', 'deptNameHere'"
    .ReturnsRecords = False 'this will avoid error 3065
    .Execute dbFailOnError
End With
arnelgp - I used your code as a guide for my code below. I'm getting an "ODBC - Call Failed" error. Can you see anything wrong? tbl_Time is a linked table to my Sqlserver database. Do I need a special library?

Code:
Public Sub Create_SqlUser(NewUser As String)

Dim db As DAO.Database
Set db = CurrentDb
With db.CreateQueryDef("")
    .Connect = db.TableDefs("tbl_Time").Connect
    .sql = "exec sp_CreateLoginUserPermissions_BT @User '" & NewUser & "'"
    .ReturnsRecords = False 'this will avoid error 3065
    .Execute dbFailOnError
End With

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:24
Joined
May 7, 2009
Messages
19,171
I'm getting an "ODBC - Call Failed" error
do you have sp_CreateLoginUserPermissions_BT Stored procedure on your mssql?
i can only see sp_adduser?
 

TheSearcher

Registered User.
Local time
Today, 05:24
Joined
Jul 21, 2011
Messages
304
The stored procedure exists in my SqlServer database. I'm not sure what you mean by "mssql".
Where do you see sp_AddUser?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:24
Joined
May 7, 2009
Messages
19,171
is sp_CreateLoginUserPermissions_BT, user-defined, 'coz i can't find it in my SQLExpress:
sp_create.png

this is adduser:
sp_adduser.png
 

GPGeorge

Grover Park George
Local time
Today, 02:24
Joined
Nov 25, 2004
Messages
1,776
is sp_CreateLoginUserPermissions_BT, user-defined, 'coz i can't find it in my SQLExpress:
View attachment 107438
this is adduser:
View attachment 107439
It has to be a User Defined stored proc. We can tell that from the absence of anything with that name under System stored procs, plus the suffix is a clue.

sp_CreateLoginUserPermissions_BT

My guess is that the problem is in the Connection. The code posted is trying to retrieve the connection information from a linked tables connect property. That may or may not work, but I don't think I've seen that approach for a linked SQL Server table.

Also, it may be that the user trying to use this stored proc to create permissons for a new user login needs elevated privileges from those in that table's connection.

I'd recommend using debug.print to send the connection retrieved by this line:

.Connect = db.TableDefs("tbl_Time").Connect

to the immediate window so you can verify what it actually contains.
 

cheekybuddha

AWF VIP
Local time
Today, 09:24
Joined
Jul 21, 2014
Messages
2,237
This line:
Code:
' ...
    .sql = "exec sp_CreateLoginUserPermissions_BT @User '" & NewUser & "'"
' ...
should be either:
Code:
' ...
    .sql = "exec sp_CreateLoginUserPermissions_BT '" & NewUser & "'"
' ...
or:
Code:
' ...
    .sql = "exec sp_CreateLoginUserPermissions_BT @User:='" & NewUser & "'"
' ...
 

cheekybuddha

AWF VIP
Local time
Today, 09:24
Joined
Jul 21, 2014
Messages
2,237
Ah yes, sorry, I messed up the syntax for the second version. :oops:

I think it should be:
Code:
' ...
    .sql = "exec sp_CreateLoginUserPermissions_BT @User='" & NewUser & "'"
' ...

It's VBA that uses the := for named parameters - T-SQL simply uses = for that assignment 😬

Anyway, glad you got it working! (y)
 

Users who are viewing this thread

Top Bottom