[SOLVED] Export data into Access Database

Hi
Is there a code which will allow me to click on a cmd button to add the
infromation shown in certain cells on the excel worksheet into an existing
Access Database to specific fields.

Thanks
Noemi

Yes the below uses DAO (you can use AODB also if your using a current version
of Office). Remeber to set the Reference to Microsoft DOA 3.6 in your
project. The Workspace code may vary with your version.

The example below takes data from E3, E4 and E5 and puts it into an Access
table with a primary key, if the record already exists it prompts for
overwrite. The below is basic but should get you started. The code is
attached to a button.

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
--
Tony Green


"Noemi" wrote:

> Hi
> Is there a code which will allow me to click on a cmd button to add the
> infromation shown in certain cells on the excel worksheet into an existing
> Access Database to specific fields.
>
> Thanks
> Noemi