Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: External Stored Procedure and DataSheets

    If anyone could help, it would be very appreciated.

    I am trying to call a MS SQL Server stored procedure from MS Access (via ODBC). We are trying to not change our existing form, which uses a sub-form (datasheet would be acceptable). The code used to bind the data in the form with DataSource, but I can not do this with a stored proc. I understand that I can update the RecordSet, which gives me the correct number of rows in the datasheet, but I can not figure out how and where to bind the recordset fields with the datasheet fields. Alternatively, is there a way to spin through the datasheet rows and manually set the data. I'm a C++/VB developer and not too familiar with MS Access. Would have been easier for me to use a Far Point Spread control, but trying to not rip apart the users form.

    Thaniks in advance.
    Scott

  2. #2
    Join Date
    Sep 2003
    Location
    Lancashire, UK
    Posts
    22
    I'm a bit unsure what you are trying to do, but it sounds like you want to attach data returned from a stored proc to a form. If so then this should help.
    Create a new query. From the Query menu select 'SQL specific', then select 'Pass-through'. View the query properties. In the ODBC property enter the connection details. Set the 'Return records' property to yes.
    Enter the code in the query design window (I assume something like 'EXEC sp_name parameters'). Save the query, then in the form design set the DataSource = query name.

    Hope this is what you are trying to do, but the post is a bit vague

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm not sure exactly what you're looking for here.. basically are you saying you ARE able to pull the required data into a usable recordset but are unable to bind the controls? If you're looking only for a datasheet, which does not actually need bound controls, you can simply assign the RecordSource to your recordset. Eg:

    Depending on the number of fields, you may hard code all of the bindings. eg:

    Dim frm As Form

    Set frm = Forms!yourForm

    frm!namedctrl1.controlSource = rs(FirstField)
    frm!ctrl2.ControlSource = rs(SecondField)

    etc...

    Or you could iterate using indexes:

    for i = frm.controls.count - 1
    frm.controls(i).ControlSource = rs.fields(i)
    next i
    Last edited by Teddy; 03-15-04 at 12:01.

  4. #4
    Join Date
    Mar 2004
    Posts
    3
    That was my first attempt, but a pass-through procedure does not allow me to pass my stored-procedure arguments unless I could hard code them. The pass-through does not allow variables as arguments. That is why I am using VB code to create the recordset. Now I need to attach this recordset to the datasheet and bind the fields.

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    After trying this, I get the same result. Maybe my problem is with the detail section of the datasheet. Do I need to have 1 field for every column returned in the recordset? There currently in not a 1 to 1. In fact, I really want to show only a subset of the columns returned in the recordset. To be honest, I really do not have a full grasp of datasheets and how they work. (bet you couldn't tell - haha).

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by aldensc
    After trying this, I get the same result. Maybe my problem is with the detail section of the datasheet. Do I need to have 1 field for every column returned in the recordset? There currently in not a 1 to 1. In fact, I really want to show only a subset of the columns returned in the recordset. To be honest, I really do not have a full grasp of datasheets and how they work. (bet you couldn't tell - haha).
    You are right on the first count, you need one text box for each column you would like to display. As you would only like to display a certian subset, I would recommend using the explicitly defined code above.

Posting Permissions

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