Update DAO Recordset (sqlPassThrough)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Update DAO Recordset (sqlPassThrough)

  1. #1
    Julie Guest

    Update DAO Recordset (sqlPassThrough)


    I need to update a recordset that I retrieve from SQL Server. Right now I'm
    using the 'sqlPassThrough' type of recordset, and it's returning a non-updatable
    recordset. Does anyone know how to retrieve data from SQL Server, with VB,
    and be able to update this data using the EDIT method of the recordset?
    For instance, I want to be able to do this:

    MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    Set rs = Nothing
    Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    rs.Edit 'invalid operation
    rs.fields("MyNumber")= 200
    rs.Update

    I get an invalid operation error when I try to say rs.edit. That's because
    the recordset is non-updatable.

    I realize that I could just execute an update query--but that will not serve
    my purposes for a variety of reasons.

    Does anyone know?

    As info, 'mytable' does have a unique index.

    THANKS for your help!!!
    -Julie

  2. #2
    Craig Brown Guest

    Re: Update DAO Recordset (sqlPassThrough)


    Julie,

    I copied this out of the VB 6 help. I think, basically, your problem is
    in the combination of parameters you are using. Also, several other pieces
    of info point to the fact that one can only update dynasets with DAO.

    Craig Brown

    Copies the current record from an updatable Recordset object to the copy
    buffer for subsequent editing.

    Syntax

    recordset.Edit

    The recordset placeholder represents an open, updatable Recordset object
    that contains the record you want to edit.

    Remarks

    Once you use the Edit method, changes made to the current record's fields
    are copied to the copy buffer. After you make the desired changes to the
    record, use the Update method to save your changes.

    The current record remains current after you use Edit.

    Caution If you edit a record and then perform any operation that moves to
    another record, but without first using Update, your changes are lost without
    warning. In addition, if you close recordset or end the procedure which declares
    the Recordset or the parent Database or Connection object, your edited record
    is discarded without warning.

    Using Edit produces an error if:

    There is no current record.


    The Connection, Database, or Recordset object was opened as read-only.


    No fields in the record are updatable.


    The Database or Recordset was opened for exclusive use by another user (Microsoft
    Jet workspace).


    Another user has locked the page containing your record (Microsoft Jet workspace).

    In a Microsoft Jet workspace, when the Recordset object's LockEdits property
    setting is True (pessimistically locked) in a multiuser environment, the
    record remains locked from the time Edit is used until the update is complete.
    If the LockEdits property setting is False (optimistically locked), the record
    is locked and compared with the pre-edited record just before it's updated
    in the database. If the record has changed since you used the Edit method,
    the Update operation fails with a run-time error if you use OpenRecordset
    without specifying dbSeeChanges. By default, Microsoft Jet-connected ODBC
    and installable ISAM databases always use optimistic locking.

    In an ODBCDirect workspace, once you edit (and use Update to update) a recordís
    primary key field, you can no longer edit fields in that record until you
    close the Recordset, and then retrieve the record again in a subsequent query.

    Note To add, edit, or delete a record, there must be a unique index on the
    record in the underlying data source. If not, a "Permission denied" error
    will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet
    workspace, or an "Invalid argument" error will occur on the Update call in
    an ODBCDirect workspace.


    "Julie" <jcalcorn@up.com> wrote:
    >
    >I need to update a recordset that I retrieve from SQL Server. Right now

    I'm
    >using the 'sqlPassThrough' type of recordset, and it's returning a non-updatable
    >recordset. Does anyone know how to retrieve data from SQL Server, with

    VB,
    >and be able to update this data using the EDIT method of the recordset?


    >For instance, I want to be able to do this:
    >
    >MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    >Set rs = Nothing
    >Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    >rs.Edit 'invalid operation
    >rs.fields("MyNumber")= 200
    >rs.Update
    >
    >I get an invalid operation error when I try to say rs.edit. That's because
    >the recordset is non-updatable.
    >
    >I realize that I could just execute an update query--but that will not serve
    >my purposes for a variety of reasons.
    >
    >Does anyone know?
    >
    >As info, 'mytable' does have a unique index.
    >
    >THANKS for your help!!!
    >-Julie



  3. #3
    Julie Guest

    Re: Update DAO Recordset (sqlPassThrough)


    Craig,

    Thanks for the info. But do you know what the correct parameters are? I've
    tried every possible combination, and nothing works except the dbsqlpassthrough
    (since I'm passing the query to the actual SQL Server machine). Basically,
    I need parameters that will run a query against SQL Server data, and return
    a dynaset type recordset.

    Any ideas?

    THANKS!

    -Julie


    "Craig Brown" <BrownC3@ing-afs.com> wrote:
    >
    >Julie,
    >
    >I copied this out of the VB 6 help. I think, basically, your problem is
    >in the combination of parameters you are using. Also, several other pieces
    >of info point to the fact that one can only update dynasets with DAO.
    >
    >Craig Brown
    >
    >Copies the current record from an updatable Recordset object to the copy
    >buffer for subsequent editing.
    >
    >Syntax
    >
    >recordset.Edit
    >
    >The recordset placeholder represents an open, updatable Recordset object
    >that contains the record you want to edit.
    >
    >Remarks
    >
    >Once you use the Edit method, changes made to the current record's fields
    >are copied to the copy buffer. After you make the desired changes to the
    >record, use the Update method to save your changes.
    >
    >The current record remains current after you use Edit.
    >
    >Caution If you edit a record and then perform any operation that moves to
    >another record, but without first using Update, your changes are lost without
    >warning. In addition, if you close recordset or end the procedure which

    declares
    >the Recordset or the parent Database or Connection object, your edited record
    >is discarded without warning.
    >
    >Using Edit produces an error if:
    >
    >There is no current record.
    >
    >
    >The Connection, Database, or Recordset object was opened as read-only.
    >
    >
    >No fields in the record are updatable.
    >
    >
    >The Database or Recordset was opened for exclusive use by another user (Microsoft
    >Jet workspace).
    >
    >
    >Another user has locked the page containing your record (Microsoft Jet workspace).
    >
    >In a Microsoft Jet workspace, when the Recordset object's LockEdits property
    >setting is True (pessimistically locked) in a multiuser environment, the
    >record remains locked from the time Edit is used until the update is complete.
    >If the LockEdits property setting is False (optimistically locked), the

    record
    >is locked and compared with the pre-edited record just before it's updated
    >in the database. If the record has changed since you used the Edit method,
    >the Update operation fails with a run-time error if you use OpenRecordset
    >without specifying dbSeeChanges. By default, Microsoft Jet-connected ODBC
    >and installable ISAM databases always use optimistic locking.
    >
    >In an ODBCDirect workspace, once you edit (and use Update to update) a recordís
    >primary key field, you can no longer edit fields in that record until you
    >close the Recordset, and then retrieve the record again in a subsequent

    query.
    >
    >Note To add, edit, or delete a record, there must be a unique index on the
    >record in the underlying data source. If not, a "Permission denied" error
    >will occur on the AddNew, Delete, or Edit method call in a Microsoft Jet
    >workspace, or an "Invalid argument" error will occur on the Update call

    in
    >an ODBCDirect workspace.
    >
    >
    >"Julie" <jcalcorn@up.com> wrote:
    >>
    >>I need to update a recordset that I retrieve from SQL Server. Right now

    >I'm
    >>using the 'sqlPassThrough' type of recordset, and it's returning a non-updatable
    >>recordset. Does anyone know how to retrieve data from SQL Server, with

    >VB,
    >>and be able to update this data using the EDIT method of the recordset?

    >
    >>For instance, I want to be able to do this:
    >>
    >>MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    >>Set rs = Nothing
    >>Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    >>rs.Edit 'invalid operation
    >>rs.fields("MyNumber")= 200
    >>rs.Update
    >>
    >>I get an invalid operation error when I try to say rs.edit. That's because
    >>the recordset is non-updatable.
    >>
    >>I realize that I could just execute an update query--but that will not

    serve
    >>my purposes for a variety of reasons.
    >>
    >>Does anyone know?
    >>
    >>As info, 'mytable' does have a unique index.
    >>
    >>THANKS for your help!!!
    >>-Julie

    >



  4. #4
    Julie Guest

    Re: Update DAO Recordset (sqlPassThrough)


    Doug,

    Is there any other way to run against an ODBC database (Sql Server) besides
    using a passthrough query? (I realize I could open an Access db and link
    to my SQL Server tables, but I still don't think that recordset would be
    updatable, plus speed would probably be affected...)

    THANKS,

    Julie


    "Douglas J. Steele" <djsteele@canada.com> wrote:
    >AFAIK, the results of a pass-through query are never updatable.
    >
    >--
    >
    >Doug Steele, Microsoft Access MVP
    >Beer, Wine and Database Programming. What could be better?
    >Visit "Doug Steele's Beer and Programming Emporium"
    >http://I.Am/DougSteele/
    >
    >
    >"Julie" <jcalcorn@up.com> wrote in message news:3ab12f26$1@news.devx.com...
    >>
    >> I need to update a recordset that I retrieve from SQL Server. Right now

    >I'm
    >> using the 'sqlPassThrough' type of recordset, and it's returning a

    >non-updatable
    >> recordset. Does anyone know how to retrieve data from SQL Server, with

    >VB,
    >> and be able to update this data using the EDIT method of the recordset?
    >> For instance, I want to be able to do this:
    >>
    >> MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    >> Set rs = Nothing
    >> Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    >> rs.Edit 'invalid operation
    >> rs.fields("MyNumber")= 200
    >> rs.Update
    >>
    >> I get an invalid operation error when I try to say rs.edit. That's

    >because
    >> the recordset is non-updatable.
    >>
    >> I realize that I could just execute an update query--but that will not

    >serve
    >> my purposes for a variety of reasons.
    >>
    >> Does anyone know?
    >>
    >> As info, 'mytable' does have a unique index.
    >>
    >> THANKS for your help!!!
    >> -Julie

    >
    >



  5. #5
    Douglas J. Steele Guest

    Re: Update DAO Recordset (sqlPassThrough)

    AFAIK, the results of a pass-through query are never updatable.

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Julie" <jcalcorn@up.com> wrote in message news:3ab12f26$1@news.devx.com...
    >
    > I need to update a recordset that I retrieve from SQL Server. Right now

    I'm
    > using the 'sqlPassThrough' type of recordset, and it's returning a

    non-updatable
    > recordset. Does anyone know how to retrieve data from SQL Server, with

    VB,
    > and be able to update this data using the EDIT method of the recordset?
    > For instance, I want to be able to do this:
    >
    > MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    > Set rs = Nothing
    > Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    > rs.Edit 'invalid operation
    > rs.fields("MyNumber")= 200
    > rs.Update
    >
    > I get an invalid operation error when I try to say rs.edit. That's

    because
    > the recordset is non-updatable.
    >
    > I realize that I could just execute an update query--but that will not

    serve
    > my purposes for a variety of reasons.
    >
    > Does anyone know?
    >
    > As info, 'mytable' does have a unique index.
    >
    > THANKS for your help!!!
    > -Julie




  6. #6
    Craig Brown Guest

    Re: Update DAO Recordset (sqlPassThrough)


    Julie,

    I think Doug is correct about the SQL server dynasets. But I don't have
    a lot of experience with SQL Server, so I did not want to point you in the
    wrong direction

    Perhaps you can set up a stored procedure and execute that from within your
    program?

    Maybe you can use your dynaset to populate a string that is the update SQL
    and send that through ODBC. As far as I know, you can execute an SQL statement.

    Craig Brown

    "Julie" <jcalcorn@up.com> wrote:
    >
    >Doug,
    >
    >Is there any other way to run against an ODBC database (Sql Server) besides
    >using a passthrough query? (I realize I could open an Access db and link
    >to my SQL Server tables, but I still don't think that recordset would be
    >updatable, plus speed would probably be affected...)
    >
    >THANKS,
    >
    >Julie
    >
    >
    >"Douglas J. Steele" <djsteele@canada.com> wrote:
    >>AFAIK, the results of a pass-through query are never updatable.
    >>
    >>--
    >>
    >>Doug Steele, Microsoft Access MVP
    >>Beer, Wine and Database Programming. What could be better?
    >>Visit "Doug Steele's Beer and Programming Emporium"
    >>http://I.Am/DougSteele/
    >>
    >>
    >>"Julie" <jcalcorn@up.com> wrote in message news:3ab12f26$1@news.devx.com...
    >>>
    >>> I need to update a recordset that I retrieve from SQL Server. Right

    now
    >>I'm
    >>> using the 'sqlPassThrough' type of recordset, and it's returning a

    >>non-updatable
    >>> recordset. Does anyone know how to retrieve data from SQL Server, with

    >>VB,
    >>> and be able to update this data using the EDIT method of the recordset?
    >>> For instance, I want to be able to do this:
    >>>
    >>> MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    >>> Set rs = Nothing
    >>> Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    >>> rs.Edit 'invalid operation
    >>> rs.fields("MyNumber")= 200
    >>> rs.Update
    >>>
    >>> I get an invalid operation error when I try to say rs.edit. That's

    >>because
    >>> the recordset is non-updatable.
    >>>
    >>> I realize that I could just execute an update query--but that will not

    >>serve
    >>> my purposes for a variety of reasons.
    >>>
    >>> Does anyone know?
    >>>
    >>> As info, 'mytable' does have a unique index.
    >>>
    >>> THANKS for your help!!!
    >>> -Julie

    >>
    >>

    >



  7. #7
    Julie Guest

    Re: Update DAO Recordset (sqlPassThrough)


    Craig,

    Yes, I can execute an "UPDATE" statement via sqlpassthrough, but that is
    not acceptable to the users (for a variety of reasons that are quite long!).


    I'd like to be able to edit a recordset returned from SQL Server...But don't
    know if that's possible?!?

    THANKS,

    -Julie



    "Craig Brown" <BrownC3@ing-afs.com> wrote:
    >
    >Julie,
    >
    >I think Doug is correct about the SQL server dynasets. But I don't have
    >a lot of experience with SQL Server, so I did not want to point you in the
    >wrong direction
    >
    >Perhaps you can set up a stored procedure and execute that from within your
    >program?
    >
    >Maybe you can use your dynaset to populate a string that is the update SQL
    >and send that through ODBC. As far as I know, you can execute an SQL statement.
    >
    >Craig Brown
    >
    >"Julie" <jcalcorn@up.com> wrote:
    >>
    >>Doug,
    >>
    >>Is there any other way to run against an ODBC database (Sql Server) besides
    >>using a passthrough query? (I realize I could open an Access db and link
    >>to my SQL Server tables, but I still don't think that recordset would be
    >>updatable, plus speed would probably be affected...)
    >>
    >>THANKS,
    >>
    >>Julie
    >>
    >>
    >>"Douglas J. Steele" <djsteele@canada.com> wrote:
    >>>AFAIK, the results of a pass-through query are never updatable.
    >>>
    >>>--
    >>>
    >>>Doug Steele, Microsoft Access MVP
    >>>Beer, Wine and Database Programming. What could be better?
    >>>Visit "Doug Steele's Beer and Programming Emporium"
    >>>http://I.Am/DougSteele/
    >>>
    >>>
    >>>"Julie" <jcalcorn@up.com> wrote in message news:3ab12f26$1@news.devx.com...
    >>>>
    >>>> I need to update a recordset that I retrieve from SQL Server. Right

    >now
    >>>I'm
    >>>> using the 'sqlPassThrough' type of recordset, and it's returning a
    >>>non-updatable
    >>>> recordset. Does anyone know how to retrieve data from SQL Server, with
    >>>VB,
    >>>> and be able to update this data using the EDIT method of the recordset?
    >>>> For instance, I want to be able to do this:
    >>>>
    >>>> MySQL = "select mynumber, uniquefield from mytable where mynumber =

    20"
    >>>> Set rs = Nothing
    >>>> Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset, dbSQLPassThrough)
    >>>> rs.Edit 'invalid operation
    >>>> rs.fields("MyNumber")= 200
    >>>> rs.Update
    >>>>
    >>>> I get an invalid operation error when I try to say rs.edit. That's
    >>>because
    >>>> the recordset is non-updatable.
    >>>>
    >>>> I realize that I could just execute an update query--but that will not
    >>>serve
    >>>> my purposes for a variety of reasons.
    >>>>
    >>>> Does anyone know?
    >>>>
    >>>> As info, 'mytable' does have a unique index.
    >>>>
    >>>> THANKS for your help!!!
    >>>> -Julie
    >>>
    >>>

    >>

    >



  8. #8
    Douglas J. Steele Guest

    Re: Update DAO Recordset (sqlPassThrough)

    Access linked to SQL Server tables is updatable, provided you've got primary
    keys defined for all of the SQL Server tables.

    Yes, speed will usually be affected, especially if your pass-through queries
    are complicated, or do a lot of filtering.

    Don't understand, though, how your users would even know if you were running
    Update queries behind the scenes...

    --

    Doug Steele, Microsoft Access MVP
    Beer, Wine and Database Programming. What could be better?
    Visit "Doug Steele's Beer and Programming Emporium"
    http://I.Am/DougSteele/


    "Julie" <jcalcorn@up.com> wrote in message news:3ab23bcc$1@news.devx.com...
    >
    > Doug,
    >
    > Is there any other way to run against an ODBC database (Sql Server)

    besides
    > using a passthrough query? (I realize I could open an Access db and link
    > to my SQL Server tables, but I still don't think that recordset would be
    > updatable, plus speed would probably be affected...)
    >
    > THANKS,
    >
    > Julie
    >
    >
    > "Douglas J. Steele" <djsteele@canada.com> wrote:
    > >AFAIK, the results of a pass-through query are never updatable.
    > >
    > >--
    > >
    > >Doug Steele, Microsoft Access MVP
    > >Beer, Wine and Database Programming. What could be better?
    > >Visit "Doug Steele's Beer and Programming Emporium"
    > >http://I.Am/DougSteele/
    > >
    > >
    > >"Julie" <jcalcorn@up.com> wrote in message

    news:3ab12f26$1@news.devx.com...
    > >>
    > >> I need to update a recordset that I retrieve from SQL Server. Right

    now
    > >I'm
    > >> using the 'sqlPassThrough' type of recordset, and it's returning a

    > >non-updatable
    > >> recordset. Does anyone know how to retrieve data from SQL Server, with

    > >VB,
    > >> and be able to update this data using the EDIT method of the recordset?
    > >> For instance, I want to be able to do this:
    > >>
    > >> MySQL = "select mynumber, uniquefield from mytable where mynumber = 20"
    > >> Set rs = Nothing
    > >> Set rs = dbCashApp.OpenRecordset(MySQL, dbOpenDynaset,

    dbSQLPassThrough)
    > >> rs.Edit 'invalid operation
    > >> rs.fields("MyNumber")= 200
    > >> rs.Update
    > >>
    > >> I get an invalid operation error when I try to say rs.edit. That's

    > >because
    > >> the recordset is non-updatable.
    > >>
    > >> I realize that I could just execute an update query--but that will not

    > >serve
    > >> my purposes for a variety of reasons.
    > >>
    > >> Does anyone know?
    > >>
    > >> As info, 'mytable' does have a unique index.
    > >>
    > >> THANKS for your help!!!
    > >> -Julie

    > >
    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center