+ Reply to Thread
Results 1 to 31 of 31

Overcoming ADO Recordset 64 Characters limitation for Fields

  1. #1
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Overcoming ADO Recordset 64 Characters limitation for Fields

    I have some aphanumeric data in an Excel Sheet of a Closed Excel Workbook named temp.xlsx. I am creating an ADODB Recordset by an SQL Query to pull this range into my current Workbook.

    The problem i am facing is that some of the column headers in the Sheet are more than 64 characters in length. This results in the ADO field names getting truncated to 64 characters once i import the range into an ADO recordset.

    Please Login or Register  to view this content.

    Is there an alternative method to ensure that the full column name characters gets imported into the ADO Recordset from a CLOSED Excel Workbook i.e. overcoming the 64 character limitation of Fields?

    Can an ADO.STREAM object do the job maintaining the column names OR Can an MSXML2.DOMDocument.Load File or MSXML2.DOMDocument.LoadXML do the trick?

    Any help would be most appreciated.

    https://stackoverflow.com/questions/...ion-for-fields

    http://www.vbaexpress.com/forum/show...ion-for-Fields
    Last edited by junoon; 02-15-2020 at 11:06 AM.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Cross-Post @ https://stackoverflow.com/questions/...ion-for-fields

    BTW, did you try using "HDR=No" instead of "HDR=Yes"

    In this way, you can get the headers as data from the recordset...

    .
    Last edited by Haluk; 02-14-2020 at 05:13 PM.

  3. #3
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    @Haluk, Yes but i need the headers, as the headers text is used to manipulate and pull the recordset data into a ListObject table. I cannot have "F1", "F2"...."Fn" OR "Field1", "Field2"...."Fieldn" as the headers. Also the number of Header columns may vary everytime.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Administrative Note:

    Welcome to the forum.

    We would love to help you with your query; however, it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly.

    Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    HTH
    Regards, Jeff

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    @junoon, may be you can try the following Excel4 macro approach ....

    Please Login or Register  to view this content.
    .
    Last edited by Haluk; 02-15-2020 at 03:21 PM.

  6. #6
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Thanks @Haluk. You piqued my interest. A very Out of the Box solution!

    The code however misses out the last row and last column. I think somehow COUNTA is not calculating the counts correctly. BTW, I dont have any blank rows or columns in my current data.

    P.S: Though not denying i may have some blank rows and columns in some other csv data. So the code has to take that also into account.
    Last edited by junoon; 02-15-2020 at 03:56 PM.

  7. #7
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Can you attach a sample file ?

    .

  8. #8
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    @Haluk see attached a sample.xlsx file.
    Last edited by junoon; 02-16-2020 at 01:14 AM.

  9. #9
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    I see that since my Column1 (C1) and Row1 (R1) has a blank, i think COUNTA is not counting that as expected.
    Also it tends to add 0's to cells that are blank. So all the columns containing Letters and blanks are added 0's, i.e. the columns prefixed with "-Testing".

  10. #10
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Well, the "ADO" approach with the option "HDR=No" seems doing the job with the below code....

    Please Login or Register  to view this content.
    .

  11. #11
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    I can do that, but i need to exclude the "-Testing" columns from my ADO Recordset. How do i do that if i keep HDR=NO?

  12. #12
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    If you don't want the columns with ".....-Testing" then you can use the below code;

    Please Login or Register  to view this content.
    .
    Last edited by Haluk; 02-15-2020 at 04:38 PM.

  13. #13
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    But this recordset is not getting affected. You are dumping the data to sheet and deleting the columns. Instead, I needed recordset containing data excluding the "-Testing" columns.

    EDIT:
    If i use HDR=NO, I cannot do JOINS as the Headers are required to be known to perform joins between different CSV data. :-(
    Last edited by junoon; 02-16-2020 at 06:47 AM.

  14. #14
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    OK..... a final RecordSet excluding the ".......-Testing" columns will be created with the macro below;

    I hope this one will help you...

    Please Login or Register  to view this content.
    Last edited by Haluk; 02-16-2020 at 04:21 AM.

  15. #15
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Yes, i have tried the XML approach as shown in my original post but it only works for a Sheet Range of an Open workbook. The only problem with this approach (HDR=No) is that Field names are no longer identifiable and therefore cannot do JOINS. Hope you are getting my point.

    @Haluk, one thing i have observed with the LoadXML approach is that when we create a Recordset with it, If we open the Connection once again and then do the LoadXML and then create the Recordset, it strangely allows the Recordset Fields to have > 64 characters. I think it somehow changes the datatype of the Recordset Field to hold more than 64 characters, but not sure.

    If the above is true - since i only have an example of LoadXML loading data from another sheet range in an Open workbook, i was thinking if LoadXML has an option to load a Sheet range from a Closed Workbook, just like a normal ADO recordset would do, but with the ability to hold >64 characters.

  16. #16
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    As far as I understand from your attached sample file in your message #8; you want to get columns only containing numeric datas. In other words; the columns with headers "......-Testing" contain string type of datas and you don't want to fetch data from these columns.

    So; the code below satisfies these requirements when tested on your "deleted" sample file where the column headers are also retrieved, by using the XML properties of the sample file. While getting the numeric datas, XPath is used as you can see...

    Before trying the code, make sure to backup your (sample) data file.....

    Please Login or Register  to view this content.
    Last edited by Haluk; 02-16-2020 at 05:17 PM.

  17. #17
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Hello @Haluk,

    Awesome!!!

    I was working on similar lines using the XMLDocument and XPath but facing some difficulties w.r.t different XML formats (XML String, XML from Range.Value, XML File from ADO Recordset etc.). :-)

    Below i have excluded code for excluding columns having "-Testing" for now.

    Please Login or Register  to view this content.

    The interesting thing that i see from your code is `Adding` the temp.xlsx workbook, using on default Sheet1 Range.Value(xlRangeValueXMLSpreadsheet) and grabbing Headers & Rows separately using XPath.


    Q. Will the Workbook added give the same XML Element nodes everytime so that XPath does not fail? The reason why i am asking this is:

    When i load a Range from an Excel worksheet using Range.Value(xlRangeValueXMLSpreadsheet) to an ADO Recordset and then convert it to XML ( to manipulate its header columns i.e. `rs:name`), the XML format is different than the XML that i get when i `Save` an ADO Recordset (with HDR=No) to an `MSXML2.DOMDocument` object.


    Q. A Question that remains in mind: Is there a way to use this "XML Magic" to create an ADO Recordset from 2 different tables in closed workbook using SQL JOINS

    OR

    an ADO Recordset from 2 different CSV files joined together by an SQL Query JOIN ?


    I am asking because most of the times when i create reports using data from a remote workbook or CSV files, i create the report based on multiple data tables joined together using ADO Queries.


    How I wish Microsoft had increased the hard limit of 64 characters to atleast 255, as most of our Survey data have huge column names. :-(
    Last edited by junoon; 02-17-2020 at 09:24 AM.

  18. #18
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Related to your first question; the XML structure of the sheet named "Sheet1" of your data file is being analysed with my suggested code.

    And by using XPath, the headers are filtered and the cells which are housing only numeric values are fetched.

    Having said this; this code will run as expected on any other data file as long as the file contains a sheet named "Sheet1" and layout of this sheet is same as your given sample file before.... (such as headers and data types of the cells) which is expected though.

    And, as per your second question .... because of the fact that; an ADO recordset field name cannot be longer then 64 characters, the only thing left is either to use HDR=No option and use default field names (F1, F2....)

    .
    Last edited by Haluk; 02-17-2020 at 09:29 AM.

  19. #19
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    And, as per your second question .... because of the fact that; an ADO recordset field name cannot be longer then 64 characters, the only thing left is either to use HDR=No option or use default field names (F1, F2....)
    True. Is it possible to e.g., Workbooks.Add or Workbooks.Open a temp.xlsx or sample.xlsx Workbook which say, contains ranges in different sheets. Since this workbook is open, we create a Listobject or Querytable by using a "SELECT....JOIN Query" that joins the two sheets together? Can a Query Table or Listobject be created using an SQL Query like this, without creating an intermediate ADO Recordset?

    The reason i am asking is, when i add a new workbook, and then use Querytable to select single CSV files (using `TEXT` as connection), then the resulting Querytable gets created without a recordset and it contains all the long Headers intact. The only problem is i don't know how to specify a SELECT JOIN query between 2 csv files to create a single Querytable or Listobject without creating an ADO Recordset in the interim.

  20. #20
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    So, are you basically asking if 2 or more ranges on different sheets (or different workbooks) can be analysed by Excel+VBA+ADO/SQL (using Left Join ....) with a single SQL statement once, and return a result report ?

    If this is the question, the answer is YES (excluding the charactes limit in headers).

    .

  21. #21
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Yes. 2 or more worksheets in open workbook joined using a SELECT JOIN query (without any ADO Recordset usage) to create a resultant ListObject table or QueryTable.

    for e.g.

    Please Login or Register  to view this content.
    Last edited by junoon; 02-17-2020 at 02:12 PM.

  22. #22
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    junoon;

    I have given 3 files in the attached ZIP folder where the files named Source1.xlsx and Source2.xlsx files are data files. The 3rd file (Test.xlsm) is our main file housing the VBA code, which gets the union of the datas in the other workbooks by using SQL and putting the result in a ListObject on a new sheet.

    In this query code, ADO and Recordset is not used as per your request in message #21.

    Hope, the code gives you some idea so that you can play with the SQL string to suit your needs.
    Attached Files Attached Files
    Last edited by Haluk; 02-17-2020 at 06:22 PM.

  23. #23
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Once again Awesome @Haluk!

    Just a question, if i have a temp2.xlsx file which has 2 sheets containing ranges that i want to select join on 2 ID's (without ADO) and create listobject in that temp2.xlsx file by adding a temporary sheet (as you showed earlier), what part of this code do i need to modify in order for it to make it work? BTW the SQL Query that i am using is not a UNION ALL. This code to access the temp2.xlsx file will be run from my current workbook (ThisWorkbook).

    e.g.

    Please Login or Register  to view this content.
    I also tried by creating the Listobject in the temp2.xlsx workbook itself. But same errors!

    Please Login or Register  to view this content.

    Errors that i got:

    1st error : When i ran the code, the first error i got in both above cases:

    Unexpected error.PNG

    2nd error : When i close the above error, i get the General ODBC error.

    General ODBC Error.PNG

    The above Error happens on this line:

    Refresh Background Query.PNG

    3rd error : And When i manually refresh the table in the Sheet, i get the following error.

    Too Few Parameters.PNG
    Last edited by junoon; 02-18-2020 at 04:24 AM.

  24. #24
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    junoon;

    Check the attached ZIP folder contents for a "Inner Join" procedure on the external workbook named Temp.xls which contains 2 different tables on Sheet1 and Sheet2.

    ADO and RecordSet is not used .... as per your request.

    The query is running on the external data file ("Temp.xlsx") and the result of the query is dumped on a ListObject placed in a new sheet in this external file. That is; nothing is being written on the main file housing the VBA code.

    Hope this will give you a rough idea ...
    Attached Files Attached Files
    Last edited by Haluk; 02-18-2020 at 06:21 AM.

  25. #25
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Hi @Haluk,

    Yes, i made it to work with my own query and tables, but still getting that 1st error recurrently on the ".CommandText" line. When i click "Close" button, the table data gets loaded. Not sure why i am getting this error or what is its solution. Googling since morning with no results!

    Please Login or Register  to view this content.
    Alas! it seems my original idea of getting the full column header names backfires. They still get truncated after 64 characters! :-(

    I wish there was some setting in the Connection Driver to allow >64 characters column names.

    I think it would be futile to ask for more help when we have exhausted all resources! THanks a bunch @Haluk!
    Last edited by junoon; 02-18-2020 at 07:16 AM.

  26. #26
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    It seems it is not only ODBC but other drivers that also have this 64 characters hard limit. Though according to below link DBase Drive does not show any hard limit, except when column characters are ASCII value >=127. But can a DBase Driver be used with Excel?

    https://docs.microsoft.com/en-us/sql...l-server-ver15

    My inference:
    • If you are pulling a single CSV file using QueryTable or Listobject, the column character length is maintained.
    • If you are using a SELECT Query on single Excel Sheet and pulling data into a QueryTable or ListObject, the column characters are truncated after 64 characters.
    • If you are using a SELECT JOIN Query on 2 or more Excel Sheets and pulling data into a QueryTable or ListObject, the column characters are truncated after 64 characters.
    • An ADO Recordset always truncates column names after 64 characters.
    Last edited by junoon; 02-18-2020 at 07:48 AM.

  27. #27
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Actually, if you use the SQL statement in the attached ZIP file, the fields having field names greater then 64 characters are also imported ...

    The difference with respect to the code in my previous message is that; all the fields in the querry is retrieved because of using ( Select * from....)

    If the results satisfy your needs, you can try using it...
    Attached Files Attached Files
    Last edited by Haluk; 02-18-2020 at 08:37 AM.

  28. #28
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    @Haluk, i ran your code and as you can see the attached output, i am still getting columns truncated. Also i did not understand what you meant by

    The difference with respect to the code in my previous message is that; all the fields in the querry is retrieved because of using ( Select * from....)
    Attached Files Attached Files

  29. #29
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    OK, it was my bad .... header names are truncated.

    Well, i guess the only option left is opening the data file and do a conventional For-Next loop to get the desired values...

  30. #30
    Forum Contributor
    Join Date
    03-07-2006
    Location
    India
    MS-Off Ver
    MSOffice 365 Professional 64 bit
    Posts
    108

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    Thanks for your patience and helping me.

    A last question:

    Can a DBase IV or 5.0 ODBC driver be used to connect to an Excel Workbook?

  31. #31
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,134

    Re: Overcoming ADO Recordset 64 Characters limitation for Fields

    DBase drivers are used for DBase files (*.dbf) and can not be used in Excel files.

    ODBC connections are used already, in the examples I've given to you in messages #22 and #24.
    Last edited by Haluk; 02-18-2020 at 10:08 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy limitation to 255 characters - problem
    By Ted1104 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-29-2020, 11:13 AM
  2. [SOLVED] Automatic limitation of the number of Characters in cells
    By george.m in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2017, 05:30 AM
  3. Trailing Characters/Space when querying via ADODB.recordset getrows
    By Jay-T in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-27-2016, 08:53 AM
  4. Open another recordset within current recordset vba
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-02-2015, 04:32 AM
  5. [SOLVED] Looks at 2 fields and If one doesnt contain certain characters then set to 0.
    By DebbieEdwards in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 05-29-2013, 07:39 AM
  6. Creating hyperlink from two recordset fields
    By Tommyp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2010, 09:56 AM
  7. Recordset's RowID as part of Recordset
    By Trip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2005, 06:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1