I am joining 2 csv files using a `Transform` (Pivoting) query with `Microsoft Access Text Driver (ADODB)` in Excel 2016 using VBA. Both csv files are small i.e. 1 is not more than 10 rows and the other may be not more than 20 rows. The `Transform` query works fine with most csv files. I am only picking up 1 or 2 columns from both the joining files - even though the columns are just 10-15 columns.

However, recently with one of the joining files having 60 rows, i started getting an error:

HTML Code: 
Observations:
  • I saw that if i tried reducing the number of rows in one of the files from 60 rows to 30 rows, it works!
  • If i run the same query in `MSAccess`, it works fine even with one of the files having 60 rows.
  • I tried replicating scenario by choosing the `Microsoft Access Text Driver` and importing the 2 csv files in `MSQuery`, then running the `Transform query`, but got the same `Record is too Large` error.
  • I tried changing the ODBC driver connection string to `SQL ACE Engine`, but still the same.
  • Initially i thought it could be that the filepath length may be > 255 characters, but that is not the case. It is within the range.
  • Initially i thought it could be spaces in the filepath, but i have found a solution of enclosing filepath in `back-ticks`, which handles filepaths with spaces, if using the `Microsoft Access Text Driver` to join files.
  • Someone suggested to convert the `TextValue` field IN `File2.csv` containing long text (not greater than 80-100 characters) as a `MEMO field`. I am not sure how to convert it in a `SELECT TRANSFORM Query`.

Here is a snapshot of the SQL Query & VBA code.

PHP Code: 
TRANSFORM FIRST(A.[textValue]) AS TextValue  SELECT clng(G.[position]) AS [G-Value], 1 AS Theme,COUNT(clng(A.[position])) AS Var, G.[label] AS [Attribute Name] FROM `C:\ABC\DEF GHI\JKL MNO PQR\TEST 1\SUBTEST 1\2019\DOIT 1\1-2`\File1.csv AS G,`C:\ABC\DEF GHI\JKL MNO PQR\TEST 2\SUBTEST 2\2019\DOIT 2\1-2`\File2.csv AS A WHERE G.[geneid] = A.[geneid] GROUP BY clng(G.[position]),G.[label] ORDER BY clng(A.[position]) Asc PIVOT clng(A.[position]) 
The connection string i am using is:

PHP Code: 
    Set oCon = CreateObject("ADODB.Connection")
    strCon = "Driver=Microsoft Access Text Driver (*.txt, *.csv);DriverId=27;Dbq=" & sFullDirectory & ";Extensions=asc,csv,tab,txt;FIL=text"
    
    With oCon
        .cursorlocation = adUseClient
        .connectionstring = strCon
        .Open
    End With

    Set oRs = CreateObject("ADODB.Recordset")
    With oRs
        .cursortype = adOpenStatic
        .locktype = adLockBatchOptimistic
        .activeconnection = oCon
        .Open (strSQL)    '----> Error: Record is too Large.
    End With 
Questions:


How do i resolve this issue without revamping much of my code (I am using other `Select-Join` queries that depend on the Microsoft Access Text Driver)?

Is there a way to make the ADO query engine recognize the `TextValue` column as a `Memo field` in the `TRANSFORM-SELECT` Query itself? I don't want to create a temporary table using (MAKE or INSERT INTO) to change the data type.
e.g. TRANSFORM CMemo( FIRST(A.[textValue]) ) AS TextValue??

Images of the CSV files:

File1.csv (1 row only)

File2.csv (60 rows)