Consulting

Results 1 to 14 of 14

Thread: Overcoming ADO Recordset 64 Characters limitation for Fields

  1. #1

    Overcoming ADO Recordset 64 Characters limitation for Fields

    I have some aphanumeric data in an Excel Sheet of a Closed 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.

        Public Const adCmdText = 1
        Public Const adOpenDynamic = 2
        Public Const adUseServer = 2
        Public Const adOpenStatic = 3
        Public Const adUseClient = 3
        Public Const adLockBatchOptimistic = 4
        Public Const adTypeBinary = 1
        Public Const adTypeText = 2
    
    
    '    Dim oStream As Object
    '    Dim oCmd As Object
    
        Dim oCon As Object
        Dim oRS As Object
        Dim strSQL As String    
        Dim strFilePath As String    
    
    '    Set oStream = CreateObject("ADODB.Stream")
    '    Set oCmd = CreateObject("ADODB.Command")    
       
    '    Set xlXML = CreateObject("MSXML2.DOMDocument")
    '    Set XL = GetObject(, "Excel.Application")
    '    Set WB = XL.ActiveWorkbook
    '    xlXML.LoadXML Replace(WB.Sheets("Sheet1").Cells(1, 1).CurrentRegion.value(xlRangeValueMSPersistXML), "rs:name="" ", "rs:name=""") 'xlRangeValueXMLSpreadsheet
    '    oRS.Open xlXML, CursorType:=adOpenStatic, LockType:=adLockBatchOptimistic
    
        Set oCon = CreateObject("ADODB.Connection")    
        Set oRS = CreateObject("ADODB.Recordset")    
    
       strFilePath = ThisWorkbook.Path & Application.PathSeparator & "temp1.xlsx"        
       With oCon        
           .Provider = "Microsoft.Ace.OLEDB.12.0"        
           .Properties("Extended Properties") = "Excel 12.0; HDR=Yes;IMEX=1;"        
           .CursorLocation = adUseClient        
           .Open strFilePath    
       End With        
       
       strSQL = "SELECT * FROM [Sheet1$]"    
       With oRS        
           .CursorType = adOpenStatic        
           .CursorLocation = adUseClient        
           .LockType = adLockBatchOptimistic        
           .activeconnection = adoCon        
           .Open (strSQL)    
       End With
    End Sub
    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 .LoadXML do the trick?

    Any help would be most appreciated.

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    Why do you need more then 64 characters to identify a column/field ?

    Use the simpler code in: https://www.snb-vba.eu/VBA_ADODB_recordset_en.html

  3. #3
    @snb the columns are like this in the closed workbook :

    NonEMI_Segment_5_UPDATED_Non_Frequent_Legal_Carolisan_Mastroska_Users | NonEMI_Segment_5_UPDATED_Non_Frequent_Legal_Carolisan_Mastroska_Users-Testing
    123 | ACDF
    98 | BCD
    76 | FGI
    After getting the data into an ADO Recordset, I have to find the Field names containing "-Testing" by looping and checking with "INSTR" and remove these columns as these are Stat Testing columns that i dont wish to import. Just note the above Column names are different everytime and their length also varies everytime. The only way to remove Stat testing columns is by the word "-Testing" in them. So right now, the ADO recordset truncates the column name to only 1st 64 characters. So no way to identify "-Testing" present in column or not. Therefore the Stat Testing column also gets picked up and added to the ListObject. I hope you understand.

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    Why don't you use:

    PHP Code:
    Sub M_snb()
      
    with getobject("G:\OF\export.xlsx")
         .
    sheets(1).rows(1).replace"*-*","")
         .
    sheets(1).rows(1).specialcells(4).entirecolumn.delete
         
    .close -1
      end with
    End sub 
    to remove/delete the unwanted columns.

    Why would you use ADODB ?

  5. #5
    @snb the above code gives :

    Compile error : 
    Syntax error =
    BTW I meant i am removing the Stat Testing columns from ADO recordset (after loading the data from the temp.xlsx workbook sheet) by looping the Recordset.Fields and testing them using INSTR function. Sorry, for the confusion.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,840
    What version of Excel are you using?
    You should be able to use Get & Transform data or its equivalent depending on your version.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    I am using Excel 2016 Pro 64 bit on Windows 10 Enterprise 64 bit with 8 GB RAM.

    If you mean Power Query, the reason i am not using it is because :

    - i don't yet know much about using VBA to code Power Query transformation steps.
    - most of the people who are going to use my tool, are on earlier versions of issue viz., Excel 2010/2013 32 bit. So code backward compatibility is an issue w.r.t Power Query.

  8. #8
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    In that case you should refrain from using ADODB as well.

  9. #9
    @snb ADODB works fine on Excel 2010/2013 and i usually check the Application.Version to know whether to load Jet or Ace provider. No issues. But the issue i suddenly faced is when one of the csv files for a particular Client Study happened to exceed 64 character limit of Jet/Ace. So trying to find ways to overcome this issue without affecting much of my other code. I have another issue of importing Images from a closed workbook (posted here) that i need to tackle before i can move on to creating the last report which requires both the csv data and images.

    So trying to find a best solution to close this issue quickly!

  10. #10
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    You still didn't answer my question: why using ADODB ?

  11. #11
    @snb i use ADODB as i dont want to open csv files, copy and then paste to my current workbook. ADODB is fast, allows some filtering, sorting and makes it easier to import data from a Closed file. I hope that makes sense.

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,074
    Location
    @snb i use ADODB as i dont want to open csv files, copy and then paste to my current workbook. ADODB is fast, allows some filtering, sorting and makes it easier to import data from a Closed file. I hope that makes sense.
    But... AODB doesn't work with your files.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    @SamT where did i say it doesn't work or gives an error? The issue is for certain csv data (which i came across issue recently), having column characters >64 characters, it truncates the column names. I thought the post Title was quite obvious.

  14. #14
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,074
    Location
    This results in the ADO field names getting truncated to 64 characters
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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