Sherman6789
Programmer
I have an Access table that needs to be updated every two weeks. The update information comes from an Excel spreadsheet with the same headings in the same order as the Access table. The Excel table is not formated. For example: phone in Excel is 2025551212. The format in Access is (202) 555-1212. The new data coming from the Excel file always brings the old data as well as the new data. Example: the original file may have had 25 records the first week. The next update may be 28 records. The original 25 records unchanged plus 3 new records. The third update may 50 records with 28 old records (25+3 unchanged records) and 22 new records. There is a key field which is NOT and should not allow duplicates. The old table has six (6) fields that are not on the Excel file. The information in these six fields are added manually to the Access table throughout the week.
When the Excel file is added to the "current" old Access Table, the added fields must not be modified. When the new data is merged into the old file, the old data from the Excel file should be ignored, if possible. If they can not be ignored and must replace the old data; that's OK provided the six fields are NOT altered. The Access file's key field will not allow duplicates which is great. However, when I try to import external data from the Excel file, the old data is overridden with the same data from the new table and the new records are added to the file as it should. The problem is that a brand new table is created which contains all of the data from the old and the new but this brand new table does not contain the six fields that were on the old Access file and I must add them each time to the brand new table. When I try to have the data only update the old table, I get an error. It seems to work only if I let Access create a new table. If won't work if the table is open and if the table is closed, it causes an error.
The ideal solution is to have the Excel file merge with the old Access table (with the six extra fields unchanged); new data is added; old files that match the key field are ignored and the same table remains the resulting table. The old table is important because there are several forms and reports that reference the old table name.
I would have less trouble if both table were Access but I don't have that choice. If I frist convert the Excel table into Access, I then have to set the format and make an update query or append in some way. This cause for too many steps unless it was a simple program or macro.
I hope that this is not too long and I've tried to cover all the details that I think you need. The users of the file are not very familar with Access. I know enough to be dangerous but I can follow directions ok and have a pretty good knowledge of general Access information.
Any help will be appreciated.
When the Excel file is added to the "current" old Access Table, the added fields must not be modified. When the new data is merged into the old file, the old data from the Excel file should be ignored, if possible. If they can not be ignored and must replace the old data; that's OK provided the six fields are NOT altered. The Access file's key field will not allow duplicates which is great. However, when I try to import external data from the Excel file, the old data is overridden with the same data from the new table and the new records are added to the file as it should. The problem is that a brand new table is created which contains all of the data from the old and the new but this brand new table does not contain the six fields that were on the old Access file and I must add them each time to the brand new table. When I try to have the data only update the old table, I get an error. It seems to work only if I let Access create a new table. If won't work if the table is open and if the table is closed, it causes an error.
The ideal solution is to have the Excel file merge with the old Access table (with the six extra fields unchanged); new data is added; old files that match the key field are ignored and the same table remains the resulting table. The old table is important because there are several forms and reports that reference the old table name.
I would have less trouble if both table were Access but I don't have that choice. If I frist convert the Excel table into Access, I then have to set the format and make an update query or append in some way. This cause for too many steps unless it was a simple program or macro.
I hope that this is not too long and I've tried to cover all the details that I think you need. The users of the file are not very familar with Access. I know enough to be dangerous but I can follow directions ok and have a pretty good knowledge of general Access information.
Any help will be appreciated.
