Joining a sql recordset with a text file recordset in one query

Author	Message

mariano42

Total Posts : 2
Scores: 0
Reward points : 0
Joined: 1/26/2012
Status: offline

Joining a sql recordset with a text file recordset in one query Thursday, January 26, 2012 2:43 AM (permalink)

0

[Helpful answer received] / [List Solutions Only]

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 separate db).

I need to be able to only pull purchase order data if the job field of the po record 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!

#1

59cobalt

Total Posts : 975
Scores: 91
Reward points : 0
Joined: 7/17/2011
Status: offline

Re:Joining a sql recordset with a text file recordset in one query Monday, January 30, 2012 12:24 AM (permalink)

0

[This post was marked as helpful]

You cannot run a single query against disparate databases. Collect the results from the flat file query into an array and try changing the database query to something like this:

strSQLQuery = "SELECT Job, Extra, Vendor, Commitment, Description, Date, Retainage_Percent, Amount " _
 & "FROM JCM_MASTER__COMMITMENT " _
 & "WHERE Job IN ('" & Join(arrProject, "','") & "')"

#2

mariano42 Total Posts : 2 Scores: 0 Reward points : 0 Joined: 1/26/2012 Status: offline	Re:Joining a sql recordset with a text file recordset in one query Monday, January 30, 2012 2:53 AM (permalink) 0 Thank you.. I ended up setting it up with a loop within a loop and just passed the project/job variable from the outer loop to the inner loop. That did the trick.
	#3