Join odbc sql recordset with text file recordset

[mariano42]

Hello-
I have a vbscript that queries a pervasive sql database and pulls out purchase order data and exports to a text file. That part is working great.

I have a second script that simply pulls one field from a flat text file that lists all of the different jobs that are active on a third party system (completely seperate db).

I need to be able to only pull purchase order data if the job record of the po equals an entry on the flat file. I have no clue how to combine the two recordset in one query, but I imagine it is possible.

Here is the code to pull the PO:
Set objCN = CreateObject("ADODB.Connection")
dim filesys, filetxt, getname, path
Set filesys = CreateObject("Scripting.FileSystemObject")
path = "c:\somefile.txt"

if filesys.FileExists(path) Then
Set filetxt = filesys.OpenTextFile(path,8)
Else Set filetxt = filesys.CreateTextFile(path, True)
end if

strConnection = "DSN=Timberline Data Source;Driver=Timberline Data;DBQ=C:\Documents and Settings\All Users\Application Data\Sage\TIMBERLINE OFFICE\Data\Construction Sample Data;CODEPAGE=1252"
objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "SELECT Job, Extra, Vendor, Commitment, Description, Date, Retainage_Percent, Amount FROM JCM_MASTER__COMMITMENT "


Dim objRS
Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)


Do Until objRS.EOF

filetxt.writeline("C" & "," & objRS.Fields("Job") & "," & objrs.fields("Extra") & "," & objrs.fields("Vendor") & "," & objrs.fields("Commitment") & "," & Chr(34) & objrs.fields("Description") & Chr(34) & "," & month(objrs.fields("Date"))&"-"& day(objrs.fields("Date")) & "-" &year(objrs.fields("Date")) & "," & objrs.fields("Retainage_percent")& "," & objrs.fields("Amount"))

objRS.MoveNext

Loop
objRS.Close

and here is the code for pulling from the text file:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objtxtconn = CreateObject("ADODB.Connection")
Set objtxtRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "C:\"

objtxtconn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

strFile = "book1.txt"


objtxtRecordset.Open "Select * FROM " & strFile ,objtxtConn, adOpenStatic, adLockOptimistic, adCmdText

Do Until objtxtRecordset.EOF
Wscript.Echo objtxtRecordset.Fields.Item("Project")

objtxtRecordset.MoveNext
Loop

any ideas?

Thanks!