Okay, so I've got an Access database that's got a table full of equipment.
I exported it to an Excel spreadsheet, removed columns I didn't want to share, then e-mailed the XLSX to a supplier who populated the COST records with their price quotes.
So far so good!
Now I would like to take the information in the Excel spreadsheet and put it back in the Access database.
I am a pretty naive user of Access, so I don't have a sense of how to do this.
Hi
Make sure that both the table in your Access database and the Excel Worksheet are in exactly the same order. If they are you simply be able to cut from your Excel Worksheet and paste into your Access table the columns that are missing.
Hope this helps.
Good luck.
Tony
Alternatively, you could import your "new" table into excel as a temporary table. Create an update query to add the data from the supplier to your "old" table from the "new" table. Once you are content that the data in the "old" table is correct, then delete the new table.
Important Note: Which ever way you go, ARGK's solutioen or mine, be sure to make a backup copy of your "old" table before updating it in case you mess it up. It will be easier to just replace it and start over with the updates.
Post back with any issues you encounter.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I like the idea of creating temporary tables as insurance against screwing up, but we have a third-party app that hooks Access to Autocad, and that piece of software does not handle changes to the layout/setup of the table(s) with any grace at all.
So it looks like re-arranging the excel to match the Access table and then copy-pasting the records in might be the way to go. I was thinking I might have to build some heavy-duty logic into an SQL query, but if copypasta works, then... "simple is best", that's my motto.
Naturally, we've changed some sizes and some tagged names between sending out to tender quotes and now, so I'll have to handle that, too....
Whelp, I'm gonna mark the thread [SOLVED] for now, but rest assured I'll come shouting for help if I crash and burn.
One more suggestion. If your excel file looks like it should as an Access table, then you could avoid the whole cutting/pasting and importing by just linking your Excel table(file) to Access. It will appear as a linked table in Access and work just as if the table were actually in Access.
Linked tables... my thought is that it's something to consider, but it's not what I'm trying to do here; I want to push the data back into Access and delete the Excel spreadsheet and have one place we record specs, not two, or three, or twelve, as can too-easily happen.
I actually thought about going the other way & linking the Access table to an excel spreadsheet, with the thought maybe I could push changes back from Excel to Access, essentially using Excel as a form, but I couldn't get that to work & I'm not sure it was a hot idea anyway.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks