Advanced Access & Excel VBA


    Running as project hands-on driven to finish up all phases of the project

    Covering more complicated scenarios to treat complex business logic

    Combining multiple data sources together to achieve work-like goals

    More practical skills to match real working environment for multiple file procession

    Advanced VBA coding skills for delivering your work

    Multi-user control methodology to make Access Database more useful

    User and permission control to secure the environment

    More handling in database utilization by processing large amount of data

    Enhanced skills for SQL queries and managing your database using VBA + DDL

    Database and query performance tuning to handle big data


Course Outline

  1. Business requirement study and project overview

– Completing existing version

– Data sources

– Operating data

– Resulting, reporting and delivering

– Multiple user access

– Security and permission

– Data synchronization

– Automation and auto-delivery

  1. Phase 1, finishing up existing version

– Existing feature enhancement

– Adding new features

– Enhancing report

– Completion of phase I, v1.0

  1. Phase 2, dealing with multiple types of data sources

– Handling multiple databases from SQL Server

– Managing multiple Access databases

– Controlling multiple Excel files

– Logics to manipulate data

– Solution design

– Infrastructure and environment setup

  1. Phase 3, detailed design

Part I, maintaining local Access database

– Accessing source data

– Processing data

– Generating result

– Centralizing the local databases

– Concurrency control for multiple user access

– Data integrity

– Making data in sync

– Considering automation

Part II, reporting

– Exporting to reports (Excel template files)

– Delivering reports by email and network share

– Ad-hoc reports

Part III, security management

– Managing users

– Controlling permissions

  1. Phase 4A, developing the core solution

– Building VBA code

– Adding connections to all data sources

– Applying data procession logic

– Creating result

– Applying concurrency control for multiple user access

– Controlling data integrity

– User and permission management

  1. Phase 4B, developing the resulting solution

– Excel template files

– Exporting results

– Delivering reports in Excel files

– Finishing up VBA code

– Unit testing with debugging and fixing errors

– Optimizing VBA code

  1. Testing and delivering

– Load testing with large amount of data

– Integration testing

– Trouble-shooting

– Performance tuning

– Checking files, databases and indexes

– Version control

  1. Configuring automation

– Automating your solution

– Monitoring automation

– Up-to-date data synchronization

  1. Maintenance

– Backing up databases and files

– Purging and archiving

  1. Finishing the project – v2.0