ADO

ADO

VBS does not have its own database commands. This does not mean that you cannot access the database but only that you need to use special objects to access the database. These objects are called ActiveX Data Objects (ADO).

ADO objects support access to various types of data sources. You can use ADO objects to work with databases – as shown here – and to access file systems or mail systems. ADO objects are independent of a specific database, which means you can use ADO objects for all databases for which an OLE database driver is available. The database drivers are available for SQL servers from Microsoft and Oracle, and the Jet Engine.

VBS, Microsoft Access, and several other applications use the Jet Engine as a database system. Whereas Microsoft Access is an application based directly on the Jet Engine as the database system, VBS is a programming system that provides the Jet Engine as an optional database system.

The Jet Engine is not a program that you can execute independently; it is a collection of system files. If you want to access the Jet Engine, for example, to open a database, you need MS Access, VBS, MS Excel, or MS Word. Therefore, the interaction between the Jet Engine and ADO makes VBS a development system that has slight limitations compared to a real database program.

Note  You must install a 64-bit version of ADO to work with ADO connection strings. Microsoft installs ADO with the Microsoft Office package. If your system cannot open a store, update your ADO installation. The "Microsoft Data Access Components" is on the Microsoft homepage. If you want to access Oracle data stores, you must install an Oracle client.

Use the ADO objects Connection, Command, RecordSet, and Error to program databases.

Connection

Use the Connection object to connect a database, to carry out a request, and to disconnect the database. To create a Connection object, use

Set oDBConn = CreateObject("ADODB.Connection")

Use the Open method to open a database connection. The database is connected according to the parameters set in ConnectionString. ConnectionString contains details about the database, such as the required driver (Provider), the database to be accessed, the user identification, and the password to be used.

oDBConn.ConnectionString = "Provider=" & "Microsoft.ACE.OLEDB.12.0; Data Source= c:\Test_DB.mdb"
oDBConn.Open

Note  Use the ArrayToChannels command to convert an array into channels. Use the AdoConStrGet command to specify the ADO connection strings for a database connection.

You can specify ConnectionString directly as a parameter of the Open method. The Execute method executes an SQL command. If this command returns data, the command saves the data in a new RecordSet object. For example, the following statement reads the complete contents of the table My_Table:

Set RS = oDBConn.Execute("Select * From My_Table")
MsgBox RS.GetString

If you work with an SQL server or any other database that allows transactions, you can use the Connection object to carry out Commit actions and Rollback actions. To disconnect the database use oDBConn.Close This command also closes the data sets opened through the connection.

Command

Use the Command object to send commands to the connected database. Use the Command object in the same way as the Execute method of the Connection object. You can use the additional properties of the Command object to specify the command. You generate a Command object with the following statement:

Set cmConn = CreateObject("ADODB.Command")

Then you assign the previously opened database to an object variable and use the CommandText property to send an SQL command. The result of the Execute method is a RecordSet if the command returns data as shown in the next example.

cmConn.ActiveConnection = oDBConn
cmConn.CommandText = "Select * from My_Table "&"where ID = 3"
Set RS = cmConn.Execute
MsgBox RS.GetString

RecordSet

The RecordSet object is the most frequently used ADO object for manipulating the data of a database. The Connection object creates this object. You can create the RecordSet object independently of this.

Set RecSet = CreateObject("ADODB.Recordset")

A RecordSet object contains data sets from a table of a database. You can use the properties and methods of the RecordSet object to access the data sets.

Note  Not all drivers (providers) support all properties and methods of the RecordSet object.

Use the Open method to access data sets of a table or to access the result of a request.

RecSet.Open "My_Table", oDBConnection
RecSet.Open "Select * from My_Table "&"where Time>0.8", oDBConnection

The RecordSet object then refers to a data set (record) you can edit with methods such as AddNew, Delete, Find, GetString, and Update. The RecordSet object also has the methods MoveFirst and MoveNext, which you can use for moving within the data set. Use Fields Collection to read individual field entries of the current data set. Use the field name or the field number to access the arrays. The property EOF (end of file) contains the position of the bookmark within the data set and is TRUE when it reaches the end of the data set.

If not RecSet.EOF Then
  RecSet.MoveFirst
  Do While not RecSet.EOF
    Msgbox RecSet.Fields(2)
  ' or RecSet.Fields("Speed")
    RecSet.MoveNext
  Loop
End If

To release system resources close the RecordSet object after use:

RecSet.Close

Error

Use the Error object to handle errors. This object contains information such as the description and number of the error that occurred when the data was accessed. The Connection object contains a collection of Error objects. If an error occurs, one or more Error objects are saved in this collection. Because the Connection object controls the Error collection, you do not have to instance the Error objects. An Error collection is created for each existing connection. Make sure that you access the correct collection when you evaluate the error. The following example shows how an Error collection is searched:

Dim ErrConn
' Connect
' Send Request
' ...
If oDBConn.Errors.Count Then
For Each ErrConn in oDBConn.Errors
Msgbox "Description " & ErrConn.Description & _vbLf & "Number " & ErrConn.Number & _
vbLf & "Native Error " & ErrConn.NativeError & _vbLf & "Source " & ErrConn.Source & _
vbLf & "SQL State " & ErrConn.SQLState
Next
End If

After handling the error, use the Clear method to delete the Error collection:

If oDBConn.Errors.Count Then
...
oDBConn.Errors.Clear
End If

	Note  To find the Microsoft-ADO help on the internet search for the keyword ADO API Reference in MSDN. These pages contain the download of a current provider for the mdb database, for example, AccessDatabaseEngine_x64.
	Noe  Refer to ADO Example for a detailed example of how to use ADO. .