In this article, I will show you how to read and update an MS Access Database using an ADODB connection in UFT/QTP. To understand it practically with example we will be using an Employee Table.
Let’s say we have a database named “UFTTestDB”. In that database, there is a table “Employee” that contains the employee details of an organization as shown below.
In order to connect MS Access Database using ADODB, we need to have a Connection String that would establish a connection between UFT and MS Access Database. We are going to use a connection string that is supported by both the older version(.mdb) and the latest version(.accdb) of MS Access Database.
Reading MS Access Database Using ADODB
We can use the following code to read the MS Access Database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
strFileName = "E:\UFT_WorkSpace\TestData\UFTTestDB.accdb" 'Path of the MS Access File Dim objCon, objRecSet 'Create an ADODB connection Set objCon= CreateObject("ADODB.Connection") Set objRecSet = CreateObject("ADODB.Recordset") 'Open connection objCon.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & strFileName & ";Uid=Admin;Pwd=;" 'Check if Error then Exit Function If Err <> 0 Then Reporter.ReportEvent micFail, "Create Connection", "Error occurred while opening Connection. Error : " & Err ExitTest End If 'Execute SQL and store results in reocrdset strSQL = "Select * from Employee" objRecSet.Open strSQL, objCon While objRecSet.EOF = False MsgBox objRecSet.Fields("EMPName")'Store the value in a variable MsgBox objRecSet.Fields("Department")'Store the value in a variable MsgBox objRecSet.Fields("Salary")'Store the value in a variable objRecSet.moveNext Wend If Err <> 0 Then Reporter.ReportEvent micFail, "Open Recordset", "Error has occured while reading data.Error Code : " & Err ExitTest End If 'Close and nullify all object variables ' Set objRecSet= Nothing |
Updating MS Access Database Using ADODB
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
strFileName = "E:\UFT_WorkSpace\TestData\UFTTestDB.accdb" 'Path of the MS Access File Dim objCon, objRecSet 'Create an ADODB connection Set objCon= CreateObject("ADODB.Connection") 'Open connection objCon.Open "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & strFileName & ";Uid=Admin;Pwd=;" 'Check if Error then Exit Function If Err <> 0 Then Reporter.ReportEvent micFail, "Create Connection", "Error occurred while opening Connection. Error : " & Err ExitTest End If 'Execute SQL and store results in reocrdset strSQL = "Update Employee Set Salary = 12500 where EmpName='Kelvin'" objCon.Execute strSQL 'Command to fire update query objCon.Close Set objCon = Nothing |
This is how the table will look after the execution of the code. The salary of the employee will be updated in the Employee table.
Recommended Posts
- Read, Write and Update Excel File In UFT
- File System Object UFT | VBA
- All You Need to Know About Object Identification in UFT
- Descriptive Programming in UFT with Examples
- DataTable in UFT One | Example of Datatable Methods
- Most Useful VBScript Functions That You Must Know
- Read and Update XML File in UFT | VBA
- Four Kinds of Loops in UFT | VBScripting
- VBScript MySQL Database Connection in UFT
- 20 VBA Date Functions in UFT That You Must Know
