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

Setare

New member
Local time
Today, 17:30
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
Tomorrow, 10:30
Joined
Jan 20, 2009
Messages
12,110
An Access Pass Through Query cannot take parameters. You need to use VBA to edit the SQL property of the querydef.
 

arnelgp

error reading drive A:
Local time
Tomorrow, 07:30
Joined
May 7, 2009
Messages
10,858
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, 16:30
Joined
Sep 4, 2020
Messages
9
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, 17:30
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, 16:30
Joined
Sep 4, 2020
Messages
9
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, 17:30
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, 17:30
Joined
Aug 13, 2020
Messages
11
Thanks, got it working!

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

Users who are viewing this thread

Top Bottom