Query from text file and retrieve records using ADODB

Query from text file and retrieve records

For example we have the following text file “Employees.txt”

LastName,FirstName,Department,Role
Nandvani,Rajiv,IT,Manager
Kumar,PP,Finance,Analyst
Shukla,Nikhil,R&D,Programmer
Singh,ANIL,QA,Tester
ARORA,ANUJ,QA,Team Leader
Kumar,Neeraj,IS,Manager

Is it important that your text files be formatted like this? It’s not just important,
it’s crucial. We’re going to use database techniques to retrieve information from
the file, and to do that the file needs to be delimited in some way (in this case,
using the comma as the delimiter).



Option Explicit
Const adStateOpen = 1
Const adOpenStatic = 3REM adOpenStatic 3 A static cursor allowing forward and backward scrolling of a
REM fixed, unchangeable set of records
Const adLockOptimistic = 3REM adLockOptimistic = 3 means Multiple users can modify the data which is not locked
REM until Update is called
Const adCmdText = &H0001
Dim oConn, oRst
Dim sFile, sPath, sSQL, sOut
Set oConn = CreateObject("ADODB.Connection")
Set oRst = CreateObject("ADODB.Recordset")
sPath = "D:\auto" : sFile = "Employees.txt"
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited"""
sSQL = "Select * FROM " & sFile & " where Department = 'QA'"
oRst.Open sSQL,oConn, adOpenStatic, adLockOptimistic, adCmdText
    Do Until oRst.EOF
     sOut = oRst.Fields.Item("LastName").Value & vbCrLf
     sOut = sOut & oRst.Fields.Item("FirstName").Value & vbCrLf
     sOut = sOut & oRst.Fields.Item("Role").Value & vbCrLf
     MsgBox sOut
     oRst.MoveNext
    Loop 
'--- Close the recordset if opened.
If oRst.State = adStateOpen Then oRst.Close
 '--- Close the connection if opened.
If oConn.State = adStateOpen Then oConn.Close