ADO Recordset CursorType Property

ADO Recordset CursorType Property

• freeflyr

  Hi Derek,

  To be honest, I didn't really explore it fully because at the end of the day I didn't know really what I was doing and it would have been too risky. During my short time on here, it's evident that you know what you're talking about, so I dare say your approach would have worked had I taken it that far.

  Anyhow, I have got round the problem and it didn't involve updating recordsets. This whole issue came about because I didn't know how to write the SQL needed to create an expression, which performed tests on a source field. Instead, I tried to get around it by updating the recordset itself. I have since learned how to write the SQL needed per below (blue text):

  Cmd1.ActiveConnection = Conn1
  Cmd1.CommandText = "SELECT 'Promotion Source' = " & _
  "CASE " & _
  "WHEN PromoSource = 'WWW' THEN 'Web' " & _
  "WHEN PromoSource Is Null THEN 'OTHER' " & _
  "WHEN IsNumeric(PromoSource) = 1 THEN 'REC' " & _
  "ELSE PromoSource " & _
  "END, " & _
  "Count(CustomerID) As 'NumberOfCustomers' " & _
  "FROM tblCustomers " & _
  "WHERE DateEntered < '" & Format(CurrentWkDate, "yyyy-mm-dd") & "' " & _
  "And CA4 = '" & Country & "' " & _
  "GROUP BY " & _
  "CASE " & _
  "WHEN PromoSource = 'WWW' THEN 'Web' " & _
  "WHEN PromoSource Is Null THEN 'OTHER' " & _
  "WHEN IsNumeric(PromoSource) = 1 THEN 'REC' " & _
  "ELSE PromoSource " & _
  "END " & _
  "ORDER BY Count(CustomerID) DESC"
  Cmd1.CommandTimeout = 100
  Cmd1.Parameters.Refresh
  
  The PromoSource field which is being evaluated in the CASE statement was what I was trying to update in the recordset.

  The only thing I'd like to clear up now is how I avoid having to re-type the CASE statement again in the GROUP BY clause. I've given the expression an alias in the SELECT statement, but it doesn't appear to work when I try to use it in GROUP BY. I've seen other threads on this issue so I'll have a gander round.

  Cheers everybody for you help!!

  
  

• OmidQRose

  Hi Keithy Boy,

  There is another recordset property you need to consider and thats cursor location, which can be set to server or client.

  Static cursors are generally read only on the server but on the client side they are read/write. Client side static cursors are stored in an ADO Cursor engine, here's an abstract from a book on ADO I have.

  ...because this static cursor is maintained by the ADO Cursor Engine (client) rather than by the database system (server), changing the contents of the cursor does not automatically update the corresponding records in your database. When you modify your client-side recordset and ask ADO to update the database, by means of the Update or UpdateBatch method, ADO interprets the changes you've made to the Recordset object and attempts to update the database by using action queries that reflect these changes.

  So from that I'd say use a Static, Client Side Cursor, and don't call adoRecordset.Update(). Test it out on a test database though and make sure it works as it says it does !

  The books called Programming ADO by David Sceppa and it's on Microsoft Press, it's a handy little reference.

  
  
  

• Jim Wooley

  Solution 1: After you have your dynamic recordset create a disconnected recordset

  set rs.ActiveConnection = nothing

  Solution 2: open a forward only recorset and create an empty recordset with the fields that you want without a connection and loop through the forward only recordset creating records in your empty recordsets as you loop through

  Public rs_drivers as adodb.recordset

  Dim sql As String
  Dim rs As ADODB.recordset
  sql = "SELECT Drivers.* FROM Drivers;"

  Dim rs As ADODB.recordset
  Set rs = New ADODB.recordset
  rs.CursorLocation = adUseClient
  rs.LockType = adLockOptimistic
  rs.CursorType = adOpenForwardOnly
  rs.Open sql, ConnectionString
  
  Dim cn As ADODB.Connection
  Set Form_SubFrmManageDrivers1.recordset = Nothing
  If Not (rs_drivers Is Nothing) Then
  rs_drivers.Close
  Set rs_drivers = Nothing
  Else
  Set rs_drivers = Nothing
  End If
  Set rs_drivers = New ADODB.recordset
  rs_drivers.Fields.Append "DriverId", adInteger, , adFldMayBeNull
  rs_drivers.Fields.Append "First", adVarWChar, 50, adFldMayBeNull
  rs_drivers.Fields.Append "Last", adVarWChar, 50, adFldMayBeNull
  rs_drivers.Fields.Append "DriverPercentage", adDouble, , adFldMayBeNull
  rs_drivers.CursorLocation = adUseClient
  rs_drivers.LockType = adLockOptimistic
  rs_drivers.CursorType = adOpenDynamic
  rs_drivers.Open
  Do While Not (rs.EOF Or rs.BOF)
  rs_drivers.AddNew
  rs_drivers!DriverId = rs!DriverId
  rs_drivers!First = rs!First
  rs_drivers!Last = rs!Last
  rs_drivers!DriverPercentage = rs!DriverPercentage
  rs_drivers.Update
  rs.MoveNext
  Loop

  rs.close

  set rs = nothing
  If rs_drivers.recordcount > 1 Then
  rs_drivers.MoveFirst
  End If
  'Set rs_drivers = Dynamic_Rs(sql)
  Set Form_SubFrmManageDrivers1.recordset = rs_drivers

  On Form Unload close the rs_drivers and set it to nothing

  
  

• Cezary

  Cheers Derek!

  I'll do as you say and try it on a test access database. With things like this, you need to play around and experiment before you get the answer and I'd rather not do that with our live database!

  That book sounds good too. May consider purchasing it.

  Regards,

  Keith

  
  

• brohans

  Hey Keithy Boy,

  I'm surprised that didn't work!! From what I read that should have been the solution, can I ask, did you close the recordset once you populated it with data or did you leave the recordset open

  Maybe the open recordset does the update and closing the recordset doesn't... I really thought that would have worked.

  
  
  

• spattewar

  I've ressurected this thread as I got our IT department to take a static copy of a table which I've been safely testing with. No matter what combinations of CursorType, CursorLocation and LockType properties I use on the recordset, it still continues to update the data source when I make changes to the records. I want the recordset, once opened and populated, to sever any links to the datasource so it's just a static table. Can this be done in ADO or is there an entirely different technique/functionality I need to use

  Cheers!

  
  
• ADO Recordset CursorType Property