How Do I? ... Import New Data from Excel into Access, keeping the queries ?

[PurrSent]

Running Windows 10 (22H2)
MS Office - Excel / Access 2019

I've sorted out how to import an Excel file into Access, and I've saved the steps. I'll need to 'replace' the data when I recieve an updated version. How can I do this, without loosing the queries I've set up, please?

I'm hoping someone could tell me how to import the updated Db from Excel, replacing the data in Access, thus keeping the queries. Also, the Access Db name must remain the same, as I've set up MailMerge to print the labels 'dynamically', as per query, rather than have to set up the label printing each time.

Perhaps, to explain further, I volunteer for a charity (UK) and we send out Newsletters several times a year. Up until recent months, I've kept the database for this so has been easy enough to print Address labels. However, I no longer am responsible for keeping the Db, having retired from this task, and another volunteer has taken over. I still print the address labels, so I'm sent a copy of the Db in Excel (securely), and import the up-to-date data into Access. I have a feeling, if I merely import, I'll just replace the whole Db, losing the queries, which I will need to use.

I hope this makes sense LOL
Many thanks for any help

 

[Joe4]

The easiest thing to do is instead of physically importing the Excel file, you can "link" the Excel file to an Access table.
Then, if you have new data, all you have to do is replace the original Excel file with the new one (give it the same name, overwriting the original), and the new data will automatically be in there, and all your queries will be applied to it without you having to do anything.

 

[PurrSent]

The easiest thing to do is instead of physically importing the Excel file, you can "link" the Excel file to an Access table.
Then, if you have new data, all you have to do is replace the original Excel file with the new one (give it the same name, overwriting the original), and the new data will automatically be in there, and all your queries will be applied to it without you having to do anything.

Joe4, thanks very much for this suggestion. I liked 'the easiest thing to do...' in your opening sentence LOL I don't need 'complicated'

So, if I link Access with Excel file, when I get an up-to-date file, I just save it with same name, Click on 'Yes' when it says 'this file already exists... replace it?' (or something similar) then Access, knowing no different, just uses the updated Excel file???

Wow, that certainly sounds so much easier than I had envisaged.

I'm now reading up re linking the two files
Thank you SO much.

 

[welshgasman]

If you have a properly structured DB, I doubt you can use the Excel file 'as is', as that will be a flat file.?
I would expect you would need to Update existing records (recipient has changed address) and add new recipients (new joiners to the charity).

Why can't the new person just use the DB, add/edit as required and run the mailmerge themselves?

 

[PurrSent]

If you have a properly structured DB, I doubt you can use the Excel file 'as is', as that will be a flat file.?
I would expect you would need to Update existing records (recipient has changed address) and add new recipients (new joiners to the charity).

Why can't the new person just use the DB, add/edit as required and run the mailmerge themselves?

Hi WelshGasMan

Thanks for this.

I'm not sure what you mean by a 'properly structured' Db, tbh. I assume you refer to a Db with relational tables, full 'front door' interfacing, and possibly with SQL in the back? This is how I was taught that Access can be used. This particular one is 'flat', mainly because I 'inherited' it like that and I prefer to work from the table/queries rather than the interface that can be set up.

So, Excel data (flat) is fine when imported into Access, for my purposes, if the Queries don't have to be set up each time. Hence, it sounds as if the 'linking' will be brilliant, for my purposes

There are a few reasons why I continue to print the labels including -

1) I have laser printer and I have reimbursement for the toner (I do other printing for them too)
2) I use Access and can run Queries, whereas the other volunteers don't have Access and have no
knowledge of how to use
3) I don't think selection of various, and changing, criteria is a particular feature in Excel

Other benefits are that it helps 'to keep my hand in' LOL (on the 'use it or lose it, principle)

Thanks again, WelshGasMan. You'll given me further insight into Access vs Excel and importing

P

 

[Joe4]

You are welcome.
Glad we were able to help!

 

[PurrSent]

You are welcome.
Glad we were able to help!

You've been absolutely brilliant
Thank you very much
Also, many thanks to @welshgasman too