Need Help with Work around: Updatable Recordset on Access Form , sql server 2000

Need Help with Work around: Updatable Recordset on Access Form , sql server 2000

Post by Dray » Sat, 22 Mar 2003 06:59:37



Currently I am trying to pull records from a stored procedure using
parameters (I've tested simple tables trying to create updatable
form/recordsets as well with no success however) down to a continuous
form in an access 2000 project.  I've succeeded in pulling the records
down however they are not updatable (from the form).  If I use a
Server Side cursor, then the method "Set Me.Recordset = objRst" Causes
the error "The Object You entered is not a valid Recordset Property".
If I set the cursor to Client side then set my form to
me.Recordset=ObjRst, the records are displayed in continuous fashion
as they should, however if I type into the form nothing happens except
a message that says "The Recordset is not Updatable".

I decided to try to make a work around by using the client side
cursor, then adding additional text boxes to the continuous form,
while hiding the actual recordset text boxes.  Then I set Each text
box value equal to a recordset text box value.  The result:  the first
record values are shown in the  non-recordset text boxes, (in the
detail section)  of the continuous form and I can actually type in the
box (which would allow me to execute an update to the database as soon
as the text box looses focus).  But, the rest of the record detail
sections on the form also show the first record data , even though the
text boxes that are bound to the recordset are showing the correct
record data.  I was hoping that the two sets of text boxes would be in
sync.

My goal is to pull the recordset down from a parameterized (multiple
table joined) stored procedure (Using the Contains keyword in the
where clause because the records will be searched using keywords typed
into a form field) into an .adp form so a person may tab between
records and record fields while making changes.  The changes need to
be saved automatically as the textbox looses focus.

The code I am using:

Private Sub Form_Open(Cancel As Integer)

Dim vStartdate, vStartTime, vSearchString As String

   vStartdate = "12/22/1999"
   vStartTime = "12:00:00"
    vSearchString = "Crystal"

Set con = New ADODB.Connection
With con
    .Provider = "MSDataShape"
    .ConnectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Initial Catalog=MyDatabase;Data
Source=MyServer\MyServerInstance"
    .CursorLocation = adUseServer
'    .CursorLocation = adUseClient
    .Open
End With

  Set cmd = New ADODB.Command
    with cmd
     .ActiveConnection = con
     .CommandText = "STP_Search"
     .CommandType = adCmdStoredProc
     .Parameters(1).Value = vSearchString
     .Parameters(2).Value = vStartdate
     .Parameters(3).Value = vStartTime
   end with

    Set objRst = New ADODB.Recordset
    With objRst
    '  .CursorLocation = adUseServer
       .CursorLocation = adUseClient
        .ActiveConnection = con
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
    End With
    objRst.Open cmd
    Set Me.Recordset = objRst
    Me.UniqueTable = "tbl_TableToEdit"
ENd Sub

I've managed to pull down and edit in a form a SqlServer View, however
can you use the CONTAINS and other keywords in a View (I am using this
to search for keywords and keyword variations such as "cry*"?  Can you
pass parameters to a view?

I've been working on this for 3 days straight.  Some Expert advice
will be GREATLY appreciated.
BTW, I am using ADO 2.7, Access 2000 SP-3, Sql Server 2000,  Windows
Server 2000 SP-3

 
 
 

Need Help with Work around: Updatable Recordset on Access Form , sql server 2000

Post by Guy Horto » Fri, 28 Mar 2003 11:53:24


Draya,

Assuming that your stored procedure select statement satisfys normal update
rules (ie  No aggregations, computed fields, many to many joins...) then try
the following:

1) You must include all the primary keys of all joined tables in the Select
statement.
2) You must have full access to update the underlying table.
3) Generally you need to use a Resync command with Stored Procedure based
forms

Hope this helps
Guy


Quote:> Currently I am trying to pull records from a stored procedure using
> parameters (I've tested simple tables trying to create updatable
> form/recordsets as well with no success however) down to a continuous
> form in an access 2000 project.  I've succeeded in pulling the records
> down however they are not updatable (from the form).  If I use a
> Server Side cursor, then the method "Set Me.Recordset = objRst" Causes
> the error "The Object You entered is not a valid Recordset Property".
> If I set the cursor to Client side then set my form to
> me.Recordset=ObjRst, the records are displayed in continuous fashion
> as they should, however if I type into the form nothing happens except
> a message that says "The Recordset is not Updatable".

> I decided to try to make a work around by using the client side
> cursor, then adding additional text boxes to the continuous form,
> while hiding the actual recordset text boxes.  Then I set Each text
> box value equal to a recordset text box value.  The result:  the first
> record values are shown in the  non-recordset text boxes, (in the
> detail section)  of the continuous form and I can actually type in the
> box (which would allow me to execute an update to the database as soon
> as the text box looses focus).  But, the rest of the record detail
> sections on the form also show the first record data , even though the
> text boxes that are bound to the recordset are showing the correct
> record data.  I was hoping that the two sets of text boxes would be in
> sync.

> My goal is to pull the recordset down from a parameterized (multiple
> table joined) stored procedure (Using the Contains keyword in the
> where clause because the records will be searched using keywords typed
> into a form field) into an .adp form so a person may tab between
> records and record fields while making changes.  The changes need to
> be saved automatically as the textbox looses focus.

> The code I am using:

> Private Sub Form_Open(Cancel As Integer)

> Dim vStartdate, vStartTime, vSearchString As String

>    vStartdate = "12/22/1999"
>    vStartTime = "12:00:00"
>     vSearchString = "Crystal"

> Set con = New ADODB.Connection
> With con
>     .Provider = "MSDataShape"
>     .ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Initial Catalog=MyDatabase;Data
> Source=MyServer\MyServerInstance"
>     .CursorLocation = adUseServer
> '    .CursorLocation = adUseClient
>     .Open
> End With

>   Set cmd = New ADODB.Command
>     with cmd
>      .ActiveConnection = con
>      .CommandText = "STP_Search"
>      .CommandType = adCmdStoredProc
>      .Parameters(1).Value = vSearchString
>      .Parameters(2).Value = vStartdate
>      .Parameters(3).Value = vStartTime
>    end with

>     Set objRst = New ADODB.Recordset
>     With objRst
>     '  .CursorLocation = adUseServer
>        .CursorLocation = adUseClient
>         .ActiveConnection = con
>         .CursorType = adOpenKeyset
>         .LockType = adLockOptimistic
>     End With
>     objRst.Open cmd
>     Set Me.Recordset = objRst
>     Me.UniqueTable = "tbl_TableToEdit"
> ENd Sub

> I've managed to pull down and edit in a form a SqlServer View, however
> can you use the CONTAINS and other keywords in a View (I am using this
> to search for keywords and keyword variations such as "cry*"?  Can you
> pass parameters to a view?

> I've been working on this for 3 days straight.  Some Expert advice
> will be GREATLY appreciated.
> BTW, I am using ADO 2.7, Access 2000 SP-3, Sql Server 2000,  Windows
> Server 2000 SP-3