none
How can I prevent memo fields (long text) from being coverted to short text in linked/imported tables? RRS feed

  • Question

  • I have a VBA script that links data from Excel spreadsheets from a network folder based on user input.  They identify the job number, it builds a link to the correct folder and links the data. 

    The issue is one of the fields on the spreadsheet may contain up to 4000 characters, and therefore needs to be a memo (or long text) field.  The application was built using Access 2007 and worked fine - but now that we've upgraded to Access 2013 all the fields are being created as short text in the linked table.

    From what I've been able to find I could be able to solve the problem by sorting the data to ensure the longest strings are within the top 25 rows, and Access would then identify it as a long text field.  However we have up to 7 spreadsheets that need to be combined, multiple jobs may need to be run each week, and the team is spread across the state - so it's not practical to manually sort the data.   ...and most of the users don't know (and refuse to learn) how to use Access.

    Is there a way to control how Access defines the data types when the file is linked, automatically sort the data by string length before it's linked or imported, or to append the data from Excel to an existing table that's cleaned out of old data by VBA/SQL at the start of each run?

    Friday, October 7, 2016 4:36 PM

Answers

  • I understand what you're saying - but I think I missed a detail in the problem description.

    The intent is to use a single Access front end that can be pointed to any one of a multiple of data folders - each containing all the spreadsheets for a given job.  In addition to the short/long text issue - we have over 200 job folders with up to 7 workbooks (some with multiple sheets) and the user needs to be able to pull up any one of these at a given time.  Using the import wizard works, but the path changes with each job - and unless I can edit the path data for the import spec I'll need a new one for each job.

    However - I think I found the answer on Stackoverflow http://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import

    Basically it seems to be create the import spec using the wizard, then in the code use a replace command to substitute the new path (or sheet number) for the original, then execute the import spec. 

    • Proposed as answer by David_JunFeng Friday, October 14, 2016 9:41 AM
    • Marked as answer by David_JunFeng Monday, October 17, 2016 2:38 PM
    Friday, October 7, 2016 8:55 PM

All replies

  • Hi,

    Just a thought but have you tried creating an import spec to use when linking the Excel files to Access? You can define the specific data type for each field/column.

    Just my 2 cents...

    Friday, October 7, 2016 4:39 PM
  • I've been trying to - but having trouble finding (useful) instructions for 2013.  Any recommendations?
    Friday, October 7, 2016 6:21 PM
  • Hmm, did you try using the Wizard? I'll see if I can find a link for you.
    Friday, October 7, 2016 6:27 PM
  • Hi,

    Try this one and pay attention to steps 11 and 12.

    Hope it helps...

    Friday, October 7, 2016 6:32 PM
  • Already done that - but the issue is I need to be able to redirect it to a different folder at run time. 

    If I could create the import spec at run time I should be able to pull in a variable from a user input on a form to direct it to the right folder.  ...or if there's a way to edit the path for the spec created by the wizard, but I don't see that in any of the system files.

    Friday, October 7, 2016 6:43 PM
  • Hi,

    Not sure you understand what I'm trying to say. I thought you said the original problem is because the column is converted/imported as Short Text rather than Long Text. If so, what I am saying is use the Wizard to manually import the Excel file and specify the data type (Long Text) for the spreadsheet column. Then, at the end of the Wizard, check the box to "save the import steps as a specification." Give it a name you can remember.

    Then in your code for importing the Excel file, add the name of the import spec you created above. When Access imports the Excel file using the import spec where you specified to use Long Text, then the field should come out as a Memo field.

    Give it a try and let us know if it doesn't work.

    Just my 2 cents...

    Friday, October 7, 2016 7:01 PM
  • I understand what you're saying - but I think I missed a detail in the problem description.

    The intent is to use a single Access front end that can be pointed to any one of a multiple of data folders - each containing all the spreadsheets for a given job.  In addition to the short/long text issue - we have over 200 job folders with up to 7 workbooks (some with multiple sheets) and the user needs to be able to pull up any one of these at a given time.  Using the import wizard works, but the path changes with each job - and unless I can edit the path data for the import spec I'll need a new one for each job.

    However - I think I found the answer on Stackoverflow http://stackoverflow.com/questions/23350640/how-to-specify-a-different-file-path-for-a-saved-excel-import

    Basically it seems to be create the import spec using the wizard, then in the code use a replace command to substitute the new path (or sheet number) for the original, then execute the import spec. 

    • Proposed as answer by David_JunFeng Friday, October 14, 2016 9:41 AM
    • Marked as answer by David_JunFeng Monday, October 17, 2016 2:38 PM
    Friday, October 7, 2016 8:55 PM
  • Hi,

    Sounds like you may have your solution now. Good luck with your project.  

    Friday, October 7, 2016 11:34 PM