easier way to update access table from excel?

im working on a database that has a table of job tasks.  my boss updates the job task on an excel spreadsheet.  when he has updated a set of tasks he tells me and i get it from the network folder and start manually import to a new table.  i do this by tables, new, import table,and select excel.  it always has inport errors, format or something.  then i go through and find/replace things to make it work.  if there is a way to do an update query or something would be better.  there are things on the task list that usually dont change, like task number.  so i thought it would be better if i could automaticly do an update by task number.  like find #1 and update the remaining fields, and on to the next.  

any ideas on how to make this task easier for me?
sumhungl0Asked:
Who is Participating?
 
djpazzaConnect With a Mentor Commented:
First thing I would do is to make the excel job sheet only contain the same data format that is being exported to access.  This will minimize any import errors.  I always try a keep the same column headings in access to match excel.

Then in Access create a 'linked table' to the excel file.  Then you can create a query in access to sort /filter the data.  Any information added to the source excel file will be reflected in Access.
0
 
sumhungl0Author Commented:
almost quittin time, gotta lock up shop.  have a good weekend!
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
sumhungl0,

1. To elaborate on djpazza's first comment...
You can set up "Data Validation" in Excel to make sure that only certain types of data can be inputted.

2. <it always has inport errors, format or something. >
...We really can't help you much if you don't specfy your exact issues here.

3. <if i could automaticly do an update by task number. >
Can we agree that no one here can have any idea what this means without seeing the actual Excel File

JeffCoachman
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
sumhungl0Author Commented:
well, ok...

1. the excel file isnt mine so i would have to tell the boss to make the data validation happen, or just copy it when its time to update and do it myself.  would that data validation tell me what is wrong with the excel file?

2. ill have to try it again and see what it was.

3. the task number is an ID number for the job task which doesnt change and it is how i make sure updates get input.  i was just saying that it is a way to identify the task by number.  
0
 
djpazzaConnect With a Mentor Commented:
Could you post an example file?  edit it with some sample data for us to look at?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<would that data validation tell me what is wrong with the excel file?
>
No, put it will prevent subsequent errors.

As djpazza and I have suggested, ...Please post a sample.
0
 
sumhungl0Author Commented:
ill have to work this issue when it comes time to update again.  thanks for the help, ill try to first fix the excel file.  thanks again.
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.

All Courses

From novice to tech pro — start learning today.