excel - How to overwrite a table in Access when importing data from a spreadsheet using the DoCmd.TransferSpreadsheet Method? -
i can't figure out how overwrite current table in access when import data excel spreadsheet using docmd.transferspreadsheet
method. code below appends imported data table1
while want overwrite imported data.
sub accimport() dim s long: s = thisworkbook.worksheets("sheet1").cells(rows.count, "a").end(xlup).row dim acc new access.application acc.visible = true acc.opencurrentdatabase "f:\dbs\mydb.accdb" acc.docmd.transferspreadsheet _ transfertype:=acimport, _ spreadsheettype:=acspreadsheettypeexcel12xml, _ tablename:="table1", _ filename:=application.activeworkbook.fullname, _ hasfieldnames:=true, _ range:="sheet1$a1:p" & s acc.closecurrentdatabase acc.quit set acc = nothing end sub
discard existing data table1 before calling transferspreadsheet
:
acc.opencurrentdatabase "f:\dbs\mydb.accdb" acc.currentdb.execute "delete table1;", 128 ' dbfailonerror = 128 acc.docmd.transferspreadsheet ...
if structure of target table must change accept new data transferspreadsheet
, can remove table first , allow transferspreadsheet
re-create new structure. can execute drop table table1;
or use docmd.deleteobject
method remove table.
Comments
Post a Comment