This blog will help you to learn quick tricks on how to refresh Access database automatically. Besides that, I will also share solutions to refresh Access table, form, web app data automatically.
Without wasting any more time let’s dive into the post.
Does Access Database Save Automatically?
By default, MS Access combines forms to data and it will automatically save up your data when you either close the form or switch between the records.
It’s a plus point for all those users who don’t have the habit of saving up the data after making any changes.
Another option is to use the BeforeUpdate event of Access forms. This will ask the user whether they want to save the changes or not.
Here is an example of the BeforeUpdate event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intAns As Integer
intAns = MsgBox(“Are you sure you want to save this record?”, vbQuestion + vbYesNo, “Save Record”)
If intAns = vbNo Then Cancel = 1
If you are using the above method and when the message box pop-up just hit the NO option and ESC key for reverting back to the changes you have entered.
Or else it will repeatedly show you the dialog box after moving up the record.
What Are The Benefits Of Refreshing Access Database?
Before exploring the ways to refresh Access database automatically it’s important to know the benefits of refreshing Access database.
- Refreshed and updated data will help you out to streamline business practices in a more enhanced way.
- High quality and refreshed data result in a better and faster working style. Whereas, the old data can harm your company image and perception for which you worked very hard.
- Clear and updated data improves results accuracy.
- Keeping updated data works in making faster and easier accessing of data.
- It will be easier for you to track and extract any records detail.
What Are The Difference Between Refresh And Requery In Access?
The refresh process mainly updates all the existing data of your form or datasheet. The refresh method only displays the changes you have made in the current recordset. It doesn’t show any deleted or new records within the record source.
Requery method updates all the data present in the control or forms to show all the records which get added or deleted from the record source from the time it last queried.
Following tasks performed by the Requery method:
- It Reruns the query which is the main base for all access form or control.
- Displays all the new or modified records.
- Updates records will be shown which is based on the changes done to the Access form Filter
- Executing the requery process takes time and it moreover depends on the recordset size.
How To Refresh Access Database Automatically?
Method 1# Refresh Data In Access Database
Here are three simple tricks to refresh Access database data quickly and effortlessly.
1. For refreshing the records present in Form or Datasheet view:
- Tap to the Home tab, and get into the Records group.
- From this group click Refresh All icon, after that hit the Refresh button.
2. To refresh PivotChart or PivotTable view records:
- Tap to the Analyze tab, then go to the Data group. After that tap to the Refresh Pivot option.
3. Or simply you can press the SHIFT+F9 button from your keyboard.
Method 2# Set Time Interval For Data Refresh
Another method to refresh the Access database automatically is by setting the time interval for Access data refresh.
By default, the Access database refreshes the data every 60 seconds. You can set this time manually; here are the steps to be followed to do such changes.
- In the opened window of your Access database, make a tap on the MS Office button that is present in the left corner.
- This will open the drop-down options; here you have to hit the Access Options.
- From the left pane of the opened Access Options dialog box, hit the Advanced tab.
- In the opened right pane go to the Advanced section, here you will see the Refresh interval (sec) option. Assign the value for which you want to set for Access database refresh.
Method 3# Using The Macro To Refresh Data
If you want that your data will be refreshed automatically when any event takes place then you need to create a macro after then attach it to the event.
Suppose, you want to refresh your Access form data whenever the form gets the focus. In that case, make a macro and then attach it to the On Got Focus event of your form.
Here are the following steps that you need to perform:
- Go to the Create tab first and then from the Other group hit the Macro icon.
Or you can hit the arrow present below the macro icon; here you will see three options macro, Module and Class Module. From which you have to choose the macro.
- Tap to the arrow key present next to the green (+) plus symbol. After that click the Requery option.
- It’s time to save your macro, with the name Requery. Now close the Macro Builder.
- After that open the form which you want to keep in your Design view. If you are unable to get the property sheet then press F4 from your keyboard to show it.
- Ensure that the form is selected by itself. After then on the property sheet, tap to the Event.
- Now from the event tab, Tap to the arrow present in the On Got Focus property
- Tap to the macro name Requery and save your form.
Method 4# Using Code To Refresh Data
Using the code you can also refresh Access database data automatically. Suppose you need to refresh the data of your form each time when the form gets the focus. For this, you have to create a module first and then attach it with the form On Got Focus event.
Here is the step that needs to be followed:
- Open your Access database Form which you need to show in the Design view.
- If your property sheet doesn’t appear opened then press F4 to display this.
- Ensure that the form is selected by itself. After then on the property sheet, tap to the Event
- Now on the Event tab go to the On Got Focus property and hit the Build button.
- In the opened window of Choose Builder dialog box, tap to the Code Builder.
- This will open up a new module in the Visual Basic(VB) Editor.
- Just type the Requery, and after that close the Visual Basic (VB) Editor.
- Don’t forget to save your Access form.
How To Refresh Data In An Access Web App?
Access web app queries mainly run over the server. So you need to make use of the RequeryRecords macro to refresh data in the Access web app. This RequeryRecords macro only needs the current recordset.
- Open your Access database form which you want to use, after that tap to the Actions and then hit on the option “On Current”.
- Now from the Action Catalog, you need to hit the RequeryRecords.
If you left the “Where=” blank then additional filtering is not done on the result.
If you leave “OrderBy=” as blank, then no sorting will be done.
- At last, save your form.
How To Automatically Refresh A Linked Table In Access?
Well refreshing the data source and its associated linked tables ensure that your data source and the linked table is working correctly.
- Choose the External Data> Linked Table Manager.
Tip : for refreshing some specific linked table from the navigation pane. Just make a right-click and then tap to the Refresh Link. Place the cursor over the linked table name to watch out the connection string and other detail.
- In the opened dialog box of Linked Table Manager you have to choose the linked tables or data source.
Well if you choose the data source then all its linked tables automatically get selected.
Click on the (+) sign present in the Data Source to choose each separate linked tables.
- Tap to the Refresh option.
- If you are getting problem due to the location of the data source then enter the right location by making changes in the edit data source
- Make sure the Status column is completely visible to you and check what status you got from the following:
- Success : this shows that all the linked table has been successfully refreshed.
- Failed : it means there is some problem accused in the linked tables.
Behind this failed status the reason can be changed table name or addition of any new credentials.
Check out this blog to know: how to fix Linked Table Manager Not Working issue
- Choose the Refresh button again unless you fix all the linked table failure issue. Or until your status column shows the “Success” message.
How To Automatically Refresh Form Or Control Data In Access?
Requery method updates all the data underlying in the specific form just by requerying the data source for the Access form.
Here expression is the variable that represents the Form object.
Well using this method you can easily show the most updated data.
Controls which are based on the query or table must have included with:
- Subform controls.
- List boxes and combo boxes.
- OLE objects, such as charts.
- Controls in which the ControlSourceproperty setting should have included with SQL aggregate functions or domain aggregate functions.
If you want to specify any other control for your specified object using the “expression”, then record source for the form gets very much important.
But if your specified object in “expression” is not bound to any field in the query or table then the Requery method forces for the control recalculation.
If you remove the specified object specified in expression, then Requery method will requeried the data source for your focused control or form.
In case the control has row source, focus, or record source then it will be requeried. Or else the data of the control simply take refresh.
If your subform control is having the focus, then this option will only requeries the source of the record for subform, not for the parent form.
After following the above-mentioned tricks, you can easily refresh Access database automatically.
If unfortunately, you fail to fetch your access database data even after applying the refresh process then try the Access Database Repair N Recovery software. It is the best tool to recover linked tables, modules, forms, tables, indexes, reports, queries, relations, deleted records, and macros. Besides that, the software efficiently repairs corrupt MS Access (MDB/ACCDB) files safely and securely.
Don’t forget to leave a comment below with suggestions or questions.