We help IT Professionals succeed at work.

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

Matt Pinkston
on
Medium Priority
804 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

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

Matt PinkstonEnterprise Architect

Author

Commented:
so do I need to create a form?
CERTIFIED EXPERT
Top Expert 2016

Commented:
yes, then place a command button and name it cmdImportXL.
Matt PinkstonEnterprise Architect

Author

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
Matt PinkstonEnterprise Architect

Author

Commented:
Says you didnt specify search criteria with FindRecord Action
CERTIFIED EXPERT
Top Expert 2016

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
CERTIFIED EXPERT
Top Expert 2016
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Matt PinkstonEnterprise Architect

Author

Commented:
it crashes MSAccess when I run it
CERTIFIED EXPERT
Top Expert 2016

Commented:
post the codes that you have
Matt PinkstonEnterprise Architect

Author

Commented:
error codes?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.