Pass a variable from vba to a sql stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Pass a variable from vba to a sql stored procedure

Author

Topic

monicaroman
Starting Member

5 Posts

Posted - 2011-04-05 : 13:47:05

I have an access form where I capture a payroll code (PayrollNo). I want to be able to click the button and have access run a stored procedure in SQL Server to update the payroll field with the code in the access form, where the EmpNo (id) = the EmpNo on the table with the list of current employee's payroll. My code did not work. What did I do wrong? Thanks so much!

Here is the VBA code on the button:

Private Sub UpdPRNo_Click()

Dim mydb As DAO.Database
Dim qdf As DAO.QueryDef
Set mydb = CurrentDb
Set qdf = mydb.CreateQueryDef("qryUpdatePayroll")

With qdf
.Connect = "ODBC;DSN=servername;UID=username;DATABASE=databasename;Trusted_Connection=Yes"
.SQL = "dbo.UpdateHRPayroll '" & Me.PayrollNo & "';"
.ReturnsRecords = False
.Execute
.Close
End With

CurrentDb.QueryDefs.Delete (qdf.Name)

End Sub

This is the Stored procedure

CREATE PROCEDURE [dbo].[UpdateHRPayroll]
(@PayrollNo NVARCHAR(10))

AS
BEGIN
ALTER TABLE dbo.Employees
Disable TRIGGER tr_TIMESTAMP_UPDATE
Update dbo.Employees
SET dbo.Employees.Payperiod = @PayrollNo
FROM dbo.Employees INNER JOIN
dbo.Temp_Payroll ON
dbo.Employees.EmpNo = dbo.TempPayroll.EmpNo
ALTER TABLE dbo.Employees

Truncate Table dbo.Temp_Payroll
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-05 : 13:59:28

You'll need to provide more detail on "my code did not work". Is there an error message?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

monicaroman
Starting Member

5 Posts

Posted - 2011-04-05 : 14:19:24

Hi, thanks for responding Tara.
No, no error message, it just doesn't update. I thought perhaps I did something wrong on the code.
The connection string works, I know this because I use the same connection string on another query that works.

Any ideas?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 14:24:56

Are you sure there's something in Temp_Payroll when the sproc runs?

Jim

Everyday I learn something that somebody else already knew

monicaroman
Starting Member

5 Posts

Posted - 2011-04-05 : 14:38:46

Hi Jim, yes there are records on Temp_Payroll.
So the code looks fine then?
I'm soooo stumpted!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-05 : 14:56:35

Well, your sproc is missing the enable trigger part.

Could you post what the .SQL looks like when you go to run it?

Jim

Everyday I learn something that somebody else already knew

monicaroman
Starting Member

5 Posts

Posted - 2011-04-05 : 15:28:48

good call on the trigger ;-)....it is there though, but it has nothing to do with the update so I took it out.
Not sure what you mean by what the .SQL looks like when I go to run it..?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-04-05 : 16:50:57

Does this return anything if you execute it in Query Analyzer?

SELECT	e.*
FROM	dbo.Employees e
JOIN	dbo.Temp_Payroll p
ON	e.EmpNo = p.EmpNo

Wow, using DAO, huh? Why?

monicaroman
Starting Member

5 Posts

Posted - 2011-04-06 : 11:26:38

Russel, tried that, the data is all there.
Using DAO because I read somewhere that it would be faster, I'm only communicating to SQL Server. Not really so well versed in those protocols.

Still hoping for someone to find the magic answer!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-04-06 : 14:18:17

does the stored proc work in query analyzer?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources