Batch Update, Sync Access Tables Using Recurring External Data Source

[MAF4Fam6]

Step #13 - Create an APPEND Query to Import New Stocks (Records) into tblMainStocks Table



1. Create New Query by going to Query Design.

2. Show ONLY the tblImportStocks table.

3. Add the following Refresh fields (in same order) to Query Design View:

chrStockID
chrStockSymbol
chrCompany
curLastStockPrice
chrIndustryGroup
dblEarningsRating
chrStockRating
dtmRefreshDate
dtmRefreshTime
bytRecordStatus
dtmRecordLoadDate

4. In Query Tools/Design tab (top of the screen):

- Change the Query Type from: Select, and change to: Append
- Select the Append To Table Name: tblMainStocks, and Click OK

5. Update the "Append to:" row on ALL query fields per the following screenshot:

Attachment 634

6. Save the Query and name as: qryAddNewStocks

The attached Access 2007 database (inside zip file) is what you should have.

Continued...

-RC

[MAF4Fam6]

Step #14 - Create an UPDATE Query to Update bytRecordStatus to 99 on Records in tblMainStocks Table with Record Status 20

1. Create New Query by going to Query Design.

2. Show the tblMainStocks table.

3. In Query Tools/Design tab (top of the screen):

- Change the Query Type from: Select, and change to: Update

4. Continue building the Update query per the following screenshot:

Attachment 639

5. Save the Query and name as: qryUpdateRecordStatus99

Continued...

-RC

[MAF4Fam6]

Step #15 - Create a DELETE Query to Delete ALL Records in tblMainStocks Table with Record Status 99

1. Create New Query by going to Query Design.

2. Show the tblMainStocks table.

3. In Query Tools/Design tab (top of the screen):

- Change the Query Type from: Select, and change to: Delete

4. Continue building the Delete query per the following screenshot:

Attachment 640

5. Save the Query and name as: qryDeleteDroppedStocks

The attached Access 2007 database (inside zip file) is what you should have.

Next, we will continue building the mcrRefreshDatabase Macro.

Continued...

-RC

[MAF4Fam6]

Step #16 - Continue Building the mcrRefreshDatabase Macro

1. Open mcrRefreshDatabase Macro in Design View

2. In the Action column on Row 10, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryUpdateRecordStatus20) per the following screenshot:

Attachment 649

3. Next, in the Action column on Row 11, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryRefreshSyncStocks) per the following screenshot:

Attachment 650

4. Next, in the Action column on Row 12, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryUpdateRecordStatus30) per the following screenshot:

Attachment 651

5. Next, in the Action column on Row 13, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryAddNewStocks) per the following screenshot:

Attachment 652

6. Next, in the Action column on Row 14, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryUpdateRecordStatus99) per the following screenshot:

Attachment 653

7. Save and close Macro

Continued...

-RC

[MAF4Fam6]

Step #17 - Continue Building the mcrRefreshDatabase Macro

1. Open mcrRefreshDatabase Macro in Design View

2. In the Action column on Row 15, type: OpenQuery
- Update the OpenQuery's Action Arguments (qryDeleteDroppedStocks) per the following screenshot:

Attachment 654

3. Next, in the Action column on Row 16, type: MsgBox
- Update the MsgBox's Action Arguments per the following screenshot:

Attachment 655

4. Save and close Macro

The attached Access 2007 database (inside zip file) is what you should have.

There is ONE final step before we finish this Macro. But first, we must build our FINAL three queries.

Continued...

-RC

[MAF4Fam6]

Step #18 - Create Three Select Queries From tblMainStocks Table

1. The following calculated field is provided, for you to copy/paste into the three Queries:

dtmRefreshDateTime: Format([dtmRefreshDate],"Medium Date") & ", " & [dtmRefreshTime]

2. Create the first Query by going to Query Design.

3. Show the tblMainStocks table.

4. Continue building the Select query per the following screenshot:

Attachment 664

5. Save the Query and name as: qryMainStocks

6. Next, create the second Query by going to Query Design.

7. Show the tblMainStocks table.

8. Continue building the Select query per the following screenshot:

Attachment 665

9. Save the Query and name as: qryNewImportedStocks

10. Next, create the third, and final Query by going to Query Design.

11. Show the tblMainStocks table.

12. Continue building the Select query per the following screenshot:

Attachment 666

13. Save the Query and name as: qryStocksXForDeletion

Next, we will complete the last, and final step to our Macro.

Continued...

-RC

[MAF4Fam6]

Step #19 - Finish Building the mcrRefreshDatabase Macro

Our last, and final step in building the Macro must perform the following task:

During the database refresh process: cancel the entire procedure IF there are any stocks on the running watch list that are marked for deletion; stock traders must remove these stocks from the watch list before the next database refresh is allowed to be executed.

Do we need to create another query to Select these type of stocks that are marked for deletion? No. There should be no reason to create another query. If you recall from our last step, the qryStocksXForDeletion query does this for us already. Therefore, we will need to incorporate this query into our final step in the Macro.

1. Open mcrRefreshDatabase Macro in Design View.

2. Insert TWO rows between the SetWarnings Macro Action and first OpenQuery Macro Action per the following screenshot:

Attachment 674

- Macro's Row 6 and Row 7 should now be empty.

3. In the Action column on Row 6, type: MsgBox
- Update the MsgBox's Condition Statement to:

DCount("[chrStockID]","qryStocksXForDeletion")>0

- Then, update the MsgBox's Action Arguments per the following screenshot:

Attachment 675

4. Next, in the Action column on Row 7, type: StopMacro
- Update the StopMacro's Condition Statement with three periods: ...

Refer to the following screenshot for visual:

Attachment 676

5. Save and close Macro

The attached Access 2007 database (inside zip file) is what you should have.

The Macro is now finished. We are ready to test the database in our next step.

Continued...

-RC

[MAF4Fam6]

Step #20 - Test the Database

1. Open the tblMainStocks table and ensure you have ZERO records.
- Close the tblMainStocks table

2. Make sure you saved the SelectedStocks.xls file (provided in Post #4) in the correct destination on your hard drive in accordance with the Macro's TransferSpeadsheet's Action Arguments (Post #9).

3. Run the mcrRefreshDatabase Macro, and Click OK.
- Click No when asked to Continue, and ensure you receive the MsgBox telling you: Stocks Database Refresh Cancelled!

4. Run the mcrRefreshDatabase Macro again, and Click OK.
- Click Yes when asked to Continue, and ensure you receive the MsgBox telling you: Database Refresh completed successfully!

5. Open the qryMainStocks query (Selects ALL stocks that exist on the SelectedStocks.xls file).
- You should have 73 records
- Scroll over to the bytRecordStatus field; ALL 73 records should show a Record Status 10 (New Imported Stocks)
- Close the query

6. Open the qryNewImportedStocks query (Selects ALL new imported stocks during the last database refresh).
- You should have 73 records in Record Status 10
- Close the query

7. Open the qryStocksXForDeletion query (Selects ALL Watch List stocks marked for deletion).
- You should have ZERO records
- Close the query

8. Open the qryMainStocks query.
- Update/Add the ESRX (stock symbol) and GOOG (stock symbol) stocks to the current Buy Watch List (Go to field name: blnBuyWatchList)
- Close the query

9. Open the SelectedStocks.xls file.
- Delete the following stocks (records): ESRX, GOOG, MRVL, SNDK
- Save, and Close the Excel file

10. Run the mcrRefreshDatabase Macro, and process the database refresh.

11. Open the qryMainStocks query.
- You should now have 69 records (73 stocks minus 4 stocks we deleted from the SelectedStocks.xls file)
- Scroll over to the bytRecordStatus field; ALL 69 records should show a Record Status 30 (Refreshed/Synced Stocks)
- Close the query

12. Open the qryNewImportedStocks query.
- You should have ZERO records
- Close the query

13. Open the qryStocksXForDeletion query.
- You should have 2 records (ESRX and GOOG)
- These stocks were on the current Buy Watch List, and then dropped off the SelectedStocks.xls file
- Close the query

14. Run the mcrRefreshDatabase Macro, and process the database refresh.
- You should have received the MsgBox telling you: Database Refresh cancelled!
- These are stocks (marked for deletion) that must be removed from the Buy Watch List before performing the database refresh

15. Open the qryStocksXForDeletion query.
- Remove the ESRX and GOOG stocks from the current Buy Watch List (Go to field name: blnBuyWatchList)
- Close the query

16. Open the qryStocksXForDeletion query.
- You should now have ZERO records
- Close the query

17. Run the mcrRefreshDatabase Macro, and perform the database refresh.

18. Restore the ORIGINAL SelectedStocks.xls file (contains 73 records) and overwrite your existing Excel file on your hard drive.

19. Open the SelectedStocks.xls file.
- Make a few obvious changes to 3 or 4 records (Columns B though G (ONLY)); keep notes on your changes
- Save, and Close the Excel file

20. Open the qryMainStocks query.
- Make a few obvious updates (3 or 4 records) to any of the following local fields (ONLY):

blnFirmOwnsShares
chrStockRec
blnBuyWatchList
curStockBuyPoint
memRemarks

- Close the query

21. Run the mcrRefreshDatabase Macro, and perform the database refresh.

22. Open the qryMainStocks query.
- You should now have 73 records (69 records plus 4 new imported stocks)
- Scroll over to the bytRecordStatus field; 69 records should show a Record Status 30, and 4 records should show a Record Status 10
- Locate ALL the obvious changes you made to the SelectedStocks.xls file; fields are refreshed/synced to match Excel file's refresh data
- Locate ALL the obvious updates you made to local fields; fields are left untouched during the database refresh process
- Close the query

23. Open the qryNewImportedStocks query.
- You should have 4 records in Record Status 10
- Close the query

24. Open the qryStocksXForDeletion query.
- You should have ZERO records
- Close the query

This concludes the test portion of the database.

Continued...

-RC

[MAF4Fam6]

This concludes the Tutorial on this subject.

From here, your next step is to begin creating your Forms. How you create your Forms is entirely up to you and your individual preferences.

1. Create a Form (recommend Form wizard) using one (any) of the following record sources:

qryMainStocks
qryNewImportedStocks
qryStocksXForDeletion

2. Make two additional copies of your first Form and change the record source to a different query (above). The intent is to have three Forms (one for each query).

3. Then, create a Form (or Switchboard) so you can launch (OnClick events) the following objects from command buttons (respectively).

mcrRefreshDatabase Macro
frmMainStocks Form (created above)
frmNewImportedStocks Form (created above)
frmStocksXForDeletion Form (created above)

4. Finish

Final Remarks:

I hope that this tutorial was fun and informative for you, all at the same time. As stated before: you may find, through more experience in Access, of other and even faster ways to accomplish this process. I hope this tutorial has given you some of the tools you will need, to go out there and explore some of other options Ms Access has to offer.

This is what Ms Access is all about: AUTOMATION

Thank you!

-RC

[thorsonb]

Thank You!

[MAF4Fam6]

You are very welcome!

-RC

[jclo2go]

As I understand it, this would be based on the idea that the file name that contains the information is the same each time you want to refresh. What if it were to change? Is there a way to prompt the user to select the ANY version of the stock file to be used?

For instance, I have a file folder that contains many CSV files with identical table layouts (only the records are different). How can I choose which CSV file I want to use?

[lflatley]

I'm going to try this myself, very interesting!

[liebor]

I reckon you use the language and expressions in a fairly complex example...such a 'tutorial' is comprehensive to rather advanced users that do not need a tutorial but just a hint or a little inspiration.

[nyowanowich]

This is a great and very useful tutorial. It really helped me get the basic concept of what I am trying to make. So as I am playing with this and tweaking it I was wondering what would be the way to show all the deleted records that were in the table before the refresh but that are no longer there after the refresh? (regardless of them being marked for deletion) And I assume it has to do with the Marked for deletion and Deleted queries...

Thanks.