I need help importing data in Access 2013

Hello Experts,
I need help with a feature I need implemented to an Access 2013 application.
I am developing a scheduling app.  The staff members email me their availability on a spreadsheet.  
I then save those spreadsheet to a specific folder in my C drive (C:\Schedules\).

I have a table in Access 2013 where I save the data in the spreadsheets into.  This is tedious work.

I want to create a form in Access 2013 with just 1 button, that will read all the files in the C:\Schedules directory.
And import the information into my schedules table.

Image of what my C:\Schedules directory
Directory.jpg
Here is a sample of the information that the spreadsheets contain:

USER ID            DATE_AVAILABLE            EMAIL            PHONE
100001            11/1/2014            me@test.com      111-112-2233
100001            11/2/2014            me@test.com      111-112-2233
100001            11/9/2014            me@test.com      111-112-2233
100001            11/12/2014            me@test.com      111-112-2233
100001            11/13/2014            me@test.com      111-112-2233
100001            11/14/2014            me@test.com      111-112-2233      


My tblSchedules table in Access has the same fields:
USER ID            DATE_AVAILABLE            EMAIL            PHONE


How can I do this?  Thank you in advance for all of your help.


mrotor
mainrotorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can use the DIR function to loop through a directory and get the contents:

Dim sFile As String
sFile = Dir("C:\Schedules", "*.xls")

Do Until Len(sFile) = 0
  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table", "C:\Schedules\" & sfile
  Dir
Loop

Personally, I'd import to a "staging" table first, then use VBA/SQL to move the new data into the "live" table. This would allow for you to insure you have clean data.

Also, if your file extension isn't ".xls", change the Dir command to reflect the correct extension.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mainrotorAuthor Commented:
Thanks for your reply Scott,
I will try your suggestion over the weekend and post my results.  Thanks.

mrotor
0
mainrotorAuthor Commented:
Hi Scott McDaniel,

I tried your code but I got a Type Mismatch error.  I have attached an image of the error.  How can I fix this?
Thanks in advance.

mrotorType mismatch errorType mismatch error
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.