• Twitter @PowerSheetAI
  • Facebook for PowerAccess |
  • LinkedIn for PowerAccess | PowerSQL
  • PowerAccess|PowerSQL YouTube Channel
  • Instagram for PowerAccess | PowerSQL
  • Pinterest for PowerSheet.ai
Free Excel BI Dashboards Database Builder AI Add-in Tools Pivot Reports Data Entry Forms Tables

© 2019 PowerSheet.ai

Solutions

Solutions & Use Cases

PowerAccess Power Template

Out-of-box Common Access Database Features

Database Building Accelerated

New Auto Database Builder Tools | Functions | Templates | Features | CodeGen | New Tools like Global Find & Replace (across All Queries/VBA/Data/Names) & Smart Rename | PowerGit | Auto-Generate VBA/SQL/Tables/Data Macros

Excel to Databases Simplified

 Bring Power Query & Excel-like Functions for Access
| Convert Excel-to-Access Databases (Formulas to SQL Queries)
| Intuitive, Fast, Previous Row-based "Excel Formulas" for Query Design
with XPrevRow(), XNPV() & more

Microsoft Access
Modernized, Automated

Edit Tracking & Undo | User Permissions | VBA Framework | In-Memory Caching | 800+ New Functions / Events / Macros | OnExit | Global Find & Replace | Full VBA Source | Logging, Telemetry & Diagnostics

PowerGit
Easy Auto Git Versioning

Simplified, Automated Versioning and Auto Merge for Collaborative Editing of VBA, Form, Report, Query, Table & Data Macros with PowerGit Add-in for Access, Excel & Office
| Auto Run, Calculate, Cache, Sync, Import (PDF, Excel, CSV) | Export

Cloud, Web, Mobile,
Mac & iPad
Enabled

iPad | Web Browsers | Mac | iPhone | Android | Cloud Hosting |
| Cloud Sync to SQL Server & Azure | Calculated Tables | Lookup Caching | Auto Migrate Data to SQL Server or Azure (as Linked Tables for use with Access Forms & Reports)

PowerSQL
BI + Excel Formulas

500+ XLookup / QCriteria / Excel / Power Query-like Functions | PowerSQL | QComment | QParam | XNPV | No Quoting | No Null Checks | Memory-Cached Lookups
{Field, Value}.. | QIsBetween() | QIsAny() criteria parameters

PowerAccess Benefits

Database Building Accelerated

 
New Auto Database Builder Tools Functions | Templates | Features
Auto-Generate VBA, SQL, Tables, Data Macros & more

Excel Users Empowered

 
Excel Formulas for Query Design | XPrevRow()
Excel-to-Access / SQL / Cloud conversion

Cloud, Web & Mobile Enabled

iPad | Web Browsers | Mac | iPhone | Android
Cloud Hosting | Access Web Apps (AWA) Conversion

Microsoft Access Modernized

 
Edit Tracking & Undo | User Permissions | VBA Framework | In-Memory Caching
800+ New Functions / Events / Macros | OnExit | Global Find & Replace | Full VBA Source

Queries & Dynamic Lookups Simplified

PowerSQL | 500+ XLookup / QCriteria / Excel / Power Query-like Functions
No Quoting | No Null Checks | QComment | QParam | XNPV | Memory-Cached Lookups
{Field, Value}.. | QIsBetween() | QIsAny() criteria parameters

Solutions Automated

Auto-cache Queries to CalculatedTables / Azure / SQL Server | End-User Customization
Change Triggered / Scheduled Auto Run | Auto Import / Export / Sync of Data / PDF / Image

 

 

PowerAccess Technical Features

The PowerAccess™ Framework brings PowerSQL™ to Microsoft Access, along with support for:

  • Database Creation Simplified - for Everyone
    • Convert Excel Formulas to Queries - as SQL for easy visual editing with Query Designer

    • Empower Subject Matter Experts to build own tools & databases - without Access / SQL experience

    • Microsoft Access made Accessible to anyone who can use Excel

​​
  • Modernize Access
    • with New Time-saving Tools, Generation and Automation​​

  • Reduce Time, Cost & Complexity
    • New powerful tools, automation & functions save time for Access Pros, Consultants, Power Users & Novices Alike

  • Faster & Easier to Build than Excel
    • for Financial Models, BI Dashboards and Ad-hoc or Automated Reporting

    • Modernize and Accelerate use of Microsoft Access with new, faster, easier-to-use and more powerful tools, automation and functions

​​
  • Finally Can Use ALL Queries outside of Access - with Excel, Power BI, PowerApps & more
    • Automated Query Result Caching to Calculated Tables™

    • Cloud Sync to Azure, SQL Server & more and Query Result Caching

