|DIAdem 2017 Help|
|DIAdem 2018 Help|
|DIAdem 2019 Help|
|DIAdem 2020 Help|
|DIAdem 2020 SP1 Help|
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.
Use the ADO objects Connection, Command, RecordSet, and Error to program databases.
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 Connectionobject to carry out Commitactions and Rollback actions. To disconnect the database use oDBConn.Close This command also closes the data sets opened through the connection.
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
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:
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. .|