Results 1 to 7 of 7
  1. #1
    ronin256
    Guest

    stored proc w/ param as form data source (Access 2000 SR1)

    I'm trying to use a parameterized stored procedure as an updatable datasource for a form. When I try to set the Unique Table property, no selections are available and I cannot add one manually. There was a known issue that SR1 was supposed to fix but it still doesn't work. Any suggestions would be greatly appreciated.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    I assume you're using SQL Server, but 7.0/MSDE or 2000? And are you using an MDB or an ADP in Access?
    Charlotte

  3. #3
    ronin256
    Guest

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    This is an Access 2000 adp (tried both SR-1 and SR-2) against MS SQL Server 7.0 sp2.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    So are you trying to create the stored procedure from Access? If so, I'd recommend doing it from SQL Server instead. There are some things that are just way too hard in Access because you don't have all the tools that SQL Server provides.
    Charlotte

  5. #5
    ronin256
    Guest

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    No, the stored procedure has already been created and works correctly on its own and as a read-only record source for the form. The problem is I can't set the Unique Table property in the Form's property sheet to make it an updateable record source, no tables show up to select. If I remove the parameter from this stored procedure ( hard code a value for the parameter inside the stored procedure for testing purposes) and use this modified version of the stored procedure for the form's record source then I am able to select a table in the Form's Unique Table property setting making this an updateable record source.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    Ah, I didn't quite understand what you were trying to do. It probably works when you remove the parameters because it becomes a View, and those are essentially tables and always return rows. Stored procedures have to be executed before they return rows and this one won't return rows until its parameters have been passed. Have you tried using code in the Open event of the form to open a recordset using the stored procedure, parameters and all, and then setting the recordset property of the form to the resulting recordset? At that point, you may be able to set the Unique Table property from code. At least it's worth a try. Just remember, you'll have to make sure it's a DAO recordset if your controls are bound to the fields, because otherwise, it won't be updateable no matter what you do.
    Charlotte

  7. #7
    ronin256
    Guest

    Re: stored proc w/ param as form data source (Access 2000 SR1)

    Thanks for your time Charlotte. I believe you are correct regarding Access having to execute the sp to determine the tables involved, this was mentioned in the kb article that discussed this problem and claimed it was fixed in SR-1. I've ended up going with a view and using the server filter property instead. This gives me an updateable recordset without having to load the DAO reference, which I believe I would have to do to use your suggestion.

Posting Permissions

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