Solved

I need help importing data in Access 2013

Posted on 2014-11-21
3
631 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40457827
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
ID: 40458620
Thanks for your reply Scott,
I will try your suggestion over the weekend and post my results.  Thanks.

mrotor
0
 

Author Comment

by:mainrotor
ID: 40477900
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
microsoft Accesss Basic Coding 44 63
MS Access Query Question 2 35
Options for Linking SQL tables to Access 2013 9 40
VBA Delete sql "Too Few Parameters" 8 24
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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