• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

Append Data from Excel form to Access Table

Is there an automated way to append a table in Access from a form in Excel?  Most of the information on the form is selected from list boxes created from the same Access database, but there is a note field with typed text as well.

Experts Exchange helped me automate the updating process for the Excel form by "Refresh" through "Connections".  I am hopeful the process can be reversed some way to get new data from the spreadsheet form into the database without having to type it all in.  The same form is used each time with different info in the fields.  And it is done many times each week - very time consuming.
0
BrainFrost
Asked:
BrainFrost
  • 6
  • 5
  • 3
  • +1
2 Solutions
 
PatHartmanCommented:
Why would you not just use an Access form to do the data entry?   You'll have a lot better control over what is happening.  If you are trying to collect data remotely, then you still wouldn't do it this way.  You would collect the data in Excel and then have the Access app import from Excel.  I'm sure you can get Excel to write to Access but you will almost certainly need to use automation to do it.  You won't be able to make a live link from the form to Access.
0
 
Jeffrey CoachmanMIS LiasonCommented:
External Data-->Import & Link-->Excel
Select: "Append a copy of the records to:
...then select the table.

You should then be able to follow the rest of the wizard's steps...

JeffCoachman


Note that you must have the same fields in the Access table and the Excel sheet
0
 
Jeffrey CoachmanMIS LiasonCommented:
Sorry, ...Updated steps:
External Data-->Import & Link-->Excel
Browse to the Excel bile
Select: "Append a copy of the records to:
...then select the table.
You should then be able to follow the rest of the wizard's steps...


...but Pat brings up a good pint.

If this has to be done on a regular basis, ...then consider moving all the data to Access...

JeffCoachman
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
BrainFrostAuthor Commented:
It is reassuring to learn that what I'm after is doable.  Yes, I would like it to be automated.  I assume this means some programming.  Ideally each new form will be saved to the same location/path and renamed the same name.  Then a module or macro can be ran from Access to "import" the data.  The process will have to be repeated for each new copy of the form that is turned in.

The form has a drop-down box to select the state and other to select the school district from those in that state, another to select the school from those in that district, another to select the room in that school from those that contain equipment we need to service.  Our service tech uses the form that was emailed to him after the latest refresh to report the customer service work he has done.  He then emails it to the database administrator for use in updating the database.  The service techs don't carry their own copy of the database, which wouldn't update "the" database anyway.  If a database form can be created to be used the same way we now use the spreadsheet form, I am willing to do it if it will help automate updating the database from the completed forms.
0
 
PatHartmanCommented:
Exporting a spreadsheet for the tech to fill in will work fine.  Just make sure to include the ID of the record that will be updated.  You can establish a separate email folder to receive the returned files.  The Access app can periodically read the emails, extract the attached speadsheets and save them for backup.  It can then use the data in the spreadsheet to update the database.  The final step would be to delete the email or move it to an archive folder.

If the tech always works from a sheet you create, it can be prepopulated with most of the info he needs.  All he needs to enter is an outcome or time completed.
0
 
BrainFrostAuthor Commented:
Thanks Pat.  Your concept sounds great.  Just to make sure there is no misunderstanding, the table is to be appended with a new record from each form.  The equipment ID is in the form, but hidden from view as the tech does not need to see it.  The form can be changed as needed to facilitate automating the database updating process.

The tech uses the form we create from the database, but selects the prepopulated info from drop-down boxes on the form.  Most everything in the drop-down boxes have corresponding ID numbers:  District ID, School ID, Equipment ID, but they are hidden from view.  The tech selects everything by name from the boxes.
0
 
PatHartmanCommented:
Sounds like the Tech is working from a template.  That works file although if you "schedule" things, you could send him a pre-populated file with everything set for the task he is working on.  The second method would minimize erroneous selections.
0
 
BrainFrostAuthor Commented:
I see how making all the selections for the technicians on the form and then sending each tech the forms he will use each day would minimize erroneous selections, but we haven't had that problem so far.  The bigger problem would be spending too much time prepopulating their forms and sending all the required emails.  Presently there is one form for everyone and they all use it until we update it from the database and send a new one to everyone.

Must I convert from an Excel template to an Access template in order to get "automatic" appending of the data from the many returned templates to the database?  What is my next step to get moving in that direction?
0
 
PatHartmanCommented:
There is nothing automatic about this.  You need to create a procedure to receive and process the emails, extract and save the spreadsheets, and then finally import the data.

An alternative would be to give the techs an Access database to do data entry into.  Then at the end of the day, they upload the database and run the code (that you create) to append the data.  Each day, the tech should start with a fresh, empty template.

The question becomes - where are the techs working?  If they are local, then they can do the data entry directly into the Access app.  If they are remote, then they can use email to send a file as you are proposing or if you can rely on their having an internet connection at all times, you can create a hybrid Access app where they have a data entry form that is hosted in SharePoint and goes directly to the Access database.
0
 
BrainFrostAuthor Commented:
The procedure you suggest may actually automate too much.  More than one procedure may be better.

 The database administrator needs to validate the data before it becomes part of the database by viewing it, preferably in the spreadsheet form submitted by the tech to see if it is reasonable and correct any errors.  After it is approved by a human brain, it can then be imported into the database.

A procedure that harvests and saves the spreadsheets for future validation and another that imports the data from validated spreadsheets.

I must confess that I can not even begin to know how to do this.  I don't need help doing it - I need someone to do it for me.  If the code was already written, I might be able to make some changes to adapt it for my specific paths and file names, but I would even need guidance for that.  Are you available or can you put me in touch with someone who can do this for me?
0
 
PatHartmanCommented:
The Import process can also validate data.  Some validations are better done with automation.  Others by humans.

Many experts have the hire me button enabled.  I'm sure you can find someone who is willing and able to do the job for you.
0
 
BrainFrostAuthor Commented:
If anyone feels they want to take this project on and you are available, I need to get some cost estimates.
0
 
BrainFrostAuthor Commented:
The question has not been answered.  My attempt to hire someone fell through.  No one has since volunteered.  My need remains and should not be closed for the sake of appearances.  If my issue remains much longer I will go away.
0
 
Jeffrey CoachmanMIS LiasonCommented:
BrainFrost,
Email me again and I'll see if I can't pick this up...
0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now