Groups | Search | Server Info | Login | Register

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

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

Show all headers | View raw

Monica 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=Y
es"
> .SQL = "dbo.UpdateHRPayroll '" & Me.PayrollNo & "';"

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).

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