Adv. 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

2. Phase 1, finishing up existing version

    - Existing feature enhancement

    - Adding new features

    - Enhancing report

    - Completion of phase I, v1.0

3. 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

4. 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

5. 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

6. 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

7. Testing and delivering

    - Load testing with large amount of data

    - Integration testing

    - Trouble-shooting

    - Performance tuning

    - Checking files, databases and indexes

    - Version control

8. Configuring automation

    - Automating your solution

    - Monitoring automation

    - Up-to-date data synchronization

9. Maintenance

    - Backing up databases and files

    - Purging and archiving

10. Finishing the project - v2.0


本页最后更新: | -- | 网站设计和虚拟主机服务 CMS