SalesLogix Basics 4 – ADO Recordsets in SalesLogix LAN

By using ADO, you have a powerful resource for reading and storing data in SalesLogix.  In this basics article, I’ll outline the main components involved in constructing and using an ADO recordset in general, and specifically with SalesLogix .  If you’ve never worked with ADO before, this simple example will give you what you need to incorporate ADO into your own customizations.



Set rs = CreateObject(“ADODB.Recordset”)
With rs
         Set .ActiveConnection = Application.GetNewConnection
         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockOptimistic
         .Open “select * from c_test where c_testid = ‘” & labelID.Caption & “‘”

         If .RecordCount = 0 Then
            .Fields(“c_testid”).Value = Application.BasicFunctions.GetIDFor(“c_test”)
            .Fields(“accountid”).Value = CurrentID
            .Fields(“createuser”).Value = Application.BasicFunctions.CurrentUserID
            .Fields(“createdate”).Value = Now
         End If

         .Fields(“modifyuser”).Value = Application.BasicFunctions.CurrentUserID
         .Fields(“modifydate”).Value = Now
         .Fields(“field1”).Value = edit1.Text
         .Fields(“field2”).Value = edit2.Text

End With
Set rs = Nothing



The basic
steps for using an ADO Recordset are:

  1. Create and ADO connection to the database
  2. Open the database connection
  3. Create an ADO recordset
  4. Set connection options for the
  5. Open the recordset
  6. Extract/Modify the data you
    need from the recordset
  7. Update the recordset (if
  8. Close the recordset
  9. Close the connection.


first two steps are handled by SalesLogix so we don’t need to worry about them
within the SLX Environment.  I’m
including them here just to list all the steps in working with a ADO Recordset



Create an ADO connection to the database


set conn=Server.CreateObject(“ADODB.Connection”)



In this
code, we have created a connection object and are using the JET OLEDB provider
to connect to an Access database


Open the database connection




This code
uses the connection object to open the access database, “Northwind”.


Create an ADO recordset


Set objRS =

Set objRS.ActiveConnection =


Here we
have created the basic ADO
recordset object, and set it to use a connection from the SLX connection pool.


Set connection options for the


Cursors are
used to control a few different things, namely record navigation, visibility of
changes in the database, and updatability of data.




The cursor
location can be set to use either the Server or Client cursor service.  Basically, this controls weather or not
processing is done on the server or on the individual client machine.  Server-side cursors are used by default.



  • adUseServer (default)
  • adUseClient




The cursor
type controls how you can cycle through the records returned in the recordset
as well as visibility of any changes you make.



  • adOpenForwardOnly (Default)
    • Uses a forward-only
      cursor.  Identical to a static
      cursor, except you can only scroll forward through records.  This can improve performance when you
      need to make only one pass through a recordset.
  • adOpenKeySet
    • like a dynamic cursor, except
      that you can’t see records that other users add, although records that
      other users delete are inaccessible from your Recordset.  Data changes from other users are
  • adOpenDynamic
    • Additions, changes and
      deletions by other users are visible, and all types of movement through
      the Recordset are allowed.
  • adOpenStatic
    • Additions, Edits and Deletes
      from other users are not visible.




The lock
type specifies the type of locking when editing values in the recordset.



  • adLockReadOnly
    • Recordset is read-only
  • adLockPessimistic
    • The provider locks records
      immediately after editing.
  • adLockOptimistic
    • Records are locked only when
      Update is called
  • adLockBatchOptimistic
    • Records are locked when
      updated is called.  Required for
      batch updates.



objRS.CursorLocation = adUseClient

objRS.CursorType = adOpenStatic

objRS.LockType = adLockOptimistic


Open the recordset


Here we are
opening the recordset, using the SQL query provided.


objRS.Open “Select * from TestTable
where 0=1”



Extract/Modify data from the


Values are
returned using (in this case set to a variable strValue):

strValue = objRS.Fields(“FieldName”).Value


Values can
also be set by using: 


objRS.Fields(“FieldName”).Value =
“New Value”


Update the recordset


objRS.Update or objRS.UpdateBatch


The update
command applies changes made to the recordset. 
Until this is called, the changes are not permanent.  Closing the recordset before calling update
would cause the changes to be lost.


Close the recordset



Set objRS.Nothing


statement closes the recordset when you are done.  Setting the object = “Nothing”, aka “Destroying”
the object, frees up the resources used by the recordset object.

Close the connection


This step
is not needed when using recordsets in SalesLogix.  The connections would be closed when closing
the database.




Batch Updates Example:


By using the locktype of adLockBatchOptimistic and UpdateBatch, you can loop through a number of updates, and commit them all at once.


Set rs = CreateObject(“ADODB.Recordset”)

With rs

         Set .ActiveConnection = Application.GetNewConnection

         .CursorLocation = adUseClient
         .CursorType = adOpenStatic
         .LockType = adLockBatchOptimistic

         .Open “select * from c_test where Value = ‘” & labelID.Caption & “‘”

          While not (.Bof or .Eof)

              .Fields(“modifyuser”).Value = Application.BasicFunctions.CurrentUserID
              .Fields(“modifydate”).Value = Now
              .Fields(“field1”).Value = edit1.Text
              .Fields(“field2”).Value = edit2.Text




End With

Set rs = Nothing

I hope you find this example helpful.  Thanks for reading!  [:)]



Want content like this delivered to your inbox? Sign up for our newsletter!

Jason Buss

Jason is a senior application developer with Customer FX.

Submit a Comment

Your email address will not be published. Required fields are marked *