Q: Fred Kerr writes that code for importing data from text files that worked fine in Access 2003 doesn’t work in Access 2007. Why?
A: If you are using a saved Import spec, it won’t work in Access 2007. There was a significant change in Importing/Exporting in Access 2007; import/export specs are now saved in a system table, and are not available for use in code. You can create and save specs in Access 2007, but they can’t be used in code, only in the interface (why Microsoft did this is beyond me!). Here is what I say in my Chapter 10 of my new book:
Unfortunately, Access 2007 VBA code doesn’t recognize saved specifications. This feature worked for several previous versions, but at present it is broken, so we must wait for a patch or service pack to fix it. For now, only code that avoids using specifications will work. (You can import from, or export to, a comma-delimited file without a specification, but not a fixedwidth file.)
However, you there are several other options for working with text files; you may be able to use components of the FileSystemObject, ADO code or legacy VB statements for importing data from (or exporting data to) text files. These topics are covered in Chapter 9 of my new book, Access™ 2007 VBA Bible For Data-Centric Microsoft Applications.
