Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export data into Access Database
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to Export Selected fields to an Existing Access Database | Excel Programming | |||
export data from MS Excel sheet to external database | Excel Programming | |||
export data from Excel to MS Access (ADO) using VBA | Excel Programming | |||
From Excel 97 Programmatically create access 97 or 2000 database and export data to. | Excel Programming | |||
Export Data to Access Table | Excel Programming |