none
ADODB for CSV, filename too long RRS feed

  • Question

  • I have a working ADODB model that is converting a .csv file to an array.  However, when the filename is too long, the macro will not work.  I have spent days trying to fix this.  The idea is to save time so renaming the file daily is not an option.

    Tuesday, September 15, 2015 7:32 PM

Answers

  • Hi Chase C,

    I am not able to reproduce this issue. If the file name is long enough, I would get the error like figure below:

    And the issue seems relative to the limitation for the ADO(length of SQL, file name), I suggest that you rename the file as a workaround.

    >>I have a working DAO connection that doesn't require me to truncate the file names, is there any reason to think ADODB would be faster?<<

    No. ADO is the successor to DAO/RDO. Functionally ADO 2.0 is most similar to RDO, and there's generally a similar mapping between the two models. ADO "flattens" the object model used by DAO and RDO, meaning that it contains fewer objects and more properties, methods (and arguments), and events. For example, ADO has no equivalents to the rdoEngine and rdoEnvironment objects, which exposed the ODBC driver manager and hEnv interfaces. Nor can you currently create ODBC data sources from ADO, despite the fact that your interface might be through the ODBC OLE DB service provider.

    You can get more detail about ADO and DAO from link below:
    ADO Compared with RDO and DAO

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 18, 2015 6:33 AM
    Moderator

All replies

  • Hi Chase Ca,

    What's the exact error message you got? Based on my understanding, there is an length limitation for the connection for the ADODB. If the error is cause by this limitation, there is no way to fix this issue except reducing the connection string.

    If you don't want to rename the file daily, you can try put the file to an folder which path is shorter than before. Also we can use code rename the file before connect the data using ADODB and change it back after finish manipulating the data.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, September 16, 2015 5:36 AM
    Moderator
  • Fei,

    Thanks for taking the time to answer my question.  The error message I receive is "cannot locate file..." but if I truncate the length of the filename, it opens fine (same directory, same file, slightly shorter name).  I believe the issue is from length of the .csv file name and not from the length of the directory name.  I know .csv files automatically save the tab name to be the same as the file name, but in this case the tab name is only 31 characters long (the maximum), could this be the reason the connection will not work?  I have a working DAO connection that doesn't require me to truncate the file names, is there any reason to think ADODB would be faster?  I would greatly appreciate your input.

    Best,

    Chase C

    Thursday, September 17, 2015 1:50 PM
  • Hi Chase C,

    I am not able to reproduce this issue. If the file name is long enough, I would get the error like figure below:

    And the issue seems relative to the limitation for the ADO(length of SQL, file name), I suggest that you rename the file as a workaround.

    >>I have a working DAO connection that doesn't require me to truncate the file names, is there any reason to think ADODB would be faster?<<

    No. ADO is the successor to DAO/RDO. Functionally ADO 2.0 is most similar to RDO, and there's generally a similar mapping between the two models. ADO "flattens" the object model used by DAO and RDO, meaning that it contains fewer objects and more properties, methods (and arguments), and events. For example, ADO has no equivalents to the rdoEngine and rdoEnvironment objects, which exposed the ODBC driver manager and hEnv interfaces. Nor can you currently create ODBC data sources from ADO, despite the fact that your interface might be through the ODBC OLE DB service provider.

    You can get more detail about ADO and DAO from link below:
    ADO Compared with RDO and DAO

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 18, 2015 6:33 AM
    Moderator