Best way to perform series of updates

[mrlddst]

I'm using Access 2007, accdb format.

I have been tasked with adding a script that used to be run against an Oracle table to now run this in Access. I have the table linked inside of the Access database. The script runs around 40 updates on two fields depending on other column values. Can I call a procedure in Oracle to update the table from Access, or if I use a function as a module, I could only call it for one field correct? I would have the same problem switch, but I probably won't go that route as it would extremely long.

Script example
UPDATE PPA_JAN14_TEST
SET PPA_OE_COLUMN = NULL,
PPA_OE = NULL;
COMMIT;
UPDATE PPA_JAN14_TEST
SET
PPA_OE_COLUMN = '1',
PPA_OE = 'AFC-10'
WHERE substr(rgn_dist,1,1) = '2'
AND approp = 'XXX'
AND AFC = 'X1'


AND PPA_OE_COLUMN IS NULL;
COMMIT;
UPDATE CG_TIER_PPA_JAN14_TEST
SET PPA_OE_COLUMN = '1',
PPA_OE = 'AFC-10'
WHERE substr(rgn_dist,1,1) = '2'
AND approp = 'XXX'
AND AFC = 'X2'
AND PPA_OE_COLUMN IS NULL;
COMMIT;
and so on.........

[pbaldy]

The data is still in Oracle, with linked tables in Access? I've only worked with SQL Server, but presumably Oracle has similar capabilities. If so, yes you can run an Oracle procedure from Access. You can either use a pass through query or an ADO command object. You could also run a pass through query with that SQL in it. If you want to switch to Access, each UPDATE would have to be executed separately, as Access does not allow multiple statements in one query.

[mrlddst]

Thanks! I will try the passthough first.

[pbaldy]

No problem, post back if you get stuck (though my lack of Oracle experience may become a factor). For SQL Server, the pass through to run a stored procedure on the server would simply be:

EXEC ProcedureName

if there were no parameters.

[mrlddst]

Quick question, I haven't set up the pass through yet but if I set this as the first step in a macro that runs make tables (I want to run them after the updates). Do you think the macro won't work properly since it's not waiting on Oracle to finish?

[pbaldy]

It's possible, though that should run pretty quick in Oracle. You may be able to have the Oracle process return a value and test for it. Or if you use VBA you can add a pause to wait x seconds (not sure if that's doable in a macro).

[mrlddst]

Just wanted to update you. I was able to do the passthrough and attach it to a macro. The macro does wait for the procedure called in the query to finish before moving on to its next stage.

Oh yeah, with an Oracle PL/SQL passthrough, you must use a begin/end around your call.

Thanks again for you help.

[pbaldy]

Happy to help, and thanks for the update.