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:
Observations:
Here is a snapshot of the SQL Query & VBA code:
The connection string i am using is:
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:
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:
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:
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:
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 WithQuestions:
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:
Last edited:
