Microsoft Access Programmer Tulsa, Oklahoma

Microsoft Access Programmer Information

Call MS Access Solutions at (323)285-0939 For Complimentary Consultation

The material below originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission. There may be references to "Figures" or "Chapters"that are not reprintable and are not used on this page.

Programming Subreports

When a subreport has been added to a report, it's important to understand what properties have been set by the SubReport Wizard so that you can modify the SubReport control, if needed. You should become familiar with a few properties of a subreport:

• Source Object: Enables you to indicate the name of the report or other object that's being displayed within the control.


• Link Child Fields: Enables you to indicate the fields from the child report that link the child report to the master report.


• Link Master Fields: Enables you to indicate the fields from the master report that link the master report to the child report.


• Can Grow: Determines whether the control can expand vertically to accommodate data in the subreport.


• Can Shrink: Determines whether the control can shrink to eliminate blank lines when no data is found in the subreport.

You should not only know how to work with the properties of a SubReport object but also be able to easily modify the subreport from within the main report. You can always modify the subreport by selecting it within the list of reports in the Navigation Pane. To do this, click the report you want to modify; then click Design. You can also modify a subreport by selecting its objects directly within the parent report.

Programmer Tip

Access 2007 makes it easy to work with subforms and subreports in Design view. Scrolling has been improved so that it's easier to design subforms and subreports. In addition, you can open a subreport in its own separate Design view window by rightclicking the subreport and selecting Subreport in New Window. Alternatively, instead of right-clicking the subreport, you can select the subreport and then click Subreport in New Window on the Design tab of the ribbon.

Working with Sorting and Grouping

As opposed to sorting within forms, sorting the data within a report isn't determined by the underlying query. In fact, the underlying query affects the report's sort order only when no sort order has been specified for the report. Any sort order specified in the query is completely overwritten by the report's sort order, which is determined by the report's Sorting and Grouping window (see Figure 6.26). The sorting and grouping of the report is affected by what options you select when you run a Report Wizard. You can use the Sorting and Grouping window to add, remove, or modify sorting and grouping options for the report. Sorting simply affects the order of the records on the report. Grouping adds Group Headers and Footers to the report.

---

Author Attribution

This material originally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development and is reprinted here with the author's permission.

---

Get The Best Microsoft Access Programmer In Tulsa, OK

When you need a Microsoft Access programmer for your Tulsa, Oklahoma business, call MS Access Solutions at (323) 285-0939. We have more than 25 years of hands-on work building Microsoft Access database applications for hospitals, medical clinics, government agencies, branches of the U.S. military, universities, community colleges, school districts, agricultural operations, human resources departments, insurance providers, and other sectors. Projects range from a single-purpose tool used by one team to multi-user systems that connect to SQL Server and support hundreds of thousands of rows.

Our programmers write clear, dependable code, they ship databases that people can use without a long learning curve. The team is led by our owner, Alison Balter, widely regarded as one of the leading Microsoft Access experts in the world. She has written multiple books on Access development, she speaks to developer and business audiences, and she sets the technical bar for how we design tables, write queries, and release updates.

We start with a clean data model. Tables are normalized to the level the business needs, field names follow a readable pattern, and keys are chosen to match how data is used. Serious applications are split into a front end and a back end; the front end holds forms, reports, queries, and code, the back end holds data. We usually ship the front end as an ACCDE, so code is compiled and forms open quickly. Each release is versioned and documented so staff know what changed.

Access front ends connect to SQL Server through ODBC when data volume or concurrency calls for it. Linked tables are used for typical screens, pass-through queries or stored procedures handle heavy reporting and updates, and parameters are bound rather than concatenated. Large joins and aggregations run on the server, not across the network. For steady performance, we keep connection strings DSN-less, we avoid chatty row-by-row updates, and we batch writes when possible.

Speed matters, so we measure it. We review primary keys and indexes, add the ones that help filters and joins, and remove overlaps that slow writes. Queries are trimmed to the fields the form needs, criteria push down to the source, and we avoid domain functions inside big queries. On forms, we open with a WHERE clause or a limited recordsource; on reports, we move long-running totals to server-side queries and cache small lookup lists in memory.

Access files gain size over time, which can lead to slowdowns or fragility. We keep file size under control by turning off Name AutoCorrect in production, removing orphaned and hidden objects from old versions, and running Compact and Repair on a schedule that matches usage. We watch the 2 GB limit for ACCDB files; when growth is likely, data goes to SQL Server and Access stays as the front end. Front ends remain small and local on each workstation; back ends stay on a server.

Multi-user stability is built in. Forms bind to narrow recordsets, record-level locking is set correctly, and writes are short to reduce conflicts. We add an error log table with timestamps, machine names, and user names, so support decisions are based on facts. For remote staff, we use VPN or Remote Desktop; if internet access is required, we use Azure SQL Database or a hosted SQL Server and keep the Access file local. Direct file sharing over the open internet is avoided.

Repair and rescue work is common, and we handle it methodically. We fix broken forms and reports that were copied between versions, we replace unstable macros with focused VBA, and we add Option Explicit across modules. We correct missing references after Office updates, convert late-bound code to early-bound DAO where it helps readability and speed, and remove dead modules and unused timers. For 64-bit Office, we add PtrSafe declarations and test libraries for compatibility.

Upgrades and migrations are planned, not rushed. When moving data to SQL Server, we use SQL Server Migration Assistant for Access, then review data types, keys, defaults, and identity behavior. We replace action queries that used to touch local tables with stored procedures or pass-through queries. We check security, test concurrency with real user counts, and train staff before go-live. After launch, we monitor slow queries and adjust indexes or query plans as needed.

• Custom Microsoft Access applications for scheduling, inventory, HR, billing, and reporting
• Access front ends connected to SQL Server through ODBC with linked tables and pass-through queries
• Query tuning for speed, correct indexing, and server-side filtering
• File size control, Compact and Repair plans, and cleanup of Name AutoCorrect artifacts
• Form and report fixes, reliable VBA, and replacement of unstable macros
• Front end and back end splits, ACCDE deployment, and versioned release builds
• Migration planning with SSMA, data type reviews, and stored procedure based updates

References

Improve performance of Access databases, Microsoft Learn

Access specifications and limits, Microsoft Support

Create a pass-through query, Microsoft Support

SQL Server Migration Assistant for Access, Microsoft Learn

Record locking to avoid editing conflicts, Microsoft Support

VBA reference for Access, Microsoft Learn

Alison Balter, Mastering Microsoft Office Access 2007 Development, Pearson

---

You will find more information about our programming services on the Microsfot Access Programmer Oklahoma City, Oklahoma web page.

Access Programmer Tulsa OK

Map of just some of the businesses we serve in Tulsa Oklahoma