Excel To Access: Transfer multiple rows from excel to access

[sam]

Hi All,

How can I transfer a Bock of data to Access from excel by clicking a "Submit"
button?

eg: I have designed a "Submit" button on the excel sheet that exports all of
the
student data into access in a single row... BUT I want to insert all this
data
in access in seperate rows.

Here is what My excel table looks like:

Student_ID Subjects Grades
123456 Eng A
123456 Hist B
123456 Math B+
123456 Bio B-

So, once we click "Submit" I want the data displayed above to go to
access.
NOTE: it should look exactly the same in access, each row in a seperate row
in access

What I have now: I can get this data into access but all in one single row,
which looks like this:

Student_ID Subjects Grades Subjects2 Grades2 Subjects3 Grades3
123456 Eng A Hist B Math
B+

What I want:

Student_ID Subjects Grades
123456 Eng A
123456 Hist B
123456 Math B+
123456 Bio B-

So basically It should look the same in access like it looks in excel
(transfer the entire data shown below in access at the same time, each in
new row).

Hope I made it clear

Thanks in advance

 

[JimS]

Use the Access object model and the "transferspreadsheet" command. You must
have the Access object reference, a license for Access, and use vba.
Something like this (Not tested or compiled...)

Dim objAccess as new Access.Database
objAccess.Open........(database name, etc.)
objAccess.DoCmd.TransferSpreadsheet........ (you'll need to have the data
in a single sheet or a named range...)
objAccess.Close

Again, very rough. You're basically "becoming" Access and giving it
instructions to draw from your spreadsheet. There may be an issue with the
spreadsheet being open when you do this. If that's the case, try doing the
whole thing from Access.