I have a table with text fields that contain values with trailing spaces. As I usually work with oracle data or other enterprise level data, trailing spaces are inevitable. How can I export an access table to excel and retain the trailing spaces.
I have a table with text fields that contain values with trailing spaces. As I usually work with oracle data or other enterprise level data, trailing spaces are inevitable. How can I export an access table to excel and retain the trailing spaces.
How do you get Access to retain trailing spaces? Why would you want to retain?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
When access can't do something people will say I've developed on Access for 20 years and I've never had to do x
If you want to know, I used acces these past two weeks because my new job has it. I'm a financial analyst in a corp that uses oracle and sap because of mergers. These systems keep their trailing spaces so if I want someone non technical to vlookup against it I need to have it.
Here are some more solutions Ive tried which have all have varying degrees of success and a rant!
Bringing the excel into access is a big fail because access doesn't import well. Linked tables with the first 25 rows fake and with the right type for type sniffing, batch files that auto import. Forget it. Access isn't made for any of this
Power query. Our systems put out 100k row excel tables with over 150 columns and power query has to bring the whole thing in before the first manipulation.
power pivot. Don't even start this stupidity. Case insensitive joins. At least access has alter table alter column binary (510) which necessitates hand joins over binary sometimes.
If it wasn't for the GUI, the right answer is python and postgre, but you can't package that into one solution and the qt designer doesn't have automatic connects like swing and access.
i don't know how people program for a living, but I do not know what's wrong with Microsoft. The teams don't talk so the syntax is everywhere, but even worse, the data types are everywhere. M power pivot syntax is case sensitive without a real IDE and intellisense and access isn't case sensitive in their database. Access binary is auto converted to padded 255 string for text export, but dao.recordset2 keeps it at 510 byte array. And, even though they've got the whole 16 bit in 2 byte pairs, it can't handle something as basic as a non width space. If it wasn't for packaging and the forms with nearly auto connects I'd be right to python qt. maybe java swing but I hate hand crafting hash table objects so much boiler plate