Earlier this week I wrote about the frustration of transferring Excel data to Access with flaky OBDC connections or drivers. I’m not sure that was exactly the cause but a lot of error conditions were experienced trying to manipulate data with the linked tables which were Excel spreadsheets. Eventually I got the data to be transferred using VBA but it has come home to us this week in transferring data from Excel to Word (using mail-merge) for our school reports that there is a 255 character limit on some text fields. Excel basically will check the first 8 rows of a spreadsheet in order to try to determine the type of field and if strings are less than 255 characters in length they are cut to that length. From Access we know that to get more than 255 characters of text you have to use a memo field. If you use VBA code to automatically import a spreadsheet to a new table the data type of the table is set on each column automatically based on the above (text for strings up to 255 characters and memos for more than 255 characters). This can lead to data loss.
On the other hand if you manually import the spreadsheet from Excel into Access you get to specify the data type for each field as part of the wizard and can then choose to specify Memo for the fields which have more than 255 characters of text in them. The data is then imported according to the specified settings and is fully preserved. However I find it annoying that this can only be guaranteed to work if the manual import process is followed each time. If you create a table and change its field definitions and then use VBA code to import that table it will not honour the field data types, it just makes them up again which is pretty stupid because it should be able to detect what the user wants since they have no other way of programmatically specifying the data type for each field.
There is a registry key called TypeGuessRows which is supposed to have an impact on earlier versions of Office. It looks like support for this feature has been dropped in Office 2010 because changing the setting had no effect on the behaviour either in Word or Access.
I would like to say I am extremely annoyed at the way this functionality has been sprung on Office users through successive levels of arbitrary design and coding decisions taken at Microsoft. It is likely there are a considerable number of people doing mail merges from Excel sheets with text fields larger than 255 characters who will be impacted negatively by this design limitation. In previous versions of Office the registry mentioned provided a means of working around this problem. However the apparent discontinuation of this functionality in Office 2010 makes it impossible to guarantee a successful import or merge unless a clumsy workaround is used (this is to put a row at the top of your spreadsheet, preferably the 2nd row, that has more than 255 characters in each text field). Furthermore, that when using VBA code to import a spreadsheet directly in Access, there is again no surety, it would be OK if there was a means to specify the import specification which can be done with the TransferText method but this is not possible when importing a spreadsheet because that option simply isn’t available. You can only guarantee with a manual import and setting each field type at import time that you will get a successful transfer of all long text fields. There may be ways to work around this problem if you are using VBA with methods other than DoCmd. However these means seem to rely on the same drivers which are the functionality where the problems exist.
My advice therefore is to use one of the following:
- Migrate to MS Access (which has a lot better functionality as a database that Excel)
- Migrate to some other database if you can so you don’t have to use any part of Office
- Try the workaround for the second row of the spreadsheet for current worksheets you need to import to Access/merge to Word
