Append Data To Table From Excel

[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

[Mike S.]

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

"Martin" <md******@earthlink.net> wrote in message
news:Cx***************@newsread1.news.atl.earthlin k.net...

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

[Martin]

Mike,

Thanks for a great response!!!

Martin
"Mike S." <do******@noresponse.com> wrote in message
news:Bs***********************@bgtnsc04-news.ops.worldnet.att.net...

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

"Martin" <md******@earthlink.net> wrote in message
news:Cx***************@newsread1.news.atl.earthlin k.net...

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

[Martin]

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
"Martin" <md******@earthlink.net> wrote in message
news:sk****************@newsread1.news.atl.earthli nk.net...

Mike,

Thanks for a great response!!!

Martin
"Mike S." <do******@noresponse.com> wrote in message
news:Bs***********************@bgtnsc04-news.ops.worldnet.att.net...

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

"Martin" <md******@earthlink.net> wrote in message
news:Cx***************@newsread1.news.atl.earthlin k.net...

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

[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

"Mike S." <do******@noresponse.com> wrote in message
news:Bs***********************@bgtnsc04-news.ops.worldnet.att.net...

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

"Martin" <md******@earthlink.net> wrote in message
news:Cx***************@newsread1.news.atl.earthlin k.net...

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