[RESOLVED] [Access] Getting data from Excel Spreadsheet into Access Table

Hi All,

I've tried to do some research about this online, but I haven't been able to find a situation similar to mine. I think this will either be very easy and I'm overlooking something, or next to impossible.

I work in a program at a college and we have both the college database (Oracle) and a program database (Access). I do a fair amount of work in both of them, though the program database in Access is the only one I have the power to modify. What I am trying to do is get student Placement test scores out of the School (Oracle) database and into the Program (Access) one. I am able to Export the scores from the school database into an Excel spreadsheet. Normally, it would just be a matter of importing that spreadsheet into the respective Access table, which I've done countless times, but in this case that's not an option.

The reason I can't just do a direct import is because the student records already exist in the table. The Placement test column is blank, and I just want to fill in that column for all the students who took it. When I tried a direct import, Access appended the already existing records to the table, so I ended up with duplicates. The next thing I tried was to make a command button and back it with VBA if/then statements. The way I kept track of which student belonged to which score was by studentID. For example: If StudentID="abc" Then TestScore="123" ElseIf StudentID="cde" Then TestScore="456" and so forth. The problem here was that the button would only execute one statement every time I clicked it, so I'd have to click it 50 times if I wanted to add test scores for 50 students. I don't think that's a good solution. I'm not sure if the conditional if/then statements would even be appropriate for what I want to do. I tried looping so that all 50 of my if/then statements would execute with one button click, thought about arrays, but I have no idea where to even begin. Could someone please point me in the right direction? My experience is that I've taken an Introduction to Visual Basic, so I know the basics, but if you have ideas for anything advanced if you could please explain at beginner level, I'd really appreciate it. Thank you!

You've got your button.
Only thing to do is loop through the excell file till end and update your access table where studentID equals with the studentID from excell table.

Show what you got already, so we can point u in the right direction.

well.. thats pretty hard way.. i think you are using.. an easier solution is there for this...

1. You can import the excel sheet with different table name.. (some thing like XYZ ... every time..)

2. Then create a fixed update query.. Where in you will link this XYZ table to your Employees table and update the blank field with what every you want.

3. Next time onwards.. just double click on this fixed query and update your data...

I hope I understand your concern correctly.. pardon me if I'm Wrong..

Happy Coding..
Srikanth.

If you have time, do both methods.

nagasrikanth 's method is probably the better, but I'd up it slightly.
1) make an mdb for the import table

Your button should:
2) copy the mdb (template) file
3) relink the table in the copied mdb to the main mdb
4) fill the copied mdb table with the excel data (different options there your choice)
5) run the update query


Reason is this... If you have the template mdb and it copies it every time you will always overwrite the copy with a clean template mdb file that happens to be very small. Access has a tendancy to balloon up when erasing data and adding new data.

You get to learn code to relink tables automatically (Use DAO and tabledefs... slightly easier than ado and schemas).

Its up to you though. All the methods mentioned will work, and if you do swatty's first the at least you have a working version with nagasrikanth's as an upgrade (speed and use perhaps).

Thank you to all who replied! Srikanth's advice seemed the easiest for me to follow, so I worked with that and it went really well. I'd always been afraid of creating new tables, but I've learned it's nothing to be afraid of. The technique will be very helpful to me in the future. I would have liked to push it further like Ecniv suggested, but I've never worked with linked tables or copied template files before so I'm not sure how to begin. If anyone has further advice on how to do implement these things, I'd be grateful to learn something new. If not, I'd like to thank the forum for the help and the new knowledge.