Excel VBA - "[Microsoft][ODBC Text Driver] Record is too Large." - Joining 2 csv file

[sifar786]

Excel VBA - "[Microsoft][ODBC Text Driver] Record is too Large." - Joining 2 csv file

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:

[Microsoft][ODBC Text Driver] Record is too Large.

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:

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:

    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??

Attached TEXT files. PLEASE SAVE AS Comma separated CSV:

 

[Uncle Gizmo]

Re: Excel VBA - "[Microsoft][ODBC Text Driver] Record is too Large." - Joining 2 csv

I noticed your question has yet to receive a reply so I am pumping it up the list so that it gets a second look - best of luck