[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

MSAccess Macro to Delete all records in table then import from excel

Is there a way to create a macro that will delete all records from a table then import from an XLS that would be the same file name / location each time?
0
Matt Pinkston
Asked:
Matt Pinkston
  • 5
  • 5
1 Solution
 
Rey Obrero (Capricorn1)Commented:
place this codes in the click event of a button

private sub cmdImportXl_click()
'delete all records
currentdb.execute "delete * from table1"
'import excel

docmd.transferspreadsheet acimport,8, "table1","c:\folder\myExcel.xls",true, "NameOfSheet!"


end sub

0
 
Matt PinkstonAuthor Commented:
so do I need to create a form?
0
 
Rey Obrero (Capricorn1)Commented:
yes, then place a command button and name it cmdImportXL.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Matt PinkstonAuthor Commented:
Like Such?


Private Sub Opportunities_Del___Reload_Click()

place this codes in the click event of a button

Private Sub cmdImportXl_click()
'delete all records
CurrentDb.Execute "delete * from Opportunities"
'import excel

DoCmd.TransferSpreadsheet acImport, 8, "Opportunities", "C:\Users\pinkstom\Documents\LOAD\USPSLOADDailyIII.xls", True, "Report!"


End Sub


End Sub
0
 
Matt PinkstonAuthor Commented:
Says you didnt specify search criteria with FindRecord Action
0
 
Rey Obrero (Capricorn1)Commented:
try this

Private Sub Opportunities_Del___Reload_Click()

'delete all records
CurrentDb.Execute "delete * from Opportunities"
'import excel

DoCmd.TransferSpreadsheet acImport, 8, "Opportunities", "C:\Users\pinkstom\Documents\LOAD\USPSLOADDailyIII.xls", True, "Report!"


End Sub
0
 
Rey Obrero (Capricorn1)Commented:
what is the name of the command button, avoid using spaces for names of command button.

change the name Opportunities_Del___Reload

with
          OpportunitiesDelReload

or
             cmdOpportunitiesDelReload
0
 
Matt PinkstonAuthor Commented:
it crashes MSAccess when I run it
0
 
Rey Obrero (Capricorn1)Commented:
post the codes that you have
0
 
Matt PinkstonAuthor Commented:
error codes?
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now