Solved

I need help importing data in Access 2013

Posted on 2014-11-21
3
436 Views
Last Modified: 2014-12-14
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
0
Comment
Question by:mainrotor
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
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
 

Author Comment

by:mainrotor
Comment Utility
Thanks for your reply Scott,
I will try your suggestion over the weekend and post my results.  Thanks.

mrotor
0
 

Author Comment

by:mainrotor
Comment Utility
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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now