Thread: Batch Update, Sync Access Tables Using Recurring External Data Source

Due to the special interest on this subject, I have decided to post a tutorial on how to accomplish batch updating/syncing tables in Access using an external data source (i.e. Excel file, text file, etc.). Currently, Access provides no automated way to perform this task, so I will be programming Access to automate this process via a click of a button on a form.

As you become more experienced in Access, you will find that there are other, perhaps even faster ways to accomplish this process. So, by all means; explore some of the other options in Access and have fun.

This tutorial will provide you with step-by-step instuctions along with plenty of screenshots to walk you through the process visually. I will be using Access 2007 along with Macros and Update/Append/Delete queries. These instructions should also be compatible in Access 2003. Please note: VBA will not be utilized in this tutorial, simply because I am not an expert at it.

Continued...

-RC

For the past 13 years, myself and others have been calling this process (in the subject area), "refreshing the database". Refreshing the database is essentially programming Access to fully automate the processing of a recurring file(s) (external data source) into a database, while at the same time, updating/syncing applicable tables and fields, adding new records to tables from the external data source, and identification/deletion of records in tables that no longer exist on the external data source.

If you are currently updating your database manually by cross-referencing (eye-balling) your tables with your external data source, then this tutorial can be especially beneficial for you, and perhaps your employers/employees.

Continued...

-RC

The following is our scenario for this tutorial:

1. Our stock investment firm recently stood up operations.

2. Once a week, we are given a list of stocks in an Excel spreadsheet (file is exported from an online brokerage firm and given to us).

3. The spreadsheet contains only top US growth companies in the Top 50 Industry Groups by stock performance.

4. Our investment firm is only allowed to purchase stocks from the most current weekly spreadsheet.

5. The number of stocks (records) in the spreadsheet will inevitably change on a week-to-week basis. You should see a few NEW stocks make the list every week, while at the same time, you should see a few stocks get CUT from the previous list (hence no longer applicable for trading purposes).

6. We are given the task of building an Access database along with the following instructions:

a) Build a database utilizing the weekly Excel file

b) Fully automate the database refresh process at the click of a button upon reciept of a new weekly spreadsheet

c) Stock traders must be able to add/remove stocks from a running watch list of stocks (geared towards potential stock purchases)

d) Stock traders must be able to document the firm's Buy/Hold/Sell recommendations on ALL stocks in the database

e) Stock traders must be able to identify ALL newly imported stocks resulting from the latest database refresh at the click of a button

f) During the database refresh process: delete stocks from the database that no longer exist on the weekly spreadsheet with the exception of watch list stocks (mark these stocks (records) for deletion instead)

g) Stock traders must be able to identify ALL stocks on the running watch list that are marked for deletion during the latest database refresh at the click of a button; delete these stocks from the database immediately after a stock trader removes them from the running watch list

h) 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.

To reiterate...I will be using Access 2007. However, these instructions should also work in Access 2003. The weekly Excel file is 2003 format.

Let's have fun building the database.

Continued...

-RC

Step #1 - Analyze External Data Source

1. Open the attached Excel file (inside zip file). The file contains 73 records, not including the row containing field names. In a real-world situation, the number of records on this spreadsheet would easily range between 200-400. So, you can just imagine how many man-hours (and $$$) are saved by incorporating such an automated (refresh) process in your database.

2. Please do not make any changes to the field names. Remember, the field names will be the same field names every week when you receive a new file from the investment firm.

3. Check to see if the file contains any duplicate RECORDS. There are NO duplicate records in this file since they are uniquely identified by the CUSIP Number. The CUSIP number consists of a combination of nine characters, both letters and numbers, which acts as a sort of DNA for the security, uniquely identifying the company. The stock symbol could not be a unique identifier since a stock symbol may change at any time whenever a company submits a change to their name, and it's subsequently approved.

Warning: If the external data source in YOUR real-world situation contains duplicate records, then this tutorial will not work for you. In order for this tutorial to work, your external data source must contain a unique identifier and must NOT contain any duplicate records.

4. Take a good look at ALL the different data types on the Excel file, and try to anticipate how Access will classify each data type during the import process. Just because you are certain that a column of data in your external data source is a text data type, does not necessarily mean that Access will classify it as such. Let's take a look at Column F, for instance, on the spreadsheet. All of the data in this column seems to point to numbers right? But what IF the possibility exists that an alpha character may exist in a future Excel file for this field. If you import the field into Access now, it will be classified as a number because all you have NOW is numbers. But, in a future update to the spreadsheet when alpha characters begin to show in this field, Access will reject the data during the import process because the field in Access will be an incorrect data type. My point is simple: ensure that you understand ALL of your external data source's data types from day one of designing your database, to prevent any problems in your database refresh process.

Continued...

-RC

Step #2 - Create New Blank Database and Import Excel File

1. Create a New Blank Database, and Name: StocksDbForums

2. Use the wizard in Access to import the Excel file:

a) Browse, locate and select the Excel file on your hard drive

b) Select: Import the source data into a new table in the current database, and Click: OK

c) Select: First Row Contains Column Headings, and Click: Next

d) The CUSIP Number field should be automatically highlighted; Change Indexed to: Yes (NO Duplicates), and Click: Next

e) Click the: Choose My Own Primary Key option button, select CUSIP Number, and Click: Next

f) Change the default table name to: tblImportStocks, Click: Finish, and Click: Close

Continued...

-RC

Step #3 - Add Four Fields to tblImportStocks Table

1. Open the tblImportStocks table in Design View

2. Primary Key should be assigned to: CUSIP Number

3. Add the following field names (same order) to the end of the table:

Refresh Date
- Change Data Type to: Date/Time
- Change Format to: Medium Date
- Change Default Value to: Date()

Refresh Time
- Change Data Type to: Date/Time
- Change Format to: Medium Time
- Change Default Value to: Time()

Record Status
- Change Data Type to: Number
- Change Field Size to: Byte
- Change Default Value to: 10

Record Load Date
- Change Data Type to: Date/Time
- Change Format to: Medium Date
- Change Default Value to: Date()

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

WHAT YOU SHOULD KNOW OR BE AWARE OF:

1. DO NOT make any changes to any field names (fields 1-7) or change their particular order in any way. Doing so, will cause the database refresh process to fail. Remember, fields 1-7 will be the same fields (same order) every week when you receive a new Excel spreadsheet.

2. The four fields we added (above), should ALWAYS go at the end of this table, and should NEVER be moved from this order.

3. The Record Status field, as you may have guessed, is a record status indicator which is the most important field in the database refresh process. The database refresh process would not work without some type of record status indicator.

The following Record Status Indicators (all-inclusive) are used in the database refresh process:

10 = RECORD ADDED; new record on our weekly spreadsheet that did not exist in our database
20 = RECORD MARKED FOR DELETION; record in our database that no longer exists on the weekly spreadsheet
30 = RECORD REFRESHED; record in our database that also exists on the weekly spreadsheet--fields SYNCed to match spreadsheet data
99 = RECORD DELETED; record was marked for deletion (Record Status 20) and DID NOT have any pending actions--DELETE RECORD from database

Next, we will begin building our Macro to intiate the Database Refresh process.

Continued...

-RC

Step #4 - Begin Building Macro to Initiate Database Refresh

1. Create New Macro

2. In Macro's Design View, enable to the following Design tools (located in Macro Tools/Design tab):

- Show All Actions (Only in Access 2007)
- Conditions

Refer to the following screenshot for visual:



Please make note of all the different areas in the Macro screen for future reference.

3. In the Action column on Row 1, type: MsgBox
- Update the MsgBox's Action Arguments per the following screenshot:



