Calling procedure via ODBC

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

Guest

Is there a way to call a procedure from within Access when accessing the
database via ODBC?

My Access application uses ODBC to connect to a DB2 database. This works
fine for extracting data and for some update/append statements. However, a
table to which I need to append records has a primary key that is normally
generated by a procedure within the DB2 database. I tried incrementing using
a DMax(PrimaryKey) +1; this worked for me but did not increment the sequence
generator, so the next record created natively by a user within the DB2
environment tried to use the same ID that I had just created.

I know that Crystal Reports is able to call procedures because some of the
reporting from this same DB2 db uses procedures to pre-populate fields when a
particular Crystal Report is run. Is it possible in Access?
 
You should be able to call the procedure on the server side.

Just make a pass-through query in ms-access.

Execute sp_yourProceure

Get the above working, and I assume when it runs, you will see the "next"
value returned.

Now, in code you can go:

strNextID = currentdb.OpenRecordSet("AboveSavedPassThrouQery")(0)
 
Thanks. That got me to the right place. In this case, Execute kept giving
me
"unexpected token" errors, so I used an alternate Select statement that
performs the same function as the procedure - incrementing & returning the
value -given to me by the DB2 programmer.
Click to expand...

Good. In fact, remember that a pass through can send ANY command. So,
whatever that developer types at the command prompt to run that procedure
can (normally) be used as the text in the pass-through. So, you can even any
non sql command in the query, and stuff like table copy commands, and even
permissions commands can be sent to the server side.
 
Back
Top