Microsoft Access Programmer Greensboro, North Carolina

Using Microsoft Access Queries and Forms

Call MS Access Solutions at (323) 285-0939 for your FREE consultation.

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

Embedding SQL Statements Versus Stored Queries

In earlier versions of Access, stored queries offered better performance than embedded SQL statements. The reason is that, when a query is saved, Access compiles the query and creates a query plan, which has information on the best way to execute the query based on available indexes and the volume of data. In earlier versions of Access, if a form was based on an embedded SQL statement, the SQL statement was compiled and optimized each time the form was opened. With Access 2000 and higher, embedded SQL statements are compiled just like stored queries.

You might ask whether, with Access 2007, it is better to base a query on a stored query or on a SQL statement. My personal preference is as follows: If I plan to use the same or a similar query with multiple forms and reports, I build a query and base multiple forms and reports on that query. This keeps me from having to duplicate my efforts in building the query. If I have a query that is unique to the form, I build it as an embedded SQL statement. This eliminates the extra "clutter" of the query in the database container.

Check For Inaccurate Queries

A query plan can sometimes be inaccurate because it optimizes the query based on the amount of data in the underlying tables. If the amount of data in the tables underlying a form changes significantly, you need to rebuild the query plan. You can do this by opening, running, and saving the query or by compacting the database. Chapter 30, "Maintaining Your Application," covers the process of compacting your databases.

Connecting Microsoft Access Forms And The Internet

Microsoft has made it easier to develop Internet-aware applications by adding hyperlinks to forms and allowing you to save an Access form as HTML or XML. These features are covered in the following sections.

Adding A Hyperlink To A Form

Hyperlinks can be added to unattached labels (labels not attached to a text box or other object), command buttons, and image controls. Once added, they let the user jump to a document (UNC) or web page (URL) simply by clicking the control containing the hyperlink. To add a hyperlink to a label, command button, or image control, follow these steps:

• Click to select the control.


• View the control's properties.


• Select the Format tab of the property sheet.


• Click in the Hyperlink Address property.


• Click the build button (the ellipsis) to open the Insert Hyperlink dialog box.


• With Existing File or Web Page selected as the Link To option, you can enter a file path or URL in the text box or click Current Folder to locate a file or web page in the current folder. You can also click to insert hyperlinks to browsed pages or recent files. With Object in This Database selected as the Link To option, you can link to an object in the current database. Select E-Mail Address to link to an email address.


• Click OK to finish the process. The contents of the Link to File or URL combo box become the Hyperlink Address, and the object name becomes the Hyperlink SubAddress.

---

Author Attribution

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

---

Expert Microsoft Access Programmer In Greensboro, North Carolina

As a Microsoft Access expert with 25+ years of experience, we develop custom database solutions for diverse sectors including healthcare, government, military, education, agriculture, Human resources, and insurance. Our expertise spans:

• Enterprise-level database development integrating Microsoft Access and SQL Server.


• Technical maintenance.


• Form and report fixes.


• Query optimization.


• Macro development and debugging.


• Visual Basic For Applications (VBA) code troubleshooting.

We deliver robust, reliable Microsoft Access database applications customized to your business requirements. Contact the expert Microsoft Access programmer company, MS Access Solutions, at (323) 285-0939 for professional database development and support.

---

Find out more about our Microsoft Access programmer services at the Microsoft Access pProgrammer Raleigh, North Carolina web page.

Frequently Asked Questions

Question: Can you fix a corrupted Microsoft Access database?

Answer:Yes. We repair corrupted Access databases for businesses in Greensboro and nearby. We start with a safe copy, then run Compact and Repair, decompile and recompile, and migrate objects into a clean ACCDB. We correct broken references, missing ActiveX controls, and 32 bit or 64 bit API declares. Tables are relinked, queries tested, and damaged forms or reports rebuilt. If data is affected, we export with DAO, compare row counts, recover from backups when available, and validate keys and relationships. To prevent a repeat, we split the app into a front end and a back end, add Trusted Locations, review record locking, and set a simple backup plan. For multi user sites, we install a local front end on each workstation and keep a persistent connection to the back end to reduce link drops. You receive a short report listing the cause, the repairs made, and steps to keep it stable.

Question: What are common causes of Access corruption and slow performance?

Answer:Frequent causes include a single unsplit database on a shared drive, network drops or power loss during writes, queries that scan entire tables, missing indexes, subforms that load too many records, Name AutoCorrect left on, large attachments stored in tables, bloat from frequent design changes, mixed Office bitness, and broken references. Fixes are direct and practical. Split the database, keep data in a shared back end and a local front end on each PC, add indexes on join keys and filter fields, rewrite leading wildcard searches, set WHERE conditions on subforms, turn off Name AutoCorrect, move large files to secure storage and keep paths in tables, remove unused objects, and align Office versions and references. For heavy workloads, pass through queries can push processing to the server, and a SQL Server back end is a sound option when you outgrow Access storage. These steps raise stability and speed without changing the user interface.

Question: Do you convert Excel workbooks to an Access database?

Answer:Yes. We convert workbooks into a shared database that fits daily work. We map sheets and columns to tables and fields, clean duplicates, set correct data types, and define keys and relationships so related records link correctly. We build forms for data entry with lookups, required fields, and clear error messages. Imports use saved specifications so new files load the same way every time. Reports and saved queries replace manual filters and ad hoc totals. The design is split, a back end for data and a front end for screens and logic. Security relies on Windows file permissions, Trusted Locations, and a simple backup routine. You keep exports to Excel and PDF for clients.Users will see fewer errors, faster reporting, and better audit trails because rules live in the database rather than scattered formulas across many files.

Question: Will Excel-to-Access migration keep my existing formulas and logic?

Answer:

Answer:Yes. We map Excel logic to Access features so results match. In Excel, you might use VLOOKUP or XLOOKUP to pull matching data from another sheet. In Access, that same job is done by creating a link between two tables based on a shared field, which is called a join between tables. SUMIF and COUNTIF in Excel become aggregate queries using GROUP BY with filter conditions. IF statements become calculated fields, IIf in queries, or VBA in form and report events. Pivot tables become crosstab queries and standard reports. Excel data checks move to field validation rules, input masks, and table constraints. Date math uses DateAdd and DateDiff with clear rules for time zones. Rounding follows Round and fixed formats so totals align. We prefer calculations in queries rather than stored fields, unless a rule requires storage for audit. During migration we test with sample spreadsheets, compare totals, and adjust types and precision until numbers line up. We also review business processes that depend on these formulas and document how they are represented in Access. This gives your team a clear reference so they can see how calculations work, trace back results, and keep consistent reporting after the migration.

Have a question not listed here? Call (323) 285-0939 or request a consult.