4. Next, in the Action column on Row 2, type: MsgBox
- Update the MsgBox's Condition Statement to:

Code:

MsgBox("Do you want to continue with Stocks Database Refresh?",4,"Microsoft Access Forums")<>6

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



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

Refer to the following screenshot for visual:



6. Next, in the Action column on Row 4, type: Hourglass
- Ensure the Hourglass' Action Arguments (Hourglass On) is set to: Yes

7. Next, in the Action column on Row 5, type: SetWarnings
- Ensure the SetWarnings' Action Arguments (Warnings On) is set to: No

8. Save the Macro and name as: mcrRefreshDatabase

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

Towards the end of this tutorial, we will be running the Macro from a command button on a form. In the meantime, feel free to double click the Macro to see how it runs. Besides initiating the database refresh process, the Macro (this far) gives the user the opportunity to cancel the Refresh process. We want to make absolutely sure, this is what the user intends to do.

We will come back to this Macro and continue to build on it as we go through the rest of the tutorial.

Continued...

-RC

Step #5 - Create a DELETE Query to Remove ALL Records from tblImportStocks Table

1. Create New Query by going to Query Design.

2. Show the tblImportStocks 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:



5. Save the Query and name as: qryClearImportStocks

Continued...

-RC

Step #6 - Edit mcrRefreshDatabase Macro and Add OpenQuery and TransferSpreadsheet Actions

1. Open mcrRefreshDatabase Macro in Design View

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



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



4. Save and close Macro

5. Run the Macro.

6. Once the Macro stops running (hourglass will disappear in less than a second), Open the tblImportStocks table. Since the Excel file has not been modified, you should still show 73 records in this table. However, notice how the last four fields (Refresh Date, Refresh Time, Record Status, and Record Load Date) are now automatically populated with their default values.

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

We will continue building this Macro in our next step.

Continued...

-RC

Step #7 - Edit mcrRefreshDatabase Macro - Add MsgBox and StopMacro Actions

1. Open mcrRefreshDatabase Macro in Design View

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

Code:

DCount("[CUSIP Number]","tblImportStocks")=0

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



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

See StopMacro Action on Row 3 for example on updating the three periods

4. Save and close Macro

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

WHAT YOU SHOULD KNOW OR BE AWARE OF:

1. You must understand our Macro's objective from here. Its objective from here is to take ALL records (73 records) from our tblImportStocks table and update the tblMainStocks table (main table will be created in our next step). We need Access to: 1) SYNC fields of records that exist BOTH on the tblImportStocks table (weekly spreadsheet) and the tblMainStocks table, 2) APPEND any records from the tblImportStocks table into the tblMainStocks table that DO NOT already exist there, and 3) DELETE any records from our tblMainStocks table that DO NOT EXIST on the tblImportStocks table (I like to refer to these records as "dropped records").

2. Why add the above steps to our Macro?
Let's say your database is currently tracking 73 stocks based on the last database refresh. Your employer forwards you the new refresh file of the week. You immediately attempt to run the database refresh process using the new refresh file, not knowing the refresh file contains ZERO records (only field names). Something must have gone wrong when the refresh file was extracted from the online brokerage service. What are the repercussions? In accordance with the last paragraph above (3), the refresh process will DELETE ALL records from the tblMainStocks table due to the fact that ALL these DELETED records NO LONGER EXISTED on the weekly spreadsheet. In other words, you just lost ALL your data in your database which is now unrecoverable unless you backed up your database. Tough lesson to learn! Therefore, to prevent such data loss, we had to tell Access to stop the refresh process IF no records are found in the tblImportStocks table after the Macro's TransferSpreadsheet Action.

Feel free to take the Macro for a spin. But first, remove ALL RECORDS from the Excel spreadsheet EXCEPT FOR THE FIELD NAMES and run the Macro. Access should throw the critical pop-up window telling you there were NO records found. The Macro will stop running at this point in the refresh process. And, we can now sleep at night knowing that we will not lose any data during a database refresh.

