Hi All,

How can I make multiple data for the same column in excel go in access.

eg: There is a button "Submit" on the excel sheet that exports all of the
student data at once in the database.. I want to insert all this data in a
seperate row.. all at once..

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 the data displayed above to go in
access, Each student data should go in a new row(basically access table
should look exactly the same as the excel sheet above)

here is what I have for recordset(part of my code):

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1



Thanks in advance

I'm not sure if you're asking for help coding the Submit function, or if it
works fine but you are having trouble with your database table.

If the latter, open your database, right-click on the table name and click
"Design View". Look for the field with the little key symbol next to it.
Right-click on the key symbol and click "Primary Key" to remove the key.
Last, click on the actual field name and look at the properties below. If
you see "Yes (No Duplicates)" click there and select "Yes (Duplicate OK)".
That should allow multiple rows.

"sam" wrote:

Hi All,

How can I make multiple data for the same column in excel go in access.

eg: There is a button "Submit" on the excel sheet that exports all of the
student data at once in the database.. I want to insert all this data in a
seperate row.. all at once..

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 the data displayed above to go in
access, Each student data should go in a new row(basically access table
should look exactly the same as the excel sheet above)

here is what I have for recordset(part of my code):

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1



Thanks in advance

No, My issue is not with the primary key or the database.

I simply want this data to go to access when I click "Submit" button in excel

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

So for eg: when I click "Submit" in excel..
I want the whole thing displayed above to go to access.

Thanks in advance

"Charlie" wrote:

I'm not sure if you're asking for help coding the Submit function, or if it
works fine but you are having trouble with your database table.

If the latter, open your database, right-click on the table name and click
"Design View". Look for the field with the little key symbol next to it.
Right-click on the key symbol and click "Primary Key" to remove the key.
Last, click on the actual field name and look at the properties below. If
you see "Yes (No Duplicates)" click there and select "Yes (Duplicate OK)".
That should allow multiple rows.

"sam" wrote:

Hi All,

How can I make multiple data for the same column in excel go in access.

eg: There is a button "Submit" on the excel sheet that exports all of the
student data at once in the database.. I want to insert all this data in a
seperate row.. all at once..

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 the data displayed above to go in
access, Each student data should go in a new row(basically access table
should look exactly the same as the excel sheet above)

here is what I have for recordset(part of my code):

With rs
.AddNew
.Fields("Student_ID") = Range("A3").Value
.Fields("Subject") = Range("B3").Value
.Fields("Grades") = Range("C3").Value

.Update
End With
r = r + 1



Thanks in advance