loss of fixed width import spec in '07

[xwizardx]

I ran into the following last night. Is there a work around to this problem. If there's no solution, five years of applications are down the drain.

Below is from a posting in a newsletter I receive:

Q. Code for importing data from text files that
worked fine in Access 2003 doesn't work in Access 2007.

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!).

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 fixed width file.) However, 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 Retrieving Basic PC Information

HELP


Barry Davis, President
Small Business Computing

 

[lameid]

Options:

Don't upgrade to Access 2007.

Rewrite import and exports using a third party tool and call it programatically.

Use low level file handling () to read in the text and handle each whole record as a string, break it up and write it out via a recordset... Honestly an old book on BASIC would serve you well here.

 

[MasterRacker]

This is a bit of a zombie thread, but DoCmd.RunSavedImportExport can execute a saved specification:

http://msdn.microsoft.com/en-us/library/bb243760.aspx

One weakness is in 2007, the file names are now stored in the specification and it looks like they can't be changed programmatically as before. At least the spcifications can be run though.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]