How do I append data from an excel spreadsheet to a access db

I am trying to send a single row of data to an access database table.
Column in the spreasheet represent a column in the database table.

I want to append new records to the database and send backa reference.

how do i do this.

Below is an example using DAO (you can use ADO with later verisons of
office). The example code is attached to a button, which takes the values
from 3 cells and posts them to a table with a primary key. If you are adding
records to a table which maintains a system generated key just use the add
section; once you have updated the record you can read the key.

Private Sub CommandButton1_Click()
Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim ProdNo As Long
Dim Desc, ProdType As String
Dim Resp As Variant

ProdNo = Worksheets("Sheet1").Cells(3, 5).Value
Desc = Worksheets("Sheet1").Cells(4, 5).Value
ProdType = Worksheets("Sheet1").Cells(5, 5).Value

Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Workspaces.Append wrkODBC
DefaultType = dbUseJet
Set wrkJet = CreateWorkspace("", "admin", "")
Set Db = wrkJet.OpenDatabase("C:\data\Db2.mdb")
Set Rs = Db.OpenRecordset("Products")
Rs.Index = "PrimaryKey"
Rs.Seek "=", ProdNo
If Rs.NoMatch Then
Rs.AddNew
Rs![Product No] = ProdNo
Rs![Desc] = Desc
Rs![Type] = ProdType
Rs.Update
Else
Resp = MsgBox("Item already exists - Update or Cancel", vbOKCancel)
If Resp = vbOK Then
Rs.Edit
Rs![Desc] = Desc
Rs![Type] = ProdType
Rs.Update
End If
End If
Rs.Close
Db.Close
wrkJet.Close
wrkODBC.Close
Set Rs = Nothing
Set Db = Nothing
Set wrkJet = Nothing
Set wrkODBC = Nothing
End Sub

The above needs a reference to Microsoft DAO 3.6 adding in the tools menu

Hope this helps

--
Tony Green


"Nishen" wrote:

> I am trying to send a single row of data to an access database table.
> Column in the spreasheet represent a column in the database table.
>
> I want to append new records to the database and send backa reference.
>
> how do i do this.