Under the UPDATE gun

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We're migrating to a SQL back-end Access project. But our update queries don't work anymore. The original SQL syntax:

UPDATE dbo.tblRandom
SET Foo = 1, FooDate = frmFoo.FooishDate
WHERE (FooStatus <> 'Oof') AND (CommitID = frmFoo.FooID)

was ported over to a stored procedure but it gives an ADO error about the column prefix 'frmFoo not matching with a table name or alias name used in the query.

So clearly Update queries don't work the same way. How can an Access project newbie reference a value on a form from within an update stored procedure?
 
So clearly Update queries don't work the same way. How can an
Access project newbie reference a value on a form from within an
update stored procedure?
Click to expand...

The stored procedure will be executed on the remote server. And on the
server there is no form, it only exists on the front end PC.

If you want to put the value from a form into a stored procedure, you have
to make the procedure accept parameters. Just as a VB function can have
parameters, so can a stored procedure. Then if you call the stored
procedure, you have to hand over the value from the form.

Michael G. Schneider
 
Currently the code behind foo_Click() is

Private Sub foo_Click()

Dim stDocName As String

stDocName = "qryUpdateFoo"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Me.Refresh

End Sub

Where qryUpdateFoo obviously has the SQL update code. I guess I'm not understanding how I'll PASS THE VALUES from frmFoo to the stored procedure USING THIS SYNTAX. I found a mention of a DoCmd.RunSQL where it looks like I could just have the stored procedure's SQL syntax running right within the click event, but I'd prefer to keep the changes to the form's events as minimal as possible.
 
I guess I'm not understanding how I'll PASS THE VALUES
from frmFoo to the stored procedure USING THIS SYNTAX.
Click to expand...

I suspect that we are talking about an Access ADP project. Suppose the code
is in the form context, where an textbox named "txtAge" exists, and a stored
procedure named "SP" exists on SQL Server, taking one parameter @age. Then
inside the form you might code...

Dim lAge as Long
lAge = Me.txtAge.Value
Application.CurrentProject.Connection.Execute "dbo.SP(" & lAge & ")"

for calling the stored procedure.

Michael G. Schneider
 
Yes, this is half the solution; you will have to modify
your SP as follows: (replace ProcName with your SP's
name - no brackets)

ALTER PROCEDURE <ProcName> (@nAge Integer, @dFooishDate
Datetime)
AS
UPDATE dbo.tblRandom
SET Foo = 1, FooDate = @dFooishDate
WHERE (FooStatus <> 'Oof') AND (CommitID = @nAge)

GO

Then call this stored procedure as outlined below
-----Original Message-----


I suspect that we are talking about an Access ADP project. Suppose the code
is in the form context, where an textbox named "txtAge" exists, and a stored
procedure named "SP" exists on SQL Server, taking one parameter @age. Then
inside the form you might code...

Dim lAge as Long
Click to expand...
Dim dFooishDate as Date

dFooishDate =frmFoo.FooishDate
lAge = Me.txtAge.Value
Application.CurrentProject.Connection.Execute "dbo.SP
Click to expand...
(" & lAge & ",'" & dFooishDate & "')"
 
Back
Top