Automated Import of Data to Microsoft Access from Excel with Just One Click

In this Microsoft Access tutorial, I'm going to teach you how to automate the import of data to Access from Excel with a little VBA and just one click. We're going to use the TransferSpreadsheet command and update existing records in our database.
BECOME A MEMBER:
RUclips: / @599cd
or My Site: 599cd.com/THMember
LEARN MORE:
Automated Import: 599cd.com/AutomatedImport
PREREQUISITES:
Automated Export: 599cd.com/AutomatedExport
Update Queries: 599cd.com/UpdateQ
LINKS:
Backup Access: 599cd.com/Backup
Import New, Changed Records: 599cd.com/ImportChanges
RECOMMENDED COURSES:
Access Developer Courses: 599cd.com/Developer
CODE:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "CustomerTempT", "C:\File.xlsx", True
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Contacts
Order Entry & Invoicing: 599cd.com/Invoicing
More Access Templates: 599cd.com/AccessTemplates
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
How to Use Access in 30 Minutes: 599cd.com/30Minutes
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/TechHelp
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...
KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Import from excel to access, import updates from excel, DoCmd.TransferSpreadsheet, acImport, unable to append all the data, update query
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@Shadow.Dragon]

Thanks for the video Richard! I always learn something watching your videos! 🖖

[@RichBx]

Fabulous timing, Richard! I've used your excellent teaching videos to teach myself how to create a Budget db for my own personal use which imports csv files where I then add categories and subcategies for spend.

[@khalil547]

Thanks that was very informative our dear instructor! Thumbs up!
I liked and shared!

[@stevekettor6857]

Thanks for the presentation, I do appreciate your video.

[@alializadeh8195]

Thanks

[@abdulhamidalhaddadi2255]

Great,
it will import formatting too ?

[@omegacodedeveloper3592]

Thanks for this video

[@599CD]

You're very welcome

[@mattsarino230]

is it possible to import data from excel if the data is only from a certain range?

[@stanTrX]

El Fantastico ❤

[@petewarby7158]

13:00 😂 Its early AM in the UK but watching this, it seems counter intuitive that you're pulling records from your CustomerTempT form in to your CustomerT form, but when you look at the Query it says "Update To" field is CustomerTempT.
Maybe I need a coffee and have a rewatch, is it just me, is it too early? Lol
Anything great teaching video!!! Thank you.

[@PatielChikobe]

Kindly help how do include primary key in that statement in access =Nz(DSum("[tithe]","TitheTable","Quater='quater 1'"),0)

[@bosoxkid2]

Thanks Richard! Would it be possible to show how to choose a specific file to import by using FileDialog? Goal is to completely replace the data in a table periodically by just importing a new file.

[@599CD]

Yep. I cover this in the extended cut for 599cd.com/images

[@timbo4127]

How do you automate the import with multiple tabs, and different tables?

[@chrismcguire6423]

Should it be acLink, not acImport? I think acImport overwrites a table??

[@stevekettor6857]

I want to learn Access course and wish to sign in as a beginner

[@gautamshiyani1286]

I have One Trouble. Im Using a Software which uses CSV File for Data Import and use Access. But CSV File Sometimes Contains Wrong Data so I Have to Change it Many Times In CSV. Particular State name. I want use State name for Every Entry Same. Even if the csv have Other name State or Blank in State Column. I tried many Things But Didnt Find Anyting. And also i cant use Query and Another Thing because i use access from another Software. So theres just one way to change data when importing. is there any way?

[@danielazzam]

Automated with just one click ! Is this a new definition for automation ?

[@nonanice1]

Can this process be done to automatically import data from website to ms access?

[@599CD]

Not this process specifically but it's definitely possible

[@solarycreativo]

great, I want to know how to import without white cells in excel?

[@599CD]

That's racist

[@599CD]

LOL. What do you mean "white cells?" You mean colored white, or EMPTY? Either way: 599cd.com/Ask

[@solarycreativo]

@@599CD I'm from Colombia, I mean blank cells or empty cells I don't understand why you say about racist, sorry by this

[@solarycreativo]

@@599CD in my country there is no problem if you say some color but now I see, north American People have problem with this, I'm sorry I wrote like a Colombian

[@599CD]

No no no... I was just joking... kidding... here in the US, when someone says something, anything, with either "white" or "black" in it like that, it's a silly joke to just say "that's racist, man." Just ignore me. I know it doesn't translate to other languages / cultures.
If you want to import the NON-EMPTY cells only, it all depends on how you have your sheet set up. I would personally import EVERYTHING into Access and the sort it out there. Much easier than trying to get the sheet right.