If you're unfamiliar with connection.udl run the below code it will drop one
on your desktop.

Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA"
( _
ByVal lpBuffer As String, _
ByRef nSize As Long) As Long

Sub CreateConnectionUDL()
Dim oFSO As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

oFSO.CreateTextFile ("C:\Documents and Settings\" & UserName &
"\Desktop\Connection.udl")

Set oFSO = Nothing
End Sub

Private Function UserName() As String
Dim Buffer As String * 255
Dim Length As Long
Dim Result As Long

Length = 255

Result = GetUserName(Buffer, Length)

If Length 0 Then UserName = Left(Buffer, Length - 1)
End Function


"sam" wrote:

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, but data is populated in a single row...AND 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 as in excel, each row from
excel in a seperate row in access on clicking 'Submit'

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 a
new row).

Hope I made it clear

Thanks in advance