Recordset Edit/Update Question? 1

[VBUser77]

I have a recordset based on a table. I like to edit that recordset and make some changes to it but I don't want those changes to be saved/reflected in my table unless I execute the update method. Before I execute the update method, I like to use my record set somewhere else in the code to perform some calculations. Will my recordset contain the changes that I made before I excute the update method?

Dim rst As Recordset

Set rst = ObjMyDB.OpenRecordset("IGTable", DB_OPEN_DYNASET)

rst.Edit

' Here i make some changes

Now if i use "rst" somewhere else in the code to perform some calculations, would it contain the changes that i made earlier even though I have not executed rst.Update method yet.

I think my recordset will show the changes before the update method is executed but my table will only show changes after i execute the rst.update. Is it right?

 

[VBslammer]

The updates are lost as soon as you move the recordset to another record or the recordset variable goes out of scope, regardless of whether you called the .Edit method or not.

You can use the recordset values to do calculations all day long, but once you enter edit mode you have to commit the changes before doing any navigation or they won't even be available in the recordset.

What you're probably trying to do requires the use of a disconnected recordset, which would allow you to make changes to the recordset without committing them to the database.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBslammer]

Here's a quick example of a disconnected recordset using ADO:

Sub RstDisconnected()
  Dim cnn As New ADODB.Connection
  Dim rst As New ADODB.Recordset
  
  [green]'create a connection[/green]
  cnn.ConnectionString = CurrentProject.BaseConnectionString
  cnn.Open
  
  [green]'open a client-side recordset using our connection[/green]
  rst.CursorLocation = adUseClient
  rst.LockType = adLockBatchOptimistic
  rst.Open "SELECT * FROM Customers", cnn
  
  [green]'kill the connection[/green]
  Set cnn = Nothing
  
  [green]'muck with the records[/green]
  With rst
    While Not .EOF
      .Fields(1) = .Fields(1) & "#"
      .Update
      .MoveNext
    Wend
  End With

  [green]'show the local changes (uncommitted)[/green]
  With rst
    .MoveFirst
    While Not .EOF
      Debug.Print .Fields(1)
      .MoveNext
    Wend
  End With

  [green]'restablish the connection[/green]
  cnn.ConnectionString = CurrentProject.BaseConnectionString
  cnn.Open
  
  [green]'perform the update[/green]
  rst.UpdateBatch
  
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[VBUser77]

VBSlammer,

Can you please tell me how to use disconnected recordset in DAO.

yes you are right. I need to use the disconnected recordset ... make some changes to it and if those changes pass certain criteria (that I will check in the code), I would like to commit those changes to the database table that my recordset was based upon. Else, I wouldn't commit those changes to my database table. Thanks a lot for your help.

Jay

 

[VBUser77]

Here is what I am trying to use but it's giving me an error:

Dim ObjMyDB As DAO.Database
Dim rst As DAO.Recordset

Set ObjMyDB = DBEngine.Workspaces(0).Databases(0)

Set rst = ObjMyDB.OpenRecordset("TestTable", DB_OPEN_DYNASET)

rst.Connection = Nothing

I have also tried rst.Connection = False but still getting an error.

Any thoughts, ideas how to make it work!

Thanks

Jay

 

[PHV]

You're playing with DAO instead of ADODB ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBUser77]

I know I am using DAO but i don't know how to disconnect a recordset in DAO. Do you have any ideas?

Thanks
Jay

 

[PHV]

I'm afraid only ADO admits disconnected RS...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBUser77]

How could I figure out whether I need to use

rst.cursorLocation = adUseServer or

rst.cursorLocation = adUseClient

Thanks in advance.

jay

 

[PHV]

As you want to play with disconnected recordset, I guess a client side cursor is safer, don't you think ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244