March 8, 2021
Hot Topics:

Quick Start Guide to SQL Server 7 -- Part 2

  • By Karl Moore
  • Send Email »
  • More Articles »

Naturally, you can use the DAO and ADO controls to access your SQL Server data. But most users prefer to get at it via pure code after all, not only does it reduce the need to use a form and bound controls, it's also a lot more flexible.

We're now going to look at a skeletal piece of commented ADO code for accessing your SQL Server records from within Visual Basic. Note that you must have a reference (Project, References) to the Microsoft ActiveX Data Objects Library before running this code.

Private Sub Command1_Click()Dim objConn As New ADODB.ConnectionDim objRS As New ADODB.RecordsetDim objErr As ADODB.ErrorobjConn.Open _"Driver=SQL Server;Server=COLOSSI;Database=Blossom; _        User ID=KarlMoore;Password=TEST"' Open the SQL Server connectionobjConn.CursorLocation = adUseClientIf objConn.State = adStateOpen Then' If everything is OK and we have a connectionobjRS.CacheSize = 10objRS.Open "Select * from tblCustomers", objConn, _              adOpenStatic, adLockOptimistic' Open the recordset object, selecting everything' from tblCustomers - passing our objConn connectionIf Not objRS.EOF And Not objRS.BOF Then' If not at the End Of File (EOF) nor' Beginning Of File (BOF) - in other words,' if we actually have records here - then...MsgBox objRS.Fields("CustomerID")' Display one of our fields just to prove' everything went A-OKobjRS.Fields("CustomerID") = 123objRS.UpdateEnd IfobjRS.Close' Close the recordsetElse' If the connection didn't open A-OK, thenFor Each objErr In objConn.ErrorsMsgBox objErr.DescriptionNext' Display all the errors in the connection objectEnd IfSet objRS = NothingobjConn.CloseSet objConn = Nothing' Tidy up the references, etc.End Sub

Most of this is pretty standard stuff. However there are a few lines of code you may wish to fine tune to suit your individual needs, particularly if you're working in a multi-user, real-time environment.

Unfortunately, this is neither an ADO nor general data access tutorial - and some of the following issues are simply raised for completeness. If it's your first encounter, you may find it all a little strange. Thankfully the ADO help file can help out, with its own mini tutorial. To view it, find and run the file 'ADO210.CHM' or 'ADO200.CHM'. Alternatively, just stick with the template.

CursorLocation Property

objConn.CursorLocation = adUseClient

First off, we have the CursorLocation property. The cursor represents where in the recordset you currently are. It's a little like Word's own flashing cursor, which shows you where you are in the current document.

You can set this to either:

  • adUseServer This keeps the cursor with the server, a real resource hog if you have plenty of users. However, when used with certain types of cursor (below), it does have the advantage of being able to - spot newly added records, remove fresh deletions from your recordset and so on. But you use it at a price.
  • adUseClient This keeps the cursor on the client side, the user's computer. This removes workload from the server but doesn't allow you to view new records that match your criteria, etc. Still, most of the time, such features aren't required

If you're in doubt, go for adUseClient.

Cursor Type

objRS.Open "Select * from tblCustomers", objConn,adOpenDynamic, adLockOptimistic

The third argument in this code asks for the type of cursor you want. You can replace the current adOpenDynamic with any of the following:

  • adOpenForwardOnly This cursor allows you to simply scroll forward through the recordset, but there's no going back. This option is very resource friendly
  • adOpenKeyset This cursor allows you to move both backwards and forwards through the recordset. It maintains a live link with the database to ensure all fields are kept up-to-date (ie, they reflect the changes of other users), however it doesn't detect any externally added records. The keyset cursor maintains a live connection to the database and is quite a resource hog
  • adOpenStatic This option grabs a copy of the records. You can't view any changes, new records or deletions by other users, though you can make edits yourself. This cursor is pretty resource friendly
  • adOpenDynamic Using a dynamic cursor, you can view all changes, additions and deletions by other users. It maintains a live conversation and so is pretty resource expensive

Lock Type

objRS.Open "Select * from tblCustomers", objConn,adOpenDynamic, adLockOptimistic

That fourth argument in this code asks for a 'lock type'. The current adLockPessimistic is interchangeable with any of the following:

  • adLockReadOnly Read only. The data retrieved cannot be modified. Very resource friendly, but not always practical
  • adLockPessimistic A lock is placed on records as soon as you start editing them and released when the Update or Cancel methods are called
  • adLockOptimistic This option means that when you run the Update method, the record is locked, updated and then released, all in one swoop. If someone else changed the record before you started editing it, an error will occur
  • adLockBatchOptimistic Whereas adLockOptimistic allows you to edit a record 'offline', then update it in one swoop, adLockBatchOptimistic allows you to do this with more than one record. You can edit the records in the recordset, then do a complete database update on all those changed by running the UpdateBatch method. Any errors will be returned to the Errors collections of the connection

Cache Size

objRS.CacheSize = 10

Another interesting recordset property is CacheSize. This determines how many rows of data are buffered on the client machine.

Increasing this from the default of one allows the server to work more efficiently by returning a whole bunch of rows to the client at once, as opposed to one per shot.

However, don't forget - this means the information you store locally may not always the most up-to-date from the server. So in a multi-user environment, this could lead to update conflicts.

Check us out in a fortnight for the third part in our print-and-collect series. We'll be covering; views, defaults, user defined types and rules!

Page 5 of 5

This article was originally published on November 20, 2002

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date