Open a recordset with a Join on two FixedLength text files

Openning one txt file using ADO and a schema.ini file, which describes the record layout...


Dim Cnn As ADODB.Connection
Dim strFolder As String
Dim rst As ADODB.Recordset
Dim SQL As String

strFolder = "C:\DataFiles\"

Set Cnn = New ADODB.Connection
Cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFolder & ";" & _
"Extended Properties = ""TEXT;HDR=No;FMT=FixedLength"""
Cnn.Open

SQL = "SELECT * FROM Products.txt"
Set rst = New ADODB.Recordset
rst.Open SQL, Cnn
...


And what if you need a join on two txt files?
Simply modify the sql statement


SQL="SELECT [Products#txt].[ID1], " & _
"[Products#txt].[ID2], " & _
"[Products#txt].[Balance], " & _
"Sum([SubProducts#txt].[Amount]) " & _
"FROM Products.txt INNER JOIN SubProducts.txt ON " & _
"([Products#txt].[ID1] = [SubProducts#txt].[ID1]) AND " & _
"([Products#txt].[ID2] = [SubProducts#txt].[ID2])" & _
"GROUP BY [Products#txt].[ID1], " & _
"[Products#txt].[ID2], " & _
"[Products#txt].[Balance];"

Or

SQL="SELECT t1.ID1, " & _
"t1.ID2, " & _
"t1.Balance, " & _
"Sum(t2.Amount) " & _
"FROM Products.txt As t1 INNER JOIN SubProducts.txt As t2 ON " & _
"(t1.ID1 = t2.ID1) AND " & _
"(t1.ID2 = t2.ID2)" & _
"GROUP BY t1.ID1, " & _
"t1.ID2, " & _
"t1.Balance;"