Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I append data from an excel spreadsheet to a access db
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to append updated data to an Excel spreadsheet | Excel Discussion (Misc queries) | |||
How do I, in a marco VBA, code to take data from excel and append it to Access? | Excel Programming | |||
How do I Append excel data to a Access database table in a excel macro? | Excel Programming | |||
use vba in excel to append data in access | Excel Programming | |||
Append Data To Access Table | Excel Programming |