How to force Access to scan entire imported file to determine field type?

Posted on 2011-10-21
Last Modified: 2012-05-12
I am importing Excel worksheets into an existing Access table and am having problems with the text being imported into a memo field being truncated at 255 characters.  I think this is because Access scans the first 25 characters of the imported field to determine the field type and many of the Excel files being imported have short text fields in the first 25 rows so Access determines that it should import the field as a text field rather than a memo field.  I seem to recall there being a way to force Access to scan the entire Excel file during the import to determine field type (not just the first 25 rows), but I can't remember how that was done.  Am I correct in recalling that is possible, and if so, how is it done?
Question by:marknlynn3
    LVL 8

    Expert Comment

    by:Amit Khilnaney
    Enclosing image from following resource..

    You need to select the data type accordingly

    For more See this

    Author Comment

    I am importing data into an existing table so the field types have already been defined, and they can't be altered with the import wizard.  Access is scanning the first 25 rows during input to determine field types and even though the field it is importing to is defined as a memo field, it determines that since the first 25 rows had text fields of length 255 characters or less that all the records must be less than or equal to 255 characters and it truncates records with more text than that.  I believe there is a Access setting that can be changed so that Access evaluates the entire file when determining field types during import.  That's what I'm searching for.  Otherwise, I will have to move a record with a lot of text to be the first record in each Excel file to be imported, which I would prefer not to have to do for each imported file.
    LVL 8

    Assisted Solution

    by:Amit Khilnaney
    Can't say how to do this

    but this is by design as per Microsoft

    Solution seems to be like importing the excel file to a new access database and then importing new access database to existing database (Seems like a solution)

    Accepted Solution

    I kept searching and found how to do it.  It involves changing the Windows registry.  I have Windows 7 running a 32-bit version of Microsoft Office 2010.  I launched the program RegEdit and navigated to: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel  I changed the TypeGuessRows setting from 8 to 0, which causes the entire Excel file to be scanned to determine the data type of each field rather than just scanning the first 8 rows.  This can slow down the importing of data if you have large files, but certainly speeds up the import process for me compared to having to manually move a record with a large text field to be the first record in each imported file or having to import each file to a new Access table and then merge that with the master table.  On the other hand if you don't feel comfortable changing the registry the other two methods will work well, just a little slower.

    Here are some links with more detailed information:
    LVL 8

    Expert Comment

    by:Amit Khilnaney
    marknlynn3: Great Work..


    Author Closing Comment

    Thanks for looking into this issue.

    Expert Comment

    Many thanks for the post!  Works great now...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now