Dump all records of a table to a xml file

Dim MyConn, SQL_query,ResultSet,myName

'Open database connection
Set MyConn = CreateObject("ADODB.Connection")
MyConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\emp.mdb"

'Define the SQL query
SQL_query = "SELECT * FROM employee"

'Execute the query and store results in ResultSet
Set ResultSet = MyConn.Execute(SQL_query)
ResultSet.MoveFirst

Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\testfile.txt", True)
' Write a line with a newline character.
tf.WriteLine("<xml>")

While NOT ResultSet.EOF
tf.WriteLine("<employee>")
For each x in ResultSet.fields
tf.WriteLine(addXmlTag(x.name,x.value))
next
tf.WriteLine("</employee>")
ResultSet.MoveNext
WEND
tf.WriteLine("</xml>")

'Release resources
ResultSet.close
MyConn.close

Function addXmlTag(colName,colValue)
startTag ="<"
endTag=">"
addXmlTag = startTag & colName & endTag & colValue & startTag & "/" & colName & endTag
End Function

Dump XML output to a file

'colName is hard-coded here

Dim MyConn, SQL_query,ResultSet,myName

'Open database connection
Set MyConn = CreateObject("ADODB.Connection")
MyConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\sangeeta\emp.mdb"

'Define the SQL query
SQL_query = "SELECT emp_name FROM employee"

'Execute the query and store results in ResultSet
Set ResultSet = MyConn.Execute(SQL_query)
ResultSet.MoveFirst

Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\testfile.txt", True)
' Write a line with a newline character.
tf.WriteLine("<xml>")

'Read results from ResultSet in a loop
While NOT ResultSet.EOF
Set name1 = ResultSet("emp_name")
tf.WriteLine(addXmlTag("emp_name",name1))
ResultSet.MoveNext
WEND
tf.WriteLine("</xml>")

'Release resources
ResultSet.close
MyConn.close

Function addXmlTag(colName,colValue)
startTag ="<" endTag=">"
addXmlTag = startTag & colName & endTag & colValue & startTag & "/" & colName & endTag
End Function

Create new XML document

Dim MyConn, SQL_query,ResultSet,myName

'Open database connection
Set MyConn = CreateObject("ADODB.Connection")
MyConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\emp.mdb"

'Define the SQL query
SQL_query = "SELECT emp_name FROM employee"

'Execute the query and store results in ResultSet
Set ResultSet = MyConn.Execute(SQL_query)
ResultSet.MoveFirst

xmlResult = ""
'Read results from ResultSet in a loop
While NOT ResultSet.EOF
Set name1 = ResultSet("emp_name")
xmlResult= addXmlTag("emp_name",name1,xmlResult)
xmlResult = xmlResult & VBNullString
MsgBox xmlResult
ResultSet.MoveNext
WEND
xmlResult = xmlResult & VBNullString & ""
MsgBox xmlResult
'Release resources
ResultSet.close
MyConn.close

Function addXmlTag(colName,colValue,xmlResult)
startTag ="<"
endTag=">"
xmlResult = xmlResult & startTag & colName & endTag & colValue & startTag & colName & endTag
addXmlTag = xmlResult
End Function

Read data from a table

t=gettabledata("x.com: Your Account","x.com: Your Account Page","MyTable")

Function gettabledata(strBrowser,strPage,strTable)
Set objPage = Browser("name:="&strBrowser).Page("title:="&strPage)
Set bishBoshTable = objPage.WebTable("html id:="&strTable)
rowcount = bishBoshTable.RowCount()
colcount = bishBoshTable.ColumnCount(2)

ReDim preserve strCell((rowcount-1)*colcount)
MsgBox("Row Count"&rowcount)
MsgBox("Col Count"&colcount)
k=0
For i = 2 to rowcount
For j= 1 to colcount
temp = bishBoshTable.GetCellData(i,j)
'if temp == empty, then continue
if(Not IsEmpty(temp)) Then
strCell(k) = temp
myString = myString + strCell(k)
k=k+1
End If
Next
Next
MsgBox(myString)
If Err.number > 0 Then
Err.Clear
End If

End Function

Connect to Microsoft Access database in QTP

Here are the steps to connect to microsoft access database in QTP.

'Declare the variables
Dim MyConn, SQL_query,ResultSet,myName

'Open database connection
Set MyConn = CreateObject("ADODB.Connection")
MyConn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\temp\winrunner_db.mdb"

'Define the SQL query
SQL_query = "SELECT Name FROM Employee"

'Execute the query and store results in ResultSet
Set ResultSet = MyConn.Execute(SQL_query)
ResultSet.MoveFirst

'Read results from ResultSet in a loop
While NOT ResultSet.EOF
Set name1 = ResultSet("name")
ResultSet.MoveNext
WEND

'Release resources
ResultSet.close
MyConn.close