Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objCon=CreateObject("ADODB.Connection")
Set objRec=CreateObject("ADODB.Recordset")
objCon.Open "Provider=sqloledb;Data Source=(local);User Id=sa;Password=;Initial Catalog=Ramesh"
objRec.CursorLocation = adUseClient
objRec.Open "Select * from rameshtable",objCon,adOpenStatic,adLockOptimistic
While(objRec.EOF=False)
If objRec.EOF=False Then
Msgbox "Name: " & objRec("Name").Value & vbCrLf
objRec.MoveNext
Else
Msgbox "No Record Found"
End If
Wend
objRec.Close
objCon.Close
Set objRec=Nothing
Set objCon=Nothing
Msgbox "Executed Sucessfully"
Lets discuss the important objects in above code.
1. Set objCon=CreateObject("ADODB.Connection")
objCon is the name of the Connection Object
Connection Object: The ADO Connection object provides the means to obtain an open connection to a data source that can be the name of either an ODBC data store or an OLE DB provider. Through this open connection, you can access and manipulate a database.
2. Set objRec=CreateObject("ADODB.Recordset")
objRec is the name of the Recordset which stores queries used to retrieve the data from the database. there are 64 properties, collections, methods and events that belongs to the Recordset Object.
3. Const adOpenStatic = 3
adOpenStatic is a CursorType property sets or returns the cursor type to use when opening a Recordset object. This property can take a CursorTypeEnum value.
CursorTypeEnum Values
| Constant | Value | Description |
|---|---|---|
| adOpenUnspecified | -1 | Does not specify the type of cursor. |
| adOpenForwardOnly | 0 | Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset. |
| adOpenKeyset | 1 | Uses a keyset cursor. 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 by other users are still visible. |
| adOpenDynamic | 2 | Uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the Recordset are allowed, except for bookmarks, if the provider doesn't support them. |
| adOpenStatic | 3 | Uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible. |
4. Const adLockOptimistic = 3
adLockOptimistic is LockTypeEnum value that specifies the type of locking on a Recordset object. Default is adLockReadOnly
LockTypeEnum Values
| onstant | Value | Description |
|---|---|---|
| adLockUnspecified | -1 | Unspecified type of lock. Clones inherits lock type from the original Recordset. |
| adLockReadOnly | 1 | Default. Read-only records |
| adLockPessimistic | 2 | Pessimistic locking, record by record. The provider lock records immediately after editing |
| adLockOptimistic | 3 | Optimistic locking, record by record. The provider lock records only when calling update |
| adLockBatchOptimistic | 4 | Optimistic batch updates. Required for batch update mode |
5. Const adUseClient = 3
adUseClient is a CursorLocation property sets or returns a long value that indicates the location of the cursor service. It can be set to one of the CursorLocationEnum values. Default value is AdUseServer.
A cursor is used to:
- control record navigation
- control the visibility of changes in the database
- control the updatability of data
CursorLocationEnum Values
| Constant | Value | Description |
|---|---|---|
| adUseNone | 1 | OBSOLETE (appears only for backward compatibility). Does not use cursor services |
| adUseServer | 2 | Default. Uses a server-side cursor |
| adUseClient | 3 | Uses a client-side cursor supplied by a local cursor library. For backward compatibility, the synonym adUseClientBatch is also supported |
6. objCon.Open "Provider=sqloledb;Data Source=(local);User Id=sa;Password=;Initial Catalog=Ramesh"
Syntax for opening a Connection with a database
7. objRec.Open "Select * from rameshtable",objCon,adOpenStatic,adLockOptimistic
This is a syntax for Querying with the database
