Append Data To Table From Excel

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

There is an Access table on the network. 15 users who do not have Access are
connected to the network. Is there a way for each user to be able to enter one
or more rows containing 3 or 4 columns to Excel on his machine and then press a
button or something and append that data to the Access table on the network? Any
suggestions on what the code would be?

Thank you very much!

Martin
 
One way you could handle this is to create a user form for the Excel sheet
that has the data on it. The form would simply have a command button on it
with the following code attached to its on click event:

Private Sub CommandButton1_Click()
'declare variables for ado connection and sql command
Dim oCon, strSql

'these variables will store data from excel spreadsheet
Dim strLastName As String, strFirstName As String, strTitle As String

'instantiate the connection object, and specify connection properties
Set oCon = CreateObject("adodb.connection")
oCon.provider = "microsoft.jet.oledb.4.0"

'open the connection
'you can use a UNC to a file on a server
'ex: \\myserver\data\employees.mdb"
oCon.Open "C:\temp\employees.mdb"

'my spreadsheet data starts on second row. first row contains headers,
so i'm ignoring that
Dim intRow As Integer
intRow = 2

'stop the process as soon as we run out of data in the excel spreadsheet
While Not Sheet1.Cells(intRow, 1) = ""

'assign values from spreadsheet to our variables
strLastName = Sheet1.Cells(intRow, 1)
strFirstName = Sheet1.Cells(intRow, 2)
strTitle = Sheet1.Cells(intRow, 3)

'construct an insert query using our spreadsheet values
strSql = "insert into employees(lastname,firstname,title) values('"
& _
strLastName & "','" & strFirstName & "','" & strTitle & "');"

'execute the sql command
oCon.Execute strSql

'advance our counter to the next row number and activate the first
cell
intRow = intRow + 1
Sheet1.Cells(intRow, 1).Activate
Wend


'close and destroy our ado objects
oCon.Close
Set oCon = Nothing
End Sub

This worked ok in office xp... I think it would work in 2000.

Anyway, I hope this helps.
-Mike
 
Mike,

Thanks for a great response!!!

Martin


Mike S. said:
One way you could handle this is to create a user form for the Excel sheet
that has the data on it. The form would simply have a command button on it
with the following code attached to its on click event:

Private Sub CommandButton1_Click()
'declare variables for ado connection and sql command
Dim oCon, strSql

'these variables will store data from excel spreadsheet
Dim strLastName As String, strFirstName As String, strTitle As String

'instantiate the connection object, and specify connection properties
Set oCon = CreateObject("adodb.connection")
oCon.provider = "microsoft.jet.oledb.4.0"

'open the connection
'you can use a UNC to a file on a server
'ex: \\myserver\data\employees.mdb"
oCon.Open "C:\temp\employees.mdb"

'my spreadsheet data starts on second row. first row contains headers,
so i'm ignoring that
Dim intRow As Integer
intRow = 2

'stop the process as soon as we run out of data in the excel spreadsheet
While Not Sheet1.Cells(intRow, 1) = ""

'assign values from spreadsheet to our variables
strLastName = Sheet1.Cells(intRow, 1)
strFirstName = Sheet1.Cells(intRow, 2)
strTitle = Sheet1.Cells(intRow, 3)

'construct an insert query using our spreadsheet values
strSql = "insert into employees(lastname,firstname,title) values('"
& _
strLastName & "','" & strFirstName & "','" & strTitle & "');"

'execute the sql command
oCon.Execute strSql

'advance our counter to the next row number and activate the first
cell
intRow = intRow + 1
Sheet1.Cells(intRow, 1).Activate
Wend


'close and destroy our ado objects
oCon.Close
Set oCon = Nothing
End Sub

This worked ok in office xp... I think it would work in 2000.

Anyway, I hope this helps.
-Mike
Click to expand...
 
Mike,

A twist to my original post ---

I found the way to build an HTML form with the text fields I need. Would you
know the code to attach to the Submit button to add the data in the text fields
to the table in the Access database on an intranet.

I'm asking here because I'm wondering if your ADO connection would be used.

Thanks for all your help!

Martin
 
Mike,

Sorry, I posted in the wrong place the first time.

A twist to my original post ---

I found the way to build an HTML form with the text fields I need. Would you
know the code to attach to the Submit button to add the data in the text fields
to the table in the Access database on an intranet.

I'm asking here because I'm wondering if your ADO connection would be used.

Thanks for all your help!

Martin
 
Back
Top