I have a suggested method for searching an inventory database with an external file join. The basic idea was:

SELECT U.partnum, M.partnum AS masternum,M.partname
From UploadedDataFile AS U INNER JOIN MaterData AS M
ON M.partnum = U.partnum
ORDER BY U.partnum

The UPloadedDataFile would be a text file .csv with partnumbers that would then join with the inventory database to select those records that matched the partnumbers in the text file.

This is a batch upload by parts managers to check an online database of parts to find a match. I have a current form where they key in a part number to check but some have many and want a batch input.

The problem is that I can't get the select to recognize the external text file for input.

I set up the following quick code test:
'code above this is to upload and write file to server using SAfileup.

Dim fsoObject 'FileSystemObject
Dim tsObject 'File Object
Dim strBuffer 'Read Buffer
Const ForAppending = 8
Const TristateTrue = -1
Const ForReading = 1
Const TristateFalse = 0

Set fsoObject = Server.CreateObject("Scripting.FileSystemObject")
Set tsObject = fsoObject.GetFile(strFile)
tsObject.OpenAsTextStream(ForReading,TristateFalse )
'test read/write to verify file and contents
strBuffer = tsObject.Readline
Response.Write(strBuffer)

Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_ARROWHEAD_STRING
Recordset1.Source = "SELECT U.partnum, M.Partnumber AS masterNum,M.partname FROM tsObject AS U INNER JOIN hawker.Summarytest AS M ON M.partname = U.partnum ORDER BY U.partnum"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

The only real documentation that I could find on this was in a FORM table IN file example and a reference to jet ability to connect to external non-native in a form of 'file;'

This is on MSSQL 7

TIA