​​
  • Up to 100x Less Work to Maintain, Extend, Evolve vs. Excel 
    • Centralized Query definitions + PowerAccess' simplified "PowerSQL" Excel formula-like field definition expressions

    • Avoid time-consuming, error-prone repeated edits & copy-pasting for same formula spread across dozens of sheets - duplicated across each of the dozens of user/group-specific workbook copies
       

  • Users & Permissions

    • Define Viewing/Editing Permissions & Manage Assignments to User Groups/Roles/Projects

    • Password Login & Authorized PCs. Users only with PowerAccess–after removal from Microsoft Access
       

  • Advanced Change Tracking & Undo

    • Edit/Change Tracking, Edit Log/Comments & Undo/Rollback
       

  • iPad, Mobile, Mac, Web & PC

    • PowerAccess databases can be used anywhere & our consulting services can help you to deploy
       

  • 800+ New Functions for Excel Formula/PowerQuery- like Design & Up to 20x Less SQL/VBA
    • XPrevRow(), XLookup(), XPositiveOrZero(), XNearestUpToOrMin(), XSumToMonth(), XQTD(), XNPV(), XTreasuryRate() & hundreds more for use in Query Designer, Macro Designer, SQL & VBA

    • Easily Find or Limit XLookups by any number of (FieldName, Value) pairs and Q Criteria (QIsBetween(), QIsAnyOf() etc.) (and without needing to quote strings/dates or add null checks
       

  • Flexible Templates Included or Available
    • Quote / Estimate Building, Invoicing & POs, Sales Analytics, CRM, Project Planning, Budgeting, Compensation (Salary, Payroll, Commission, Bonus) Calculation, Accounts Receivable/Payable, Vendor Management, Finance (FP&A, Accounting, Forecasting)
       

  • No Need to Involve IT or Developer for Every Change - Or at All
     
  • Support & Development Services by World-class Microsoft Access Expert

  • Excel-like Formulas for use in SQL Queries, Query Designer, Macro Designer and VBA

    • PowerSQL syntax for Previous-Row-based Excel-like formulas for Microsoft Access

    • XNPV() and many other Excel functions

    • XQTDSum(), XYTDToDate(), XNPVSum/ToDate(), XSumToDate(), XSum(), XNearest and other dynamic sum/subquery and /lookup functions

    • Some functions like XNPVToDate() XNPVToMonth() use a default field name like "Date" or "MonthID" so that you don't have to keep repeating these, and you can override that and many other behaviors in app settings
       

  • XPrevRow() for powerful, yet easy-to-use Excel-like relative-row references

    • Using either multiple XPrev() calls + one shared XRow() call to prevent repeating the same parameters, or using standalone XPrevRow() calls

    • XNPV(), XIncrement, XQTD, XYTD and many more functions are provided based on XPrev()

    • These all have a field name parameter plus an XRow() parameter in place of the query name + Date/RowID/MonthID + optional Group by field value and name pairs.
       

  • Up to 1000x faster Subqueries and Dynamic Lookups with On-the-fly Caching

    • for near Instant Scrolling of Query results instead of freezing when scrolling through
       

  • Simplified & Shorter Queries

    • Write in a few lines in a single SQL query what would take many lines

    • With high performance caching, you can stop worrying about optimization with long, complex queries, IIFs, joins, and subqueries and use new XLookup/XSum/QWhere functions
       

  • Easier to Use, Highly Optimized, and more powerful Dynamic Lookups

    • Use XLookup, XSum, QWhere and the 100+ more dynamic sum, lookup and filtering

      • Create quick, simple, maintainable queries without worrying about optimizing your queries or avoiding VBA function use
         

  • Less VBA, More Business Logic

    • Focus on Business Logic

    • Avoid repetitive "boilerplate" VBA coding

    • DB App Template and Framework provide out-of-box Access application functionality
       

  • Calculated Tables™ with Automated DB Refresh, Sync & Publish

    • Scheduled, data change triggered, on exit, on startup, and on-demand to:

      • Query Result Caching with Calculated Tables

      • Running sequence of Queries and Macros

    • Publish to and Sync with SQL Server, SharePoint and other connected databases & data sources
       

  • Use Queries Anywhere, even those using VBA & .NET functions

    • Even for queries which use VBA and C# or VB.NET functions

    • Don't let something as simple as IsNull(), Nz() Null-to-Zero or Switch() prevent external use of your query

      • Otherwise, such queries won't even show up in data source list in Excel and other external applications due to their use of VBA functions

    • Enabled by automated caching to Calculated Tables and publish & sync with SQL Server and more
       

  • New Events scriptable with VBA & Macro Designer

    • OnExit - It was previously impossible to run macro when quitting/closing Access

    • OnRefreshDB

    • OnStartup

    • OnConfigure
       

  • No install necessary - No "Add-in" install required to:

    • No "Add-in" install required for PowerAccess-accelerated creation of Access databases

    • No dependency install required to open, run and use PowerAccess-enabled databases
       

  • Upcoming PowerExcel™ Framework for Microsoft Excel

    • Our upcoming PowerExcel™ Framework and PowerSQL Framework will bring the same XLookup(), XSum() and QWhere(), and hundreds of other powerful and easy-to-use PowerSQL functions for dynamic lookups, sums, and queries to Excel
       

  • Upcoming PowerSQL™ Framework for SQL Server and VB.NET, C# & Azure Apps

    • Use XLookup() and more functions from within T-SQL views and stored procedures for SQL Servers

    • Use from within any .NET applications, websites, Azure logic apps and web services which uses a SQL-supporting relational database such as SQL Server, MySQL, or Azure SQL.

Solutions Enabled by PowerAccess

Time-saving New Access Tools and Automation, without install or VBA

 

All this is provided without requiring any kind of installation, by embedding these capabilities into your Access database files, and with little-to-no need for VBA or macros.

PowerAccess also extends Access with many powerful tools and time-saving automation to enable quicker and easier creation of Access database applications, forms, BI dashboards, tools, VBA and Macro Designer macros, automated reporting solutions, and data visualizations which are easier to extend, maintain, and scale.

Convert Excel Formulas to Row-based SQL "Field Formulas" with PowerSQL

 

PowerAccess was specifically designed to enable Excel users - without any Access experience - to convert unwieldy Excel workbooks with dozens of tabs to scalable, high performance, and easier to maintain multi-user Access and SQL Server databases. PowerAccess enables - for the first time ever -  conversion of the actual Excel formulas (instead of just a one-time import of data) to "field formulas" in SQL queries or views.

 

These resulting SQL field formulas are similar to the original Excel relative-row/cell formulas - easily defined with respect to the previous "row's" field values - which are at least as easy to define and edit  as they were in Excel.

Use of previous-row-based functions - like XPrevRow(), XPrev() + XRow(), XIncrement, XQTD, XYTD, XNPV() and over a hundred others - not only simplifies defining and editing fields in queries/views, but benchmarks show it they are up to 1000x faster than complex subquery or dynamic lookup alternatives. Moreover, truly iterative/recursive/circular calculations which are otherwise impossible in Access SQL - often preventing scaling up from Excel to Access/SQL - are now not only possible, but as easy to write as a previews-row-based Excel cell formula.

Use Excel as Front-end for Live Viewing/Editing of Centralized Access Database

With PowerAccess, you can then create (and later, even auto-generate) small, easy-to-main Excel workbooks - complete with PivotTables for grouping/drill-down/filtering, instead of dozens of tabs/sheets - which allow end-users to use Excel as a front-end to view, edit, and submit/upload/add to your centralized data stored in a multi-user Access database on a network share or cloud drive (like Dropbox or OneDrive). This is enabled by  live, refreshable data connections to a linked Access database, allowing live or on-demand viewing/syncing/editing, as well offline viewing and editing of data cached into the Excel for later sync-up.

This allows you to avoid the need to create new "sheets / tabs" for each new project, edit your formulas across all cells of all sheets whenever a change or new field is needed, and manually add a row linking to  each new sheet/project into each Excel report sheet. This also enables live syncing of data from multiple users, and without the need to create a tailored workbook for each user to later merge all data and edits from.

PowerAccess enables automatic caching your query data to "Calculated Tables™", not just on-demand, but automatically triggered when data changes (via automatically generated data macros / triggers) and configurable at optionally as a configurable interval in background while using Access, at scheduled times, and/or on startup and exit of Access.

Link to Lighting-Fast Data in Access from Anywhere, even for Queries using VBA and .NET Functions

 

This enables not only instant data access for even the most complex queries and calculations, but also, more importantly, it enables use of these Access queries outside of Access - such as from Excel, Power BI, Tableau, SQL Server, PowerApps, SharePoint, C#/VB.NET apps, and any other database, BI tool or application - even for queries which use VBA and .NET functions within Field Formulas in SQL or Query Designer to still be accessible outside of Access. Don't let a simple IsNull(), Nz(), Switch(), DLookup() or DSum() prevent your query from even showing up for selection to view and link to it from within Excel or anywhere else outside of Access.

 

This also allows you to focus on creating short, easy-to-understand and maintain field formulas - with powerful XSum() and XLookups() and more - without having to worry about efficiency with use repeated expressions in IIF() statements and long, complex subqueries and nested queries.

Web & Mobile Use of Access Databases with PowerApps, SharePoint & Power BI - even without Microsoft's now-discontinued Access Web Apps

Whenever automatic or on-demand Refresh DB caches your query data to Calculated Tables, you will be able to have it automatically push to and sync data with SQL Server, Oracle, Azure SQL, and any other SQL databases, as well as SharePoint Lists. This will enable use of your data from standalone, mobile, and SharePoint-hosted Microsoft PowerApps - which until now have otherwise been unable to be used with Access databases.

Don't let Microsoft's deprecating / removing support for Access Web Apps prevent you from having web- and mobile- accessible data. And, don't allow PowerApps's lack of support for any kind of code (whether VBA, JavaScript, C#, VB.NET, macros, addins, inline SQL, apps/extensions or other programming languages for app/view/form logic) and Power BI's limitations (for supporting the relatively uncommon R programming language) prevent you from scripting logic with your web and mobile data apps and BI dashboards.

 

PowerAccess provides you with your choice or tools and language to write your business logic however you want, whether with VBA, VB.NET, C# or any .NET language or the Access Macro Designer, SQL and PowerSQL field and row formulas - while still providing you with a means for web and mobile use of that data.

Also, PowerAccess-enabled or auto-generated Excel workbooks - complete with PivotTables, filtering, and dynamic drill-down and live, data connections to your Access database - can be hosted on SharePoint, network shares, and cloud storage like OneDrive and Dropbox, as another PowerAccess-enabled means of web and mobile use of your data.

BI & Data Solution Development and Support Available from PowerAccess Developers & BI/Finance Professionals

 

In addition to licensing PowerAccess and other products, we also provide consulting, engineering/IT support, and custom data and BI solution development services, accelerated by use of PowerAccess and the capabilities of the team who developed it, together with our team of BI and Financial Analysis professionals.

PowerSQL Functions

800+ New Powerful Query & Macro Designer, SQL & VBA Functions
 
  • Dynamic Lookups - X Functions
     
    • XPrevRow(), XLookup, XNearest(), PositiveOrZero(), XNearest(), XSum(), XNPV() & hundreds more
    • With many variants of each function, accepting different parameters, skipping optional ones
    • Any number of field, value pairs or QIs*() calls can be passed at end of each function parameters as additional criteria
    • Dynamic lookups are now first class citizens, more reliable and easier to use without need to double quote string values and ability to use QIsBetween() and other dynamic criteria functions as parameters, as well as QParam() to specify query parameters.
       
  • Criteria - Q Functions
     
    • QWhere(), QIsBetween(), QIsNull(), QIsAnyOf(), QWhereAny() & hundreds more
    • QParam() - set query parameters for lookups
    • Reuse same condition across multiple function calls without repeating it.
    • No more constructing criteria strings, quoting values, handling null cases, or writing verbose subqueries
       
  • Macro Designer & VBA Functions
     
    • Use any X() and Q() lookup functions from Macros
    • Avoid boilerplate coding with auto-generation of macros, VBA
    • New macro events - like OnExit and OnRefreshDB
    • .NET Framework-like function library to simplify, modernize and reduce need for VBA code and simplify automation

PowerAccess Roadmap

Excel, Azure, SQL Server & .NET

Use PowerSQL in Excel, SQL Server, and C# / VB.NET Apps with Upcoming PowerExcel, PowerSQL Server, and PowerSQL.NET

 

We will also be releasing the following products which bring PowerAccess' capabilities to Microsoft Excel, SQL Server's T-SQL, and any app written in .NET language like C# or VB.NET.

These will provide those applications with PowerSQL and PowerAccess' other powerful features and time-saving tools and automation.

  • PowerExcel - enabling PowerSQL in Excel cell formulas, enabling use of XLookup, XSumToDate(), and hundreds of other functions for dynamic queries into Access, SQL Server, and other data sources.
     

  • PowerSQL Server™ for Microsoft SQL Server - enabling use of powerful, easy, and optimized previous-row-based Excel-like formulas and BI functions in T-SQL, as well as providing a large library of BI, finance, and dynamic lookup/query functions and automation tools and features.
     

  • PowerSQL.NET™ - enabling use of PowerSQL and dynamic lookup/query functions from C#, VB.NET, and other .NET languages for custom apps, web/cloud services, websites, and Azure logic apps & functions, to use, query, and update

 

Go Beyond VBA - Write Macros in VB.NET & C# for Install-free use with Access, Excel & Office 365

Also, PowerAccess and PowerExcel will also enable use of VB.NET and C# instead of VBA for macros and business logic without requiring any install, admin rights or registration, in the same innovative way that PowerAccess and PowerExcel are able to be used without requiring any install.

Modernize, Simplify, Empower, Accelerate & Extend MS Access
PowerAccess Features & Benefits

  • Convert Excel Formulas to Queries - as SQL for easy visual editing with Query Designer
    • ​Possible for first-time ever due to PowerAccess' unique XPrevRow(Field)-based Queries
    • Previous-Row-based Fields (Calculated Column) Expressions as intuitive - or more - as cell formulas in Excel
       
  • Empower Subject Matter Experts to build own tools & databases - without Access / SQL experience
    • Using PowerAccess' unique support for Excel-like, previous-row-based formulas (with XPrevRow(Field)) together with the many Finance, XLookup(), XSum(), BI, XNPV, Excel and other new functions it provides
       
  • Microsoft Access made Accessible - to anyone who can use Excel
    • No Microsoft Access experience required
    • Empowering Subject Matter Experts to build & evolve their own solutions - not just MS Access pros
    • Stop involving IT, Developers or Consultants every time you need a change, extend, or implement new models
       
  • Modernize Access - with New Time-saving Tools, Generation and Automation
    • Simplify, Save Time and Automate creation and editing of Queries, Forms, Tables, Reports, Data Macro (Triggers)
    • Make previously impossible tasks and uses of Access - like converting Excel Formulas to SQL Queries, Reliable Smart Rename/Delete or Global Find & Replace across Queries, Code, Tables & Delete - now just possible, but easy to do.
    • Completely transform the way you build databases, tools & models with Microsoft Access
    • Replace hard-to-use - and sometimes archaic - tools and activities in Access with new, more user-friendly, time-saving tools and automation
    • Extend and replace stagnating MS Access features which haven't evolved much (and in some cases have been reduced/removed) since 2003.
       
  • Faster, Simpler & Easier to Maintain Financial Models, BI Dashboards and Ad-hoc or Automated Reporting - vs. Excel or Access (without PowerAccess extensions, automation & enhancements)
    • 800+ New Functions for Query/Macro Design, SQL, VBA, Automation & Dynamic - XSum() / XLookup() - Lookups
    • New Functions & Features Specifically for:  Finance, Business Intelligence, Accounting, Analytics, Investment, Date/Time, Advanced Math, Iterative/Recursive Calculation, Tax, Amortization, XNPV, Projection & Forecasting
       
  • Finally Can Use ALL Queries outside of Access - from Excel, Power BI, PowerApps & more​
    • Now even function-using queries can be used with live links & refresh from other apps & databases
    • Stop repeated exporting of new copies to Excel, SharePoint lists, Azure SQL & other databases
    • Stop tedious repeated manual execution of dozens of queries for each design or data change
    • Use Excel for up-to-date / refreshable PivotTables, Charts, Data Entry, Reports with drill-down & filter
    • Auto-Run Query Batches, Cache Results to Calculated XTables™ - Change-Triggered, Scheduled & On-Demand
  • Up to 100x Less Work to Maintain, Extend, Evolve vs. Excel for constantly changing business rules
    • ​Avoid tedious, error-prone manual or copy-paste its across dozens of sheets duplicated across dozens of per-user/project/group workbook copies
    • Saves time & headache with constantly evolving analytical models and changing business rules
    • Stop duplicating everything with a new sheet for each product/project/investment/client or view and editing all report sheets to include them
  • Simplified Excel-like Database Creation for Excel Users
    • Excel-like Previous Row-based Formulas + Functions for Query Design​
    • Excel-Formula-to-SQL-Query Conversion (not just "Data Import") Made Possible for First time Ever
       
  • Replace Complex Formulas Duplicated Across Many Sheets & Workbooks with Easier-to-Maintain Access Databases - Now Supporting Excel Formulas
    • Stop copy-paste merging Workbooks
    • Avoid making same change to formula in a hundred places when business rules change
    • Convert to Centralized, Easier-to-Maintain Queries using New Excel-like Row Formulas for Easier, More Intuitive Editing "Excel Row Formulas" for Query Design
       
  • Empowering Financial/BI Analysts, Accountants, Sales Managers & more
    • Financial Modeling + Business Intelligence Dashboards with new BI & Finance Functions
    • Financial Modeling and Analysis
    • Accounting & Financial Reporting
    • Business Intelligence (BI) Tools & Dashboards
    • Budgeting & Planning
    • Projections & Forecasting
    • Sales & Analytics Models
    • Ad-Hoc & Automated Reporting
       
  • Time-saving New Tools, Templates & Macros for Database Design
    • Much needed update to modernize MS Access, Making More User-friendly
    • Smart Rename/Delete, Search & Replace Across All Queries/Objects
    • Auto-generate VBA, Data Macros, Tables & more
      ​​
  • Cloud Sync for Web, Mobile & BI Use
    • For PowerApps,  Power BI,  SharePoint  &  Excel Online Users
    • Auto Sync to SQL Server, Azure SQL, Hyperion & more whenever results change
       
  • Faster & Easier Query Design with XPrevRow() & 500+ New PowerSQL Functions
    • XPrevRow("Field")-based expressions for Query Design & SQL vs. complex Subquery SQL and Lookups
    • 800+ New XLookup, BI, Finance Functions for Query Design, Macro Design, SQL & VBA
       
  • Business Intelligence Dashboards + Financial Modeling in Access
    • 500 New Finance, Excel, BI, Analytics & Lookup Functions for Queries & Macros​
       
  • Run Queries in Seconds – Not Days – with In-Memory Caching​
    • Subquery & Lookup result caching​
    • Instant XPrevRow() use to replace exponentially slower Iterative/Recursive Calculation

  • Convert Excel Formulas (not just Data) into SQL Queries - Previously Impossible
    • Stop doing all calculation in Excel and just import into Access
    • All visually editable in Access Query Designer
    • What was simple to do in Excel - Excel-like "Previous-Row-based Formulas" for Queries
    • Easy Excel-to-Access (Formula-to-SQL-Query) conversion and rapid, intuitive Query Design
       
  • Up to 20x less SQL & VBA
    • ​500+ new, powerful query & macro Functions for Dynamic Lookups & more – including BI, Finance & Analytics
       
  • New Macro Events 
    • OnExit, OnStartup, OnConfigure, OnRefreshDB, and more– and time-saving Templates
       
  • No-Code / Low-Code
    • Reduce VBA needed with VBA code generation and templates
    • Saves time and reduce cost and complexity for pros
    • Makes creation of advanced, interactive, automated database applications accessible to even users not familiar with or interested in VBA programming
    •  lots of time-consuming, costly to maintain VBA coding for pros
    • Database Templates
    • Code Generation VBA Macro Code Generation
    • VBA Application Framework
      • Avoid boilerplate VBA coding with auto-generated​
    • New Event Triggers for Macro Scripting
      • OnExit, OnStartup, OnConfigure, OnRefreshDB & more​
         
  • Web, Cloud & Mobile BI use
    • ​Live/refreshable connections to Queries from Excel, PowerApps, SharePoint, PowerBI, Tableau & more enabled – even when use VBA Functions
    • By Auto-Caching query results to Access, SQL Server & Azure
       
  • Install-free, no Add-in required
    • ​with PowerAccess embedded, in shared folder or side-by-side
       
  • Modern SQL Editor*
    • Auto-Complete, Formatting, Comments, Error / Syntax Highlighting
    • Replace the extremely limited SQL Editor in Microsoft Access with use of a modern​​ SQL text/code editor with auto-complete, syntax highlighting, preserved multi-line & whitespace formatting, comments & more
       
  • Git Versioning + Collaborative Editing - for SQL and VBA*
     
  • User-Level / Row-Level Security*
    • Many Users... 
      just ONE Shared Report, Dashboard, Report or Data Entry Form/Workbook in Access, Excel or Power BI
    • Per-User-Group Dynamic Filtering & Restrictions
    • Missing, but in-demand, since Access 2003
       
  • PowerSQL for Excel, SQL Server, Azure & C# / VB.NET apps*
    • Planned future expansion of PowerAccess, also providing other PowerAccess features
       
  • Professional Support and Development Services
    • PowerAccess is backed by Solution Development, Consulting & Support services from developers of PowerAccess
    • Support as domain experts in Financial Modeling & Analysis, Business Intelligence, Data Warehousing and Big Data Analytics

* Indicates a planned or Under Development Features. May not all be available in v1.x release.


PowerAccess Roadmap, Code Generation & Database Builder Tools

Current Code Generation Features: VBA, SQL Queries, Data Macros & Tables

PowerAccess provides dynamic SQL code generation under the hood with its hundreds of powerful new Power Query-like PowerSQL functions for use in Access' Query Designer and SQL editor. This is combined with dynamic, on-the-fly lookup caching which can even drastically speed up query calculation compared to even massive, complex, difficult-to-maintain SQL queries which don't make use of them.

 

PowerAccess also provides dynamic table auto-generation from Query results - triggered whenever related data (and therefore query results) change, as well as on-startup, on-exit and/or on-demand.

 

PowerAccess also provides dynamic (at design-time or runtime) Data Macro generation to enable Smart IDs (as a more flexible, compact alternative to AutoNumbers) and to auto-detect record changes to in order to trigger Calculated Table refreshing.

PowerAccess also auto-generates macros like AutoExec and AutoKeys in order to hookup calls to initialize (and run in background, if you need it) the PowerAccess Framework runtime functionality.

The critical code generation PowerAccess performs occurs through "Empower" button which automatically injects use of PowerAccess templates into your databases, together with a reference to the PowerAccess.accde compiled PowerAccess VBA Framework database. This enhances and extends your database with 800+ new "PowerSQL" as well as VBA functions and macros, for use from SQL queries, VBA and Macro Designer. PowerAccess injects code needed for dynamic relinking to PowerAccess Framework referenced database file as well as optionally other user-referenced databases/libraries.
 

 

Simplified Deployment & Updates, Optimization Performance and Code Reuse with Shared Libraries

By having your Database reference the PowerAccess Framework (PowerAccess.accde file or .accdb file for those with Source licenses) this allows addition of all this functionality with dozens of new runtime features and hundreds of new SQL and VBA functions, all without increasing the size of your database, by avoiding duplication of code or inlining of all PowerAccess VBA code, macros and functions. This simplifies deployment, further development and maintenance by avoiding inlining all VBA Functions and Code and improves performance and reduces database size by avoiding overhead for unused functions. This allows sharing code and functionality between multiple databases or copies of them. This also enables easy, in-place updates of the PowerAccess Framework as new features, optimizations and enhancements are available.

Automatic Relinking, Relative Paths & Known Folders with PowerAccess Framework, Custom VBA Function Library References & Split Databases

And with PowerAccess tools and the minimal VBA code that is injected into your databases through codegen tools is not only simple, concise and easy to understand, but also provides you with automatic relinking which you can use to similarly use your own referenced or linked Access databases, for your own custom VBA function libraries and code reuse and for Split Front-end vs. Back-end Databases, all while benefiting from the same automatic relinking and auto detection of shared referenced databases in same folders (even if absolute path changes), based on relative paths, or searching known folders recursively based on file name.

 

Current Features for Automatic Code Generation and Database Building

 

The PowerAccess VBA Framework provides dozens of new runtime features (Calculated Tables, Cloud Sync, Iterative Calculation Engine, Dynamic lookup caching, Excel formula-based queries, logging, diagnostics, telemetry, keyboard shortcut handling, etc.). PowerAccess VBA Framework also provides 800+ VBA functions for use from VBA modules, Form & Report Designers & event handles, Macro Designer and even SQL and Query Designer in many cases.

 

These function cover everything from Arrays, Collections, File IO, Logging, Diagnostics, VBA Code (SLOC) line count metrics, VBA import/export/auto-injection,SQL/VBA/Data search & replace (at runtime), File Download/Upload, Web Scrapping, Regular Expression (RegEx / RegExp) pattern matching, .NET API abstractions (exposing parts of the Microsoft .NET Framework and as APIs for simplified use from VBA and Macros, Browse/Save/Import/Export dialogs & automation, and Message Box Prompts. These functions also provide calculation or computation for Finance, Reporting, Simplified Math & Convenience functions (MaxOf, IsAnyOf, IsNullOrEmpty, SumOf, OrDefaultIfNull(), PositiveOrZero(), etc), Analytics, Treasury Rates, Compounded Interest Rate conversion, Net Present Value (NPV) overloads / lookups / simplified row-by-row calculation, and more.

 

These functions – together with new VBA and Macro Designer-scriptable macro events like OnAppExit, OnRefreshDB, OnStartup, as well as Code Generation tools – drastically reduce boilerplate


We are also developing new tools for automatic generation of tables, relationships, SQL queries, macros, VBA (eg. for Form/Report event handles) in more cases at design-time for "Automated Database Builder" tools, as well as to enhance your existing Tables and Queries with new modernized runtime functionality (without tedious, boilerplate, complex design).  This will allow you to enhance your databases with new modern features previously unavailable or difficult to implement in Access, or integrate features which are available in PowerAccess templates into your own tables and databases.

 

User View & Edit Restrictions, Change Tracking & Rollback, Audit Logs, User Permissions, Dynamic Record Versioning and User-level / Row-level Security

 

Functions and features as those described above would modernize your Microsoft Access databases with User-level Security, Row-level Security, User View/Edit Permissions, Record Timestamps, Record Version Tracking, Data Snapshots and/or Dataset partitions. User Edit Tracking/Comments/Undo would  allow you to dynamically view past versions of data records, so that you can, for example, allow users to dynamically switch between viewing different versions / revisions / drafts (or quotes vs. final vs. revised/resubmitted versions) of the same Invoice.

 

This would also allow you to have Payment/Billing Activity refer to a specific Invoice/Quote version and have Invoices refer to specific dated or named revisions of products or use different Price Lists for them, or view/compare/rollback to different edited versions of Contacts in a Customer Relationship Management (CRM) database solution, as well as rollback edits or undelete a Contact, Product, Inventory Item, Line Item, Invoice, Project/Task Assignment, Vendor or any other kind of entity or item you may have in your database solution.

PowerEstimates™ Templates & Features for PowerAccess

 

Those User Edit Tracking/Undo features are being developed for the PowerEstimates™ templates for PowerAccess. The ability to build on and integrate our powerful, flexible PowerEstimates templates is one of the many unique advantages provided to our consulting and development services projects. We may also release those templates separately or include in all or higher-end PowerAccess editions, and are likely to provide much of that functionality into PowerAccess code & table generation tools to enhance your databases or build new databases with that kind of advanced or often critical (yet often too costly or complex to implement) functionality automatically or out-of-box.

User View / Edit Permissions and User-level / Row-level Security

 

These are also features being designed and developed for provide for your own databases through additions to the PowerAccess Code Generation and Automated Database Builder tools which are part of PowerAccess, along with the included PowerAccess VBA Framework which provides support for such features at runtime, along with the functions (such as XCanEdit() or XCanView() or XSetUserPermission()) to use them at runtime – whether through our CodeGen tools or your own use from VBA, Macro Designer or in events in Form/Report designer. These functions – together with auto-generated supporting tables and Framework runtime features – can be used to provide User-level Security and Row-level Security (which are sorely missing from Microsoft Access, ever since removed when switched from MDB to ACCDB format in Access 2007).

 

Beyond that, these PowerAccess functions and features can also be used to provide Row-level Security and Separate Dataset Partitions (such as for different offices, departments, groups, clients, regions, or individuals) even within the same shared database, where admins and managers can control and limit what records, rows or record groupings (representing invoices, projects, programs, tasks, contacts, or any other kind of item, entity or table you which to use them for) as well as which objects (reports, macros, fields/columns, queries, forms, subforms, tabs, windows, etc.) different users (such as employees, vendors or customers) or groups of users can view, edit, run or use.

 

Code Generation Tools for VBA, Macro Event Handlers and Beyond

We are also developing code generation tools to automatically hookup event handlers for forms and reports, such as to enable click on header to sort rows by it a field/column or common features like record filtering, even when using more user-friendly Continuous Forms (with customizable, user-friendly, touch-optimized, simplified rows with customizable controls/editing/display for each field) instead of Datasheet view or when using simplified, user-friendly (right click) context menus instead of showing complex built-in Advanced Developer Sort/Filter context menus.

 

Continual Delivery of New Tools & Features with PowerAccess Subscriptions and Perpetual, Royalty-free Use


These are all examples of the many powerful new code generation & database building tools, as well as PowerAccess Framework runtime features, and PowerSQL and VBA Framework functions which we are continually developing and releasing as part of PowerAccess subscriptions, as made possibly by our subscription model. However, even if you decide not to renew our PowerAccess Subscription, you can still continue to use VBA Framework and code from PowerAccess templates, samples or code generation tools in any databases you had already built with PowerAccess, royalty-free for uninterrupted perpetual use.

 

Start using core PowerAccess tools now and begin receiving many more planned and under development powerful new modernizing tools and features for Microsoft Access, such as those described here, through your PowerAccess annual update subscription, where we will roll out new tools and features to you as they are developed, with our Consulting & Development Services clients receiving earliest and most frequent updates, followed by Source Edition customers and users for all our other PowerAccess editions.

Early Access Program, Release Notification and


To get early access to PowerAccess, entering your email in the Subscribe form to get notified upon general release of PowerAccess, PowerGit, PowerSQL and other innovative tools and frameworks. You can then also apply for our Early Access Program (EAP) for Access pros.

Earliest Access and Unparalleled User Experience Consulting, Development & Customization Services

For even earlier access, you can contact us at Solutions@PowerAccess.net for a free consultation regarding our Consulting & Solution Development Services, from the leading Access experts worldwide, the developers of PowerAccess. Our solution development clients not only benefit from our use of PowerAccess tools and framework for the most rapid, cost-effective development, but also from the dozens of unique runtime features our tools, templates and framework provide.

 

These unique PowerAccess tool and framework advantages, coupled with our unparalleled expertise in not only Microsoft Access but in flexible, user-friendly, user experience (UX)-focused design (as seen in our uniquely simplified and flexible design of our PowerAccess tools and framework) allows us to deliver the most flexible, automated, cloud/service/system-integrated, and easy-to-use and easy-to-maintain and easy-to-customize (even for those without Access experience) multi-user Microsoft Access database solutions.

Learn More about PowerAccess | PowerSQL