Import/Transfer Table Data

[AC5FF]

I've read through a lot of posts about transferring data, but none seem to be able to help me. I'm hoping I can find a little expertise here..
I have a table "MASS" that has 16 fields. This table used to be updated daily via a macro that pulled in data from 5 different TXT files. Those TXT files are no longer available and I now have to update this information from an XLS spreadsheet. The positive side is that i only have 1 spreadsheet vs the 5 TXT files.

I have edited the macro and i can get the new XLS file to import (Currently importing as MASS IMPORTS table); but the field names in the table do not match (First Row contains Field Names). This is a HUGE problem since the MASS table has around 40+ dependancies.

I have gone in to MASS IMPORT table settings and have renamed the fields to match the old MASS table but when I try to import new data via the macro I get errors since the field names do not match. I've tried importing with and without field names (Field names change to F1, F2, etc..) and rename those, same error. I have tried to edit the field names during the initial import/table setup and will get the same error when trying to update.

I had been trying to 'transfer' the data from one table to the next, but that hasn't worked either. I have to basically whipe the tables first; i don't want to overwrite because one import may have 20 lines, the next may have 30 or 15.. it will vary day to day. So, there are no fields that I can link between these two tables... (I don't think).

I have even gone so far as to start editing the 40+ dependancies and replace all the MASS table references to MASS IMPORT table ones. But for some reason when I get to certain queries Access will crash... That one is WIERD....

Anyone out there know what I might be doing wrong or how I can get this data into my DB in the right format/etc..???

Thanks!!!

 

[KenReay]

Hi

Import to thge MASS Import Table as you are doing, then use an append query to add the data to the MASS table, you can "map" the diiferent couln names in the query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[AC5FF]

Ken;

That's what I was thinking, but it isn't working...
I have the query built; all the columns in it are basically the same as:
Field: Nomenclature
Table: Mass Imports
Update to: MASS:Noun

This doesn't work... BUT>>> just noticed something on this window. It says : Update Query; you said "Append Query" ... would that make a difference?

 

[AC5FF]

Didn't see how to edit the above post..

Ken;
Went back in and I was copying another query to get to the 'update' query function. (This was the first time I've tried to write anything other than a normal query.) Anyhow, I figured out how to write an 'append' query and whalla it works! LoL 3 days.. 3 DAYS i spent trying to figure this dang thing out !!!! LOL I new it had to be simple!

Thanks a TON! I left work today in a GOOD mood for once! HAHA

 

[KenReay]

Yes, it makes it worth it when a plan comes together don't you think?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK