db.Execute sql with reference to stored procedure

Question

I've started to use CurrentDb.Execute to handle all my queries because it seams to most usable. The problem which I encountered when switching from DoCmd.RunSQL to CurrentDb.Execute is that references to stored procedures inside the SQL string isn't working.

UPDATE tbl SET x = 1 WHERE id IN (SELECT id FROM [MyProc])

Does it need to be evaluated somehow, or is there something else that is wrong?

Is this pure MS Access or are you referencing another DB? In MS Access, the example above should work. You might like to post the full sql. If, for example, you are referencing a form, you may run into difficulties. — Fionnuala
– Fionnuala, Commented Jan 20, 2014 at 16:51
Not sure about your question. But the database is in a file on the local machine. Running the query it tells me there is insufficient parameters. And hence the only "special" reference i'm doing is to a stored procedure using []-brackets. — Mille
– Mille, Commented Jan 20, 2014 at 17:38
Ahh... there it was. Thank you very much. Is there a better way to evaluate the form data then Eval("Forms!Name!Box")? — Mille
– Mille, Commented Jan 20, 2014 at 17:49

Community · Accepted Answer · 2017-05-23 10:31:40Z

1

In VBA you can to refer to the form like so:

ssql = "SELECT * FROM tbl2 WHERE " & Me.Are & "=tbl2.col"

So MyProc can either be dropped or rewritten.

(See also UPDATE query based on a saved SELECT query in Access? )

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 21, 2014 at 14:53

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

db.Execute sql with reference to stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related