Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Data from Excel into existing Table in Access
Hi all, I got Access Database on path "C:\Data\Rec.mdb". I got one
table in that database with the name "Table1" with five columns in it. I want macro in excel which should delete all the records in "Table1" and then copy Range("A2:E100") of Sheet2 and paste it into "Table1". (Note: headings in Range("A1:E1") of Sheet2 are exctly match with the "Table1" headings). Please can any friend help me on this. I dont know if its possible or not! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Export Data from Excel into existing Table in Access
this should work.
Option Explicit Sub UploadDataToAccess() 'Needs reference the Axtive X Library 2.0 or higher Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim sSQL As String, sConn Dim iRow As Integer Dim ws As Worksheet Set ws = Worksheets("Sheet2") sConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "Data Source=C:\Data\Rec.mdb;Persist Security Info=False" Set cn = New ADODB.Connection With cn .Open sConn sSQL = "DELETE Table1.* FROM Table1;" Set rs = New ADODB.Recordset With rs .Open sSQL, cn End With Set rs = Nothing sSQL = "Table1" Set rs = New ADODB.Recordset With rs .Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdTable iRow = 2 Do While Len(ws.Range("A" & iRow).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record 'add values to each field in the record .Fields("Field1") = ws.Range("A" & iRow).Value .Fields("Field2") = ws.Range("B" & iRow).Value .Fields("Field3") = ws.Range("C" & iRow).Value .Fields("Field4") = ws.Range("D" & iRow).Value .Fields("Field5") = ws.Range("E" & iRow).Value ' add more fields if necessary... .Update ' stores the new record End With iRow = iRow + 1 ' next row Loop .Close End With Set rs = Nothing .Close End With Set cn = Nothing End Sub "K" wrote: Hi all, I got Access Database on path "C:\Data\Rec.mdb". I got one table in that database with the name "Table1" with five columns in it. I want macro in excel which should delete all the records in "Table1" and then copy Range("A2:E100") of Sheet2 and paste it into "Table1". (Note: headings in Range("A1:E1") of Sheet2 are exctly match with the "Table1" headings). Please can any friend help me on this. I dont know if its possible or not! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export Access Table to Excel | Excel Programming | |||
Macro to Export Selected fields to an Existing Access Database | Excel Programming | |||
Data From Excel Range To Existing Access Table | Excel Programming | |||
vb code to export data into existing Access table | Excel Programming | |||
Export Data to Access Table | Excel Programming |