• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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