Visual Basic

Database Connectivity

MAKE WEB APPLICATION WITHOUT KNOWLEDGE OF CODING? CLICK HERE

Contents

ActiveX Data Objects (ADO)
Recordset Methods
Structured Query Language (SQL)
Data Access Objects (DAO) and the Data Control

ActiveX Data Objects (ADO)
To use ADO, you must set the appropriate references by selecting, Project, References, and making sure there's a tick against Microsoft ActiveX Data Object Library. OLE DB provides a set of system-level COM interfaces that provide a method for retrieving data regardless of its format. ADO provides a language independent Automation model to provide access to the OLE DB services. The ADO object model comprises the following seven objects.

ADO Objects

Object	Description
Command	The Command object defines the command to be given to a data source. This may be either a SQL statement or an invokation of a stored procedure.
Connection	The Command object defines the command to be given to a data source. This may be either a SQL statement or an invokation of a stored procedure.
Connection Error	The Error object may be used if the OLE DB provider has implemented it. In its absence, errors are raised in the Visual Basic Err object.
Field	The Field object represents a column in a Recordset object.
Parameter	The Parameter object is implemented as a collection and stores the parameters to be used by a Command object.
Property	The Property object may be used by the Connection, Command, Recordset, and Field objects. Each of the objects has a set of unique properties that either describe or control the behaviour of the object. The properties may either be built-in, or added to the Properties collection by the data provider.
Recordset	The Recordset object contains a set of records retrieved from the Connection object.

Cursors
The Recordset object fetches data using Cursors, which are used to identify the current position in the result set, and what row will be returned next. The Recordset object supports four cursor types.

Recordset Cursors

Cursor	Constant	Description
Command	adOpenDynamic	The result set reflects subsequent changes, additions and deletions by other users. There are no limitations on how the result set may be navigated.
Forward-only	adOpenForwardOnly	The result set reflects subsequent changes, additions and deletions by other users. The result set may only be navigated in a forward direction, and may improve performance if you make a single pass through the result set. If no cursor type is specified, this cursor is used by default.
Keyset-Driven	adOpenKeyset	The result set reflects subsequent modifications to data, but not records added by other users. This cursor prevents access to records deleted by other users. There are no limitations on how the result set may be navigated.
Static	adOpenStatic	Returns a static set of records that cannot be modified. Modifications, additions, and deletions to data by other users will not be seen with this type of cursor. There are no limitations on how the result set may be naviagted.

The cursor type is set with the CursorType property of the Recordset object.

adoRS.CursorType = adOpenDynamic

Opening a Recordset
The Open method is used to retrieve a recordset from the database. The method takes four optional parameters, the recordset source, the active connection, the cursor type, the lock type, and options. The following table describes the parameters.

Open Method Parameters

Parameter

Description

Source

The source may be a Command object variable, a SQL statement, a table, or a stored procedure. In the absence of this parameter, the recordset will be opened with the value of the Source property of the Recordset object.

ActiveConnection

The ActiveConnection property may be either a valid Connection object variable, or a Connction string. In the absence of this parameter, the recordset will use the ConnectionString property of the Recordset object.

CursorType

Determines the type of cursor to use with the Recordset, where the cursor type constants are adOpenDynamic, dOpenForwardOnly, adOpenKeyset, or adOpenStatic. In the absence of this parameter, the recordset will use the value of the CursorType property of the Recordset object. If no value is specified as either a parameter, or in the CursorType property, adOpenForwardOnly is used by default.

LockType

Determines how to implement concurrent sessions. The following constants may be used:

LockType Constants

LockType	Description
adLockBatchOptimistic	Uses an optimisic lock for bacth updates, as opposed to immediate updates.
adLockOptimistic	Only locks a record when an Update method is called.
adLockPessimistic	Usually locks a record immediately upon editing.
adLockReadOnly	No locking is used as the records are read only. If no value exists, adLockReadOnly is used by default.

In the absence of this parameter, the recordset will be locked using the value in the LockType property of the Recordset object.

Options