Restore the previous Excel spreadsheet containing ALL records and run the Macro again. You should see ALL 73 records restored back in your tblImportStocks table.

We can now move on to creating our main tblMainStocks table.

Continued...

-RC

Step #8 - Copy/Paste tblImportStocks Table and Create Main Table

1. COPY the tblImportStocks table and PASTE new Table, Name: tblMainStocks
- In the Paste Options, select: Structure Only, and Click OK

2. Open the NEW tblMainStocks table in Design View

3. Rename ALL field names:

- Change CUSIP Number to: chrStockID
- Change Symbol to: chrStockSymbol
- Change Company Name to: chrCompany
- Change Price to: curLastStockPrice
- Change Industry Group Name to: chrIndustryGroup
- Change Earnings Rating to: dblEarningsRating
- Change Stock Rating to: chrStockRating
- Change Refresh Date to: dtmRefreshDate
- Change Refresh Time to: dtmRefreshTime
- Change Record Status to: bytRecordStatus
- Change Record Load Date to: dtmRecordLoadDate

Refer to the following screenshot for visual:



WHAT YOU SHOULD KNOW OR BE AWARE OF:

1. This is your opportunity to change the field names in YOUR main table.

2. The ORDER in which you see the above fields must REMAIN UNCHANGED. Changing the order will cause the refresh process to fail.

Next, we will Add our local fields to the tblMainStocks table.

Continued...

-RC

Step #9 - Add Local Fields to tblMainStocks Table

Up to this point, the tblMainStocks table contains 11 fields. I refer to these fields as "refresh fields". Refresh fields should NEVER be updateable by a user unless absolutely necessary. The reason is simple. Every time you process a new database refresh, the data inside refresh fields will be overwritten by what is imported from the new Refresh file. Therefore, if feasible, lock ALL refresh fields from update in Forms.

What are "local fields" then?
Local fields are ALL the other fields, other than the refresh fields. These fields are not affected (left untouched) during the database refresh process. They will normally be updated by a user in Forms.

1. Open tblMainStocks table in Design View

2. Add the following local fields to the end of the table:

blnFirmOwnsShares
- Change Data Type to: Yes/No

chrStockRec
- Change Data Type to: Text

blnBuyWatchList
- Change Data Type to: Yes/No

curStockBuyPoint
- Change Data Type to: Currency

memRemarks
- Change Data Type to: Memo

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

We are now ready to finish building the remainder of our queries to complete the database refresh process from within our Macro.

Continued...

-RC

Step #10 - Create an UPDATE Query to Update bytRecordStatus field to 20 on ALL records in tblMainStocks Table

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:



5. Save the Query and name as: qryUpdateRecordStatus20

Continued...

-RC

Step #11 - Create an UPDATE Query to Refresh (Sync) Fields in tblMainStocks Table

1. Create New Query by going to Query Design.

2. Show both the tblImportStocks AND tblMainStocks tables.

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

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

4. Join both tables by their Primary Keys: CUSIP Number-------------------------chrStockID

Refer to the following screenshot for visual:



5. Add the following Refresh fields (in same order) to Query Design View from tblMainStocks table:

chrStockSymbol
chrCompany
curLastStockPrice
chrIndustryGroup
dblEarningsRating
chrStockRating
dtmRefreshDate
dtmRefreshTime
bytRecordStatus

Note: DO NOT Add the chrStockID (Primary Key) and dtmRecordLoadDate fields to Query

Refer to the following screenshot for visual:



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



Shortcut: Use copy/paste from one column to the next and just change the field names.

7. Save the Query and name as: qryRefreshSyncStocks

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

Continued...

-RC

Step #12 - Create an UPDATE Query to Update bytRecordStatus to 30 on Records in tblMainStocks Table with Record Status 10

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:



5. Save the Query and name as: qryUpdateRecordStatus30

Continued...

-RC