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--it
didn't update. 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=databas ename;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

On Apr 5, 2:37*pm, Monica <monicaro... (AT) yahoo (DOT) com> wrote:
Perhaps using ADODB will work better with a connection to SQL Server.
Here's some 2000 vbScript I posted in 2004 for connecting to SQL
Server 6.5 from:

http://groups.google.com/group/comp....b51435a18d7540

Dim connBackend As ADODB.Connection 'ADODB connection
Dim strConn As String
Dim cmdGo As ADODB.Command 'ADODB.Command
Dim iCmdStoredProc As Integer
Dim prmName As ADODB.Parameter 'ADODB.Parameter

Set MyDB = CurrentDb
Set connBackend = New ADODB.Connection
strConn = "Driver={SQL Server}; Network Library=DBMSSOCN;
DataSource=MySource; Uid=MyUid; Pwd=MyPassword;"
connBackend.Open strConn
'Eventually we'll want to use a stored procedure to return the results
'Set cmdGo = Server.CreateObject("ADODB.Command")
'Set cmdGo.ActiveConnection = connBackend
'cmdGo.CommandText = "SP_GetLevel0ID"
iCmdStoredProc = 4
'cmdGo.CommandType = iCmdStoredProc
'prmName.Value = "Chemical, Fluids and Lubricants"
'prmName.Value = cbxLevel0Pick.Text
'cmdGo.Parameters.Append prmName

Try using something patterned after the above using the same
connection string you already have with the PayrollNo as a parameter.
Note: Make sure you have a Reference to ADODB. If that works, you
might be able to go back to using DAO and get that to work also.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

Monica wrote:
es"
You cannot debug a sql statement without knowing what it is. Add

debug.print .SQL

to your code at this point so you can see the resulting sql statement in the
Immediate window after running your code. Alternatively, set a breakpoint
and step through the code.

If you have built it correctly, you should be able to copy-paste it from the
Immediate window into a passthrough query's SQL View and run it without
modification. (I suspect your problem is that Me.PayrollNo does not contain
what you expect it to contain).

Monica wrote:
es"
You cannot debug a sql statement without knowing what it is. Add

debug.print .SQL

to your code at this point so you can see the resulting sql statement in the
Immediate window after running your code. Alternatively, set a breakpoint
and step through the code.

If you have built it correctly, you should be able to copy-paste it from the
Immediate window into a passthrough query's SQL View and run it without
modification. (I suspect your problem is that Me.PayrollNo does not contain
what you expect it to contain).