A constant that indicates how the provider should evaluate the Source argument if it represents something other than a Command object.

LockType Constants

CommandType	Description
adCmdText	Specifies that the command should be interpreted as text.
adCmdTable	Specifies that the command should use a SQL statement to retrieve all records from the table specified in Source.
adCmdTableDirect	Specifies that the command should retrieve all records from the table specified in Source directly.
adCmdStoredProc	Specifies that the value of Source is a stored procedure.
adCmdUnknown	Specifies the type of command in Source is unknown.
adCommandFile	Specifies that the Recordset should be restored from the file named in Source.
adExecuteAsync	Specifies that the Source should be executed asynchronously.
adFetchAsync	After the initial quantity specified in the CacheSize property is fetched, remaining rows should be fetched asynchronously.

The following may be used to open a recordset without using any of the properties of the Recordset object.

adoRS.Open strSQL, strConn, adOpenForwardOnly, adLockReadOnly, adCmdText

The following example uses the Properties to determine how the recordset should be opened, and calls the Open method with no parameters.

Dim adoConn As New ADODB.Connection
Dim adoRS As New ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & "c:\db\music.mdb"
adoConn.ConnectionString = strConn
adoConn.Open
adoRS.Source = "SELECT * FROM CD"
adoRS.CursorType = adOpenForwardOnly
adoRS.ActiveConnection = adoConn
adoRS.Open
Do While Not adoRS.EOF
Debug.Print adoRS.Fields("Title").Value
adoRS.MoveNext
Loop
adoRS.Close
Set adoRS = Nothing
adoConn.Close
Set adoConn = Nothing

Navigating Through a Recordset
The Move method moves to a record in a database specified by a row number. An optional parameter may be specified to move relative to a bookmark. The following example moves to the fourth record in the Recordset.

adoRS.Move 4

MoveFirst
The MoveFirst method moves to the first record in the Recordset.

adoRS.MoveFirst

MoveLast
The MoveLast method moves to the last record in the Recordset.

adoRS.MoveLast

MoveNext
The MoveNext method moves to the next record in the Recordset. The EOF property should be checked to prevent an error occurring. EOF is set to True if the current record is after the last record in the Recordset.

adoRS.MoveNext
If adoRS.EOF Then
adoRS.MovePrevious
MsgBox "At last record", vbInformation, "Database"
End If

MovePrevious
The MovePrevious method moves to the previous record in the Recordset. The BOF property should be checked to prevent an error occurring. BOF is set to True if the current record is before the first record in the Recordset.

adoRS.MovePrevious
If adoRS.BOF Then
adoRS.Recordset.MoveNext
MsgBox "At first record", vbInformation, "Database"
End If

Adding Records
The AddNew method adds a new record to the end of the Recordset.

adoRS.AddNew

Updating Records
The Update method saves the contents of the edit buffer to a specified Recordset object. Use after changing data in the current record.

response = MsgBox("Update this record", vbYesNo + vbQuestion, "Update")
If response = vbYes Then
adoRS.Update
Else
adoRS.CancelUpdate
End If

Structured Query Language
The Structured Query Language (SQL - pronounced "sequel") is a very simple language used to manipulate relational databases. You can use SQL to retrieve, insert, update and delete records from relational databases. Clauses may be added to restrict the records that are effected.

The Select Query
The Insert Statement
The Update Statement
The Delete Statement

The SQL Select Query
The select query is used to retrieve records from a database. The keywords used in a select query are summarised in the following table.

Select Keywords

Keyword	Description
SELECT	Retrieves fields from one or more tables.
FROM	Tables containing the fields.
FROM	Criteria to restrict the records returned.
GROUP BY	Determines how the records should be grouped.
HAVING	Used with GROUP BY to specify the criteria for the grouped records.
ORDER BY	Criteria for ordering the records.

The simplest select query is to retrieve all records from a single table. For example, to retrieve all records from the CD table in the music database:

SELECT * FROM CD

To select specific fields from a table, replace the (*) with a comma-separated list of field names. For example, to select the Artist and Title from the CD table:

SELECT Artist, Title FROM CD

