-
Dec 1st, 2009, 03:34 PM
#1
Thread Starter
Lively Member
[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!
-
Dec 2nd, 2009, 05:30 AM
#2
Frenzied Member
Re: [Access] Getting data from Excel Spreadsheet into Access Table
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.
Last edited by swatty; Dec 2nd, 2009 at 05:44 AM.
Code:
If Question = Incomplete Then
AnswerNextOne
Else
ReplyIfKnown
End If
cu Swatty
-
Dec 2nd, 2009, 06:40 AM
#3
-
Dec 2nd, 2009, 07:18 AM
#4
Re: [Access] Getting data from Excel Spreadsheet into Access Table
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).
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Dec 2nd, 2009, 01:40 PM
#5
Thread Starter
Lively Member
Re: [Access] Getting data from Excel Spreadsheet into Access Table
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|