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

PurrSent

Board Regular
Joined
Jun 7, 2014
Messages
142
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 :rolleyes: LOL
Many thanks for any help
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
Solution
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' :ROFLMAO:

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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
You are welcome.
Glad we were able to help!
 
Upvote 0
You are welcome.
Glad we were able to help!
You've been absolutely brilliant :love:
Thank you very much
Also, many thanks to @welshgasman too


1729685124005.png
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top