To limit the records returned according to some criteria, use the WHERE clause. For example, to return all Artists whose name starts with an 'S':

SELECT * FROM CD WERE Artist LIKE 'S*'

The condition in the WHERE clause can contain the operators <, >, <=, >=, =, <> and LIKE. Multiple clauses are joined together using the logical AND and OR operators. The results of a query are ordered using the ORDER BY clause. The results may be sorted in ascending order using the ASC keyword or descending order using the DESC keyword. For example, to list the artists in ascending order by name:

SELECT * FROM CD ORDER BY Artist ASC

To list the artists in descending order by name:

SELECT * FROM CD ORDER BY Artist DESC

The DISTINCT keyword may be used to retrieve unique values from fields where duplicate data may exist. The following example returns a list of unique surnames.

SELECT DISTINCT surname FROM customer ORDER BY surname

The SQL Insert Statement
The insert statement is used to insert records into a table. The keywords used in an insert statement are summarised in the following table.

Insert Keywords

Keyword	Description
INSERT	Inserts data into a table.
INTO	Specifies the table name to insert the data.
VALUES	The data for the fields in the table.

The following example inserts the data "Gorillaz" and "Debut Album" into the "Band" and "Title" fields of the "CD" table.

INSERT INTO CD ([Band], [Title]) VALUES ('Gorillaz', 'Debut Album')

The next example uses ADO to insert a record into a database.

Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String, strSQL As String
' Open a Connection object
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & "c:\db\music.mdb"
adoConn.ConnectionString = strConn
adoConn.Open
' Define a query string
strSQL = "INSERT INTO Programming ([Band], [Title]) VALUES ('"
strSQL = strSQL & txtBand.Text & "', '"
strSQL = strSQL & txtTitle.Text & "')"
' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = adoConn
adoCmd.Execute
' Tidy up
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

Further records may be added to the database using the INSERT Statement with different values.

The SQL Update Statement
The update statement is used to update records in a database. The keywords used in an update statement are summarised in the following table.

Update Keywords

Keyword	Description
UPDATE	Updates data in one or more tables.
SET	Specifies the field names to be updates. If the fields belong to more that one table, the table name should be specified before the field name. (eg. CD.Artist).
WHERE	Criteria to restrict the records being updated.

The following example updates all records in the "CD" table where the Band is "JJ73" to "JJ72".

UPDATE CD SET [Band] = 'JJ72' WHERE [Band] = 'JJ73'

The next example uses ADO to update a record in the database.

Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String, strSQL As String
' Open a Connection object
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & "c:\db\client.mdb"
adoConn.ConnectionString = strConn
adoConn.Open
' Define a query string
strSQL = "UPDATE customer SET [email]='"
strSQL = strSQL & txtEmail.Text
strSQL = strSQL & "' WHERE [CustNo]=" & CInt(txtCustNo.Text)
' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = adoConn
adoCmd.Execute
' Tidy up
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

The SQL Delete Statement
The delete statement is used to delete records in a database. The keywords used in a delete statement are summarised in the following table.

Delete Keywords

Keyword	Description
DELETE	Deletes the records in one or more tables.
FROM	Tables containing the records.
WHERE	Criteria to restrict the records deleted.

The following example deletes the records from the "CD" table where the Band and Title are passed from a Form.

The following example deletes the record in the "CD" table where the Band is "Trash" and the title of the album is "Deserve To Go".

DELETE FROM CD WHERE [Band]='Trash' AND [Title]='Deserve To Go'

The next example uses ADO to delete a set of records from a database.

Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String, strSQL As String
' Open a Connection object
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & "c:\db\active.mdb"
adoConn.ConnectionString = strConn
adoConn.Open
' Define a query string
strSQL = "DELETE FROM ActiveUsers WHERE [Session]='Expired'"
' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = adoConn
adoCmd.Execute
' Tidy up
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

If you don't find what you are looking for. Please click here to submit your query, our experts will reply soon.

E-mail : sales@virtualsplat.com
Phone : +91-9892413501

Whatsapp Icon +91-9967648641