Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Posts
    31

    Unanswered: Accessing stored procedures on SQL Server

    I have an Access DB which is a front-end to an SQL Server database. On the database there is a stored procedure that the user should be able to run by clicking a button, this will also bring up two inputboxes which will take parameters to pass to the stored procedure.

    At the moment the connection to the database is made through linking the tables using File | Get External Data | Link Tables and uses an ODBC connection.

    Does anyone know the best way to do this?

    Thanks,
    Paul

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You can use the adodb.command object to pass these parameters to the stored procedure. Will the stored procedure return anything ?

  3. #3
    Join Date
    Mar 2002
    Posts
    31
    No, it just starts a transaction and moves a few records around. Is there a way I can get it to execute the command on the ODBC connection that the rest of the database uses? I don't want to have to code in another connection string etc. in case the server is changed (which is reasonably likely).

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Are you using the adodb.connection object for your odbc connection ?

  5. #5
    Join Date
    Mar 2002
    Posts
    31
    I don't think so, I've just used File | Get External Data | Link tables. This then brought up an open file dialog so I changed it to select an ODBC connection, and then chose the System DSN I'd created.

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    What are you using the linked table for ? Are you linking back to the sql server database ? What does your front end need from the sql server database ?

  7. #7
    Join Date
    Mar 2002
    Posts
    31
    The Access database provides a front-end to the data, i.e. allowing reports to be run, and providing forms for entering and updating data. The linked tables are used to store the data, so that data is held centrally and users can share it.

    The linked tables are therefore a direct drop-in replacement for the tables that would exist if it were a regular Access database.

    I need to be able to execute a stored procedure against the same database (preferably using the same connection as the linked tables) thus avoiding having to do too much more to setup connections etc.

    Thanks,
    Paul

  8. #8
    Join Date
    Jul 2002
    Posts
    67
    Have you done any research on MSDN regarding this issue?


    MSDN
    Don't do today, what can be done tomorrow.

  9. #9
    Join Date
    Mar 2002
    Posts
    31
    I have, but haven't found anything of any use.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •