Access 2007 - Importing numbers as text

[sdkfz]

I deal with large chunks of data that I have no control over formatting when I obtain it. Several 'numbers' are in fact text, like SSN, zip codes etc. I have been able with previous versions of Acess to define a field as text and import into it say 000000001 and it shows up as just that. Access 2007 imports it as 1. The trouble is I have a large set of data tables that I purge monthly, and re populate with the latest information sets and importing to these emptied tables is no longer working correctly.

Help says I need to include text qualifiers in the form of double quotes to get around this, well I do not have the ability to do this, my data comes out as tabbed txt or csv and no ability to place the double quotes. I tried minimum field sizes in Access and import masks neither worked and in fact help states input masks are ignored when importing txt and csv.

Now it gets weird, I thought - well the DB is a Access 2007 DB converted from 2003 which was converted from XP and then from Access 97 - perhaps if I set up a brand new DB in 2007... well of course I had to import a data set into a brand new table. There was nothing in this new DB yet right? The leading zeros come in - Ah Ha! - okay it will be a pain to recreate all the tables again but not that much trouble. I test being able to import into an exisitng table by emptying the table via a delete query and re imported into the now exisiting table and now leading zeros are gone again. Oh - Access has no problems writing SSN's already in Access to other tables with the leading zeros kept, the issue is getting them into an exsitng table.

So I 'could' import the tables to a new table, copy the name from the exisitng one- delete it and re name the 'new' table, or use the Access 97 copy still floating around in my machine as the import back end database but this seems so stupid.

Anyone got any ideas? I tried MS support forums and they could/would not answer the question going back to bad DB design and why are you using Access anyway? I know the design is horrible, but I am stuck with it (inherited from the person I replaced). SSN is the only good primary key in the three different data sources I am forced to use to create reports.

[sdkfz]

bump

[pam123]

I hate to say this but

So I 'could' import the tables to a new table, copy the name from the exisitng one- delete it and re name the 'new' table, or use the Access 97 copy still floating around in my machine as the import back end database but this seems so stupid.

I know 4 people who are now doing exactly that.

[sdkfz]

Thanks Pam, maybe I'll go back to 2003, I got the Office for free (along w/Vista Ult) for going to the launch - so there is no monetary loss at least.

[pam123]

Originally Posted by sdkfz Thanks Pam, maybe I'll go back to 2003, I got the Office for free (along w/Vista Ult) for going to the launch - so there is no monetary loss at least.

Glad you're not out any cash.
Check back with the MS Support forums in a few months and see if anything's happened.