Access 2010: Positional text output

We had updates last week, and not Access 2003 isn’t working properly. When I import a file, I need to define every column as text. I select a .csv file and a window pops up showing it as delimited. I click on a column (any column) and I get an error window saying ActivX isn’t working. I can still import the file, but numeric columns are wrong because Microsoft is smarter than I am and they know that no one could possibly want dollars and cents with trailing zeros. :rolleyes: Unfortunately, my programs do want trailing zeros. If there aren’t two cents positions, the amounts will be off by a factor of ten or a hundred. (Just the thing you want on your credit report if you pay late!) There is a workaround. Instead of clicking on each column and defining it as text, I can choose Advanced on the initial import pop-up and define the columns as text there. I never knew about this, and it’s actually faster than the way I’d been doing it.

So much for the background. I really should be using Access 2010. I haven’t been, because when we first got it I couldn’t make it do what I wanted to do, and I couldn’t find anything. So the IT guy and I were trying to resolve the original problem yesterday and found the workaround for Access 2003. We tried to use Access 2010. I was able to import an .xls file (something I couldn’t to with 2003; it required a .csv file). The problem here is the export. My programs require fixed-position text files. In Access 2003 I would export as text, choosing the Advanced option and typing in the field lengths and start positions for each field. So my account numbers would start in position 6 of the text file and go for 12 bytes. The account name would start in 18 and go for 40 bytes, and so on. We could not find an option to define start positions and field lengths in Access 2010, and I’ve just heard from the IT guy that there are no Access Gurus. I can export a fixed-position text file, but Access puts in pipes and dashes to indicate cells, rather than just exporting the data. I am not going to rewrite hundreds of programs to accommodate this!

So here’s the question: Is there a way to export a file from Access 2010 as text, and define the output positions and lengths of each field?

A quick check of msdn shows there is/was a known issue near the problem you’re having https://support.microsoft.com/en-us/kb/2632231. This dates from 2011, so in in the last 5 years they may have fixed it. Is your 2010 installation fully patched?

See also https://msdn.microsoft.com/en-us/library/ff197066(v=office.14).aspx and https://msdn.microsoft.com/en-us/library/ff192475(v=office.14).aspx One or the other of these probably is the tidbit you’re looking for.
And here’s some general advice on exports: http://stackoverflow.com/questions/17272945/exporting-a-query-result-into-a-text-file-using-vba-ms-access

Between all these pages you can probably learn enough about how to do what you want.

No idea if 2010 is fully patched. The IT guy couldn’t find 2010 on my computer. I had a shortcut in a folder that went somewhere, but I don’t know where it found it.

The link above is ‘Access 2010: Fixed width text export does not respect the Start and Width settings of Export Specification’. I don’t even see where I can specify the Start and Width settings for a text export.