Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #823

Re: Pass a vba variable to a SQL Server stored procedure

Show all headers | View raw

On Apr 5, 2:37 pm, Monica <monicaro...@yahoo.com> wrote:
> 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=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

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.databases.ms-access/msg/74b51435a18d7540

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@FortuneJames.com

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Pass a vba variable to a SQL Server stored procedure Monica <monicaroman@yahoo.com> - 2011-04-05 11:37 -0700
  Re: Pass a vba variable to a SQL Server stored procedure "James A. Fortune" <CDMAPoster@FortuneJames.com> - 2011-04-05 12:24 -0700
  Re: Pass a vba variable to a SQL Server stored procedure "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-04-05 15:33 -0400
  Re: Pass a vba variable to a SQL Server stored procedure "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-04-05 15:33 -0400

csiph-web