Excel VBA Code to get data from MS Access

[atuljadhavnetafim]

Hi Expert

I am using below VBA code to get data from the Access database query with parameter [chooseregion]
this code working fine but when I change the region which has less data it is not clear for previous region data.
For e.g. Region1 has 1000 line items but when I change region to Region2 which has 500 line items in the database, the result shows the first 500 data correct but the next 500 data show from Region1

is there a way so that when I change the region, macro first clears all the existing line items and then shows data for the new region?
also need help, when I change the region I need to run the macro manually, is there any way so whenever cell F6 value changes the macro run automatically.


Sub AccParam() 'Excel VBA to import a parameter query from Access into Excel
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset

Set MyDatabase = DBEngine.OpenDatabase("D:\Database1.accdb") 'DB Name
Set MyQueryDef = MyDatabase.QueryDefs("Query1") 'Query Name

Sheet1.[A1].CurrentRegion.ClearContents 'Range to Clear
MyQueryDef.Parameters("[ChooseRegion]") = Sheet1.[f6].Value
Set MyRecordset = MyQueryDef.OpenRecordset 'Open the query
Sheet1.Range("A11").CopyFromRecordset MyRecordset

End Sub