Custom Databases in Microsoft Access - Tips and FAQs

Find a MS Access Form in a Database by FSO and Wildcard

(Reading time: 1 - 2 minutes)

Our Problem and Solution:

We currently have thousands of Microsoft Access Database that we've written since the 1990s. In each of the Access databases, we have a lot of various routines or Access Forms that we created to do all sorts of automated functions. From building a unique table to form creator with the automation of subforms that are linked with Master/Child fields to Query By Form creation engines, we have a lot of Forms and a lot of code we've written.

Sometimes our memory fails us as to what we wrote when and where that could be functional for re-use. We remember some project that seems familiar to what we need but can't locate the info easily.

This single form MS Access Database searches on your hard drive for any *mdb* in a specified path and searches Forms inside the Access database for a wildcard name such as "*qbf*" - It's written in simple and easy to use VBA that can be modified and reused. It is not error-proof and probably could do with some enhancements. However, we hope it can be a useful tool for you.

The Access DB will also store the database names and check them off as they process eliminating reruns and storing all the form names in the t_Files database table.

Things it will error on -

* Opening a form in design that has an active x control that isn't installed

* Access Databases that have a password on them.

* Error messages from opening the forms are turned off with DoCmd.Setwarnings False

 

The file that it is examining will be shown in the immediate window as will an Access Form name that has been found with the wildcard search.

Download the file by clicking on this link.

 

 

Please feel free to modify this as much as you like. Would be great if you gave Phoenix Consultants Group credit in your comments with the link above so others can find the code if needed.

The Top 5 Tips From Microsoft Access Help

(Reading time: 2 - 3 minutes)

Your 5 Keys to Easy Microsoft Access Database Editing

Microsoft Access has advanced massively over the last 20 years. It is now a rich application that provides excellent out-of-the-box functionality. It is now compatible with other applications and can be used to create a productive and functional solution that can meet all your business needs. Often, people that are looking to work with Access don’t fully understand the functionality that Access has to offer. So to help counter that, here are my top 5 tips to help you with Microsoft Access.

Database Entry

Entering information into tables and forms is, of course, very simple… and Microsoft Access makes it intuitive. On top of this Microsoft Access includes keyboard shortcuts to enhance the usability through better navigation and editing processes. CTRL + adds a new record. CTRL ‘ takes the value from a previous record and replicates it in the corresponding field in the current record. Using the F2 key provides the opportunity to edit a record. There are so many ways to improve the usability of Microsoft Access. Make sure you check out the help file in the program by looking for “keyboard shortcuts”.

The Object Properties Dialog Box

Items in the navigation pane and database window have properties that are hidden in which you can add narratives and also remove the object from the screen. There is a keyboard shortcut available in the form of ALT + Enter or you can “right-click” -> Properties. Once you have hidden an object, it will be gone from view until you select the option “Show Hidden Objects”. Utilizing this function is primarily adopted for sensitive items such as Action Queries and can be very useful.

Application Starter

It is possible to open a form on your welcoming screen instead of the navigation pane or database window. This can help make it a richer front-end. You can also stop the startup by pressing and holding “shift” during the database file launch.

Reporting In Access - Combine with Excel?

Reports are numerous in Microsoft Access database. It includes pivot tables. However, most people will be more comfortable with Excel’s pivot tables. It is possible to take data from Excel and link it to the Access database. The two applications, both being Microsoft, are great for integration and Excel also provides the added benefit of having a file that can be sent by email.

Updating Bulk Data

If you have a big update to do and it involves a pattern, then there is a function called “Update Query”, which enables you to dictate the criteria and also include calculations. A typical example of this is with an online store: A price increase of 5% across the board could be done through a formula update query. Updating each price manually could be a time consuming nightmare, but running an update query is fast and simple. There are so many different options available in Microsoft Access. It’s a far more powerful tool than most of us give it credit for. Get your head into the system and understand how it can help improve your business and you really will benefit from an efficient, productive, and profit-increasing piece of software.

© 2013 Phoenix Consultants Group, Inc.

A Microsoft Access Primer - How to Start Working in Access

(Reading time: 2 - 3 minutes)

So what exactly is Microsoft Access and how will it help my business?  This article provides an overview of the components that exist inside of MS Access that Phoenix Consultants Group can program to your individual specifications:

There are several pieces that exist within a MS Access system.  You have tables, forms, queries, reports, and modules.  Each of the pieces is called an "object" by Microsoft.

 

What is a Microsoft Access Table:

A table in MS Access is quite different than a table in real life.  Instead of having wooden legs and being used for meals.  Access Tables are a grid made up of rows and columns.  Here's and example of a table in Access:

 

What is a Microsoft Access Form:

 A form is an object that you can use to create a user interface for a database application. A "bound" form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source. Alternatively, you can create an "unbound" form that does not link directly to a table, but which still contains command buttons, labels, or other controls that you need to operate your application.

This article focuses primarily on bound forms. You can use bound forms to control access to data, such as which fields or rows of data are displayed. For example, certain users might need to see only several fields in a table with many fields. Providing those users with a form that contains only those fields makes it easier for them to use the database. You can also add command buttons and other features to a form to automate frequently performed actions.

Think of bound forms as windows through which people see and reach your database. An effective form speeds the use of your database, because people don't have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient, and it can also help prevent incorrect data from being entered.

 

What is a Microsoft Access Query:

As tables grow in size they can have hundreds of thousands of records, which makes it impossible for the user to pick out specific records from that table. Queries were designed to combat this problem. With a query you can apply a filter to the table's data, so that you only get the information that you want. 

The tricky part of queries is that you must understand how to construct one before you can actually use them. This lesson will guide you through the basics of making a couple very simple Access queries.

 

 What is a Microsoft Access Report:

Having all your data stored in Access is great for maintaining a database, but it isn't the best when you want to share the data or view it away from a computer. The solution to this problem is to create an Access report that will let you design a ready-to-print document of your desired database information.

 

Microsoft Access FAQ - Why Access?

(Reading time: 1 - 2 minutes)

 

What is Access?

Access is a database tool for gathering and understanding all your information—your phone numbers, inventory, guest lists, whatever you’re tracking—and providing a convenient way to enter, navigate, and report out your data.  As a database system, Access provides the ultimate desktop user platform that is able to collect your much needed data and get the results you need to build and make your business.

Microsoft Access is one of the leading database systems on user desktops today.  At PCG we have written hundreds of Access databases for leading corporations, small business and non-profits.  Getting an Access database to handle complex data in a user friendly manner is what we excel at.   We can produce a database quickly, accurate and deploy that database to your company on time and on budget.

Other companies write a few databases and call themselves experts.  At Phoenix Consultants Group we have a long record over 25 years of productive software that companies rely on to be able get their critical information managed and their customers supported.  Don't chose a second rate company to do what a reliable and solid database development company like Phoenix Consultants Group can do for you.    

  

When do I need Access?

  • When you want to keep track of not only who’s on your guest list, but where they’re staying, and any dietary needs.
  • When you need to be able to collect contact information accurately from large groups.
  • When you want a database that is easy for colleagues to explore your information without training.
  • When you need to run the same report daily, weekly, or monthly.
  • When you want to know who your most valuable customers are so you can target your marketing.

We have written literally hundreds of databases in Access to do what customers need most.  From inventory control systems to engineering solutions, Phoenix has been able to provide solid solutions to our clients most critical business needs.    There is no job to small as we realize that if it's critical for you to complete your job, it's critical for us to design, develop and deliver your software needs.

 

RSS Feeds

About us

9 Wilson Dr, Northfield, NJ 08225
Tel: 609.423.6979
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.