Run Stored Procedure from SQL Database

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

Guest

Hi,

I'm using MS Access 2003.

My work colleague created a stored procedure from a SQL Database. Let's say
it's called "EpisodeData". She said it accepts two parameters: a string and a
date. The string can be up to 7 characters long and the date is in the format
"yyyy-mm-dd hh:mm" which then returns relevant episode data. I have yet to
know what data it returns.

Since this is my first encounter with stored procedures, how do I use this
in my Access database? Of-course I could ask her about it, but I would like
to know from you guys.

Thanks for any help I can get.
 
Create a new query and don't select any table(s) for it.

Switch to the SQL View (under the View menu) and type

Call EpisodeData 'abcd', '2007-04-20 12:30'

(replace abcd with whatever string you want, and put whatever date you
want...)

Make sure that the query's Properties are visible (look under the View menu
if they're not), and from the menu, choose Query | SQL Specific |
Pass-Through. You'll see a new ODCB Connect Str property: click on the
ellipsis to the right of the property (the ...) to select a data source, or
type a valid DSN-less connection string in the box. (Note that you must use
ODBC: you cannot use the OleDB provider)

Note that since this is a pass-through query, you are unable to have the
query recognize parameters. In other words, you can't have it pick up the
string and date from controls on a form, or prompt the user to input them
when you run the query. Your only option is to dynamically regenerate the
SQL.
 
Thank you for the quick response and advise. Learning new things in Access
and seeing it work, however simple, is so gratifying and makes you want to
learn more. I've learned that querydef is the key to generate dynamic SQL
with stored procedures, but I have yet to try it.

No doubt, I shall be back to ask a few more.
 
Back
Top