Tips of the Day
Techniques and best practices for Microsoft Access and relational database development.
One of the first questions every database developer should ask is, “Who will use the database?”, or more precisely, “How many people will use it?” Or more precisely still, “How many people will use the database at the same time?” Theoretically, MS Access 2000/2002 will accommodate up to 255 concurrent users—that’s 255 people adding, changing, deleting or looking up records all at the same time. Yeah, right, 255 concurrent users. The practical limit to the number of concurrent users (people at individual workstations simultaneously adding, changing, deleting or looking up records in your database) is quite a bit smaller. Developers will argue over what that limit really is because it is impacted by a number of factors including the quality of the network where the database is located, the amount of memory available on the users’ workstations, and the design and installation of your database.
What I'm talking about here is unusually quick, or disproportionately large, growth of the accdb file. This is often referred to as bloat.
The primary reason for bloat is creating and deleting forms or other objects, or using a lot of append, update or delete queries. This is common during development as you create, modify and delete forms, queries and reports while designing and building the database application. It can also reflect import processes, in which new data is brought into temporary tables to be staged before final append to the destination tables. When your processes add and delete data or temporary objects in an Access database, Access doesn't actually physically remove them.
I've seen cases where bloat had gotten so great that a client's database was on the verge of failing because it was getting close to the 2 GB maximum file size.
Access simply adds a flag to deleted records or objects indicating that they are to be deleted "at some point". They still take up space in the accdb even though they are no longer used. When this happens, the accdb on disk bloats.
Compacting the Access file makes a copy of the file and physically copies all of the current, or "non-deleted" records and objects into it, leaving behind all of those objects and records flagged for delete.
Then, the old file is deleted from disk and the new, smaller, accdb file is renamed to the same name as the old one. It appears to the file system to be the "same" accdb, so it continues to function as before. It can also rearrange how the file is stored on your disk, which might potentially improve performance, although modern Operating Systems probably won't show much difference in this regard. The main performance improvement is due to the absence of all of those deleted records.
If you have a database to which you have exclusive access, you can set an option to compact it each time you close it.
Step One: A. Select File-->Options from the ribbon.
B. Select the "Current Database".
C. Check "Compact on Close"
If your database is shared by two or more users, you SHOULD NOT use this option.
If another user has the database open, trying to compact the database can cause a number of problems. Therefore, only use this option on databases
where one user will have exclusive rights to the database.
You can also manually do a Compact and Repair by clicking on the "Compact and Repair Database" icon under the "Database Tools" tab of the ribbon.
By now you’ve figured out I’m not really talking about a cardboard box and a four-drawer filing cabinet (although they are real enough features of my office). They are metaphors for some of the things we need to understand in order to build a robust, efficient, and above all, useful database. In its most fundamental sense, my box is a database. That is to say, it is a collection of data about a particular subject or purpose. In its current state of (non)organization, my “box database” isn’t very useful except, of course, as short term storage. My four-drawer filing cabinet comes closer to being a real database. It, too, is a collection. However, it is a collection of information on a particular subject or purpose, organized according to a pre-defined structure, or model. I say it holds information rather than data, because the documents in it are organized, sorted and stored according to a model I came up with before I started putting documents in its drawers.
When you create a new database, Access puts its own name in the title bar. That's fine while you're still developing the database;
however, if someone else will use the database, it's more professional looking to display your application's name, "e.g. "Weekly Production", or "Phone List"
Here's you do it.
Select "File-->Options" from the Ribbon.
Type the name of your application into the "Application Title" property.
You can also specify an icon for the application. This icon then appears in the upper left, replacing the default Access icon.
If you want it to appear on forms and reports, select that property as well.
“acNewRec” is an intrinsic constant. Visual Basic for Applications defines constants to simplify your programming. Constants can be used anywhere in your code in place of the actual values. For example, the actual value VBA uses to indicate “new record” is “ 5 ”. Since you and I are not very likely to remember that “5” means “New Record”, the Access folks created the constant “acNewRec” to represent it. There are many other VBA constants. Here’s what Microsoft says about constants. “A constant represents a numeric or string value that doesn't change. You can use constants to improve the readability of your Visual Basic code and to make your code easier to maintain. In addition, the use of intrinsic constants ensures that code will continue to work even if the underlying values that the constants represent are changed in later releases of Microsoft Access."
Search tables of customers, patients or other clients, for example, or tables of transactions. Such tables have a Primary Key field (which your users NEVER see) and one or more Natural Key fields, by which you and your users identify individual records. In a table of customers, for example, the Natural Key might be either a Customer ID (e.g. HEP1001), an Employee ID (e.g., 967389), or a Name (e.g. James). In a table of transactions, for example, the Natural Key might be an Invoice Number (e.g. 2003A1001), or Batch Number (e.g. 031228001).
Here's how you go about creating a Searching Combo Box.
Step One: A. Create or select a form bound to the table you want to search.
B. With the form open in design view, select the combo box tool from the Ribbon and draw a combo box on your form.
I usually do this in the form's header section.
C. When the combo box wizard opens, select the third option, "Find a record on my form based on the value I selected in my combo Box."
D. Click "Next" to move to the next page of the wizard.
E. Use the chevrons ">" to add the table's Primary and Natural key fields to the query in the right side of the wizard. Only include fields you
plan to use for the search, but make sure the FIRST column is the table's primary key field.
F. Finish the combo box.
Step Two: Open the form in form view and select entries from the searching combo box. Your form will move focus to the matching record.
If you want to look at the code generated by the combo box wizard, open the form in design view and click on the Builder Button
for the "After Update" event of the combo box. It will open the code module to show something like this:
Private Sub cboSelectanEmployee_AfterUpdate()
'An integer would probably work as well for most Access applications,
' which aren't that large,
'but I usually dim every variable as a long integer for ease, and because I'm lazy
Dim lngEmployeeID As Long
Dim rst As DAO.Recordset
'==================================================================
' Grover Park Consulting cboSelectanEmployee_AfterUpdate
' George R. Hepworth 10/28/2016 09:37
' This is my replacement code for the typical embedded macro Access
' creates by default. I have no objection to macros. I just find them
' too limiting. VBA is much more flexible and robust.
'
' This code does the following:
' If an employee is selected in the searching combo box,
' lngEmployeeID is set to the Primary Key for that employee's record.
' If no employee has been selected, lngEmployeeID is set to 0.
' Using the form's RecordsetClone--not its own Recordset --
' we find the first matching employeeID. Of course, being the primary key,
' there can be only one match.
' If there is no match, which will happen if lngEmployeeID is 0,
' then we set the form to add a new record into the employee table.
'
'==================================================================
lngEmployeeID = Nz(Value:=Me.cboSelectanEmployee, ValueIfNull:=0)
Set rst = Me.RecordsetClone
With rst
.FindFirst Criteria:="pkEmployeeID = " & lngEmployeeID
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
rst.AddNew
End If
End With
End Sub
Notice that the wizard created a generic name for the combo box (Combo28 in this example, which I changed for this demo).
When you change the combo box name to a standard name,
such as cboTransactionSearch, or cboSelectanEmployee you'll also need to update the reference to the combo box in both places in the code.
Officially, this is a book on using Access. Much of the book is devoted to defining, building and using the objects in an Access database. However, I feel quite strongly that it would be entirely unprofessional for me not to make a concerted effort to help you understand some fundamental concepts about relational databases to prepare you for that effort. The first, and most important, is that the tables in your database must be normalized. You may not know what that means yet, but you will after you finish reading this book! I can’t make this point strongly enough. To be useful, your database has to be properly normalized.
Here's how to hide it.
Step A. Click File--> Options-->Current Database.
Step B. Under the Navigation section, uncheck "Display Navigation Pane".
The next time the database opens, the Navigation Pane will not be displayed.
To reveal the Navigation Pane again, press [F11].
The foreign key to tblHousehold is also indexed, but it is set to allow duplicates. It must allow duplicates because we have a one-to-many relationship between tblHousehold and tblAddress. Each household can have one or more addresses. A household, for example, can have a street address and a P.O. Box. The household ID for that household must appear twice in tblAddress, once in the record for the street address and again with the record for the P.O. Box. Therefore, the index for fkHouseholdID must be set to permit duplicates.
For example, each person can have none, one or more phone numbers (home, work, mobile). That's a one-to-many relationship. It requires two tables, one for people and one for phone numbers. The Primary key from the table on the "one" side is a linked to the Foreign key in the table on the "many" side.
Define the one-to-many relationship in the relationships window, "Database Tools-->Relationships" .
Drag the required tables from the Navigation Pane into the Relationship Window.
Click to select the Primary Key field in the "one" side table.
Hold the mouse down and drag it over the Foreign Key field in the "many" side table.
In this example, PersonID is the Primary Key in tblPerson and the Foreign Key in tblPersonPhone. Note that there are two additional relationships. One between tlkpPhoneTypes and tblPersonPhone and the other between tlkpPriority and tblPersonPhone. A complete phone record indicates the person to whom it belongs, what type of phone it is (work, cell, etc.) and whether that person considers it their Primary phone.
You can further refine relationships. If the Edit Relationships dialog hasn't opened automatically, double-click on the join line between tables. The most important element here is the checkbox to Enforce Referential Integrity.
Referential Integrity, or RI, ensures that each child record in the child table is joined to a record in the Parent table. In other words, you can't have "orphaned" phone numbers which do not belong to anyone. The two other options, Cascade Update and Cascade Delete, are useful in the appropriate situations.
Now that you know what a primary key is, what its purpose is, and how to create one, it should be much easier to understand foreign key and learn how to create them in your tables. A foreign key is a field in one table which refers to the primary key field in another, related, table. It indicates the two tables are related and helps us define that relationship. As you might expect, we often refer to these as the primary and foreign tables.
Fortunately, Access provides a Find Duplicate Query Wizard to help you do this.
Start by selecting "Queries" in the Create tab of the ribbon.
When the Query Wizard dialog opens, select "Find Duplicates Query Wizard" from the list and click "OK".
Next, select the table (e.g., tblCustomer) which you think might have duplicate records in it. Click "Next" to move ahead.
Select the fields which may have duplicate values. Be careful not to include primary keys, which can't be duplicated, by definition,
or other fields which could be legitimately duplicated for two or more customers, such as addresses for several tenants of a commercial building. Click "Next" to move ahead.
At the next page of the wizard you can add other fields from the table to help you decide on how to handle any duplicates that appear.
Add any fields which will help you identify true duplicates. For example, two customers may share a name, especially if it's a common one like, "Jack Smith".
Other fields in the record, like address, phone number, etc. can help you decide if they are the same person or not. Click "OK"
At the final page of the wizard, name the query and click "OK" to see the results. Use a name that follows standard naming conventions, such as "qryDuplicateCustomer."
*Of course, a properly designed interface can help prevent that problem.
Defining a field as date/time, for example, tells Access to format data entered into that field in one of the standard date formats. In the U. S., we use the format mm/dd/yyyy, (08/01/2003) for dates. In South America, on the other hand, the standard format is dd/mm/yyyy (01/08/2003). The Regional Settings you have selected in the Windows Control Panel determines which format Access will use.
Again, you already know that there can be no nulls in our primary key field, pkHouseholdID. In other cases, though, you might want to permit null values in order to facilitate data entry. For example, my niece recently had a baby. I know the baby was born a few weeks ago because my brother, her grandfather, called me. However, I don’t yet know the baby’s name because my niece hasn’t had a chance yet to let me know what it is. Therefore, to add the new baby to my family and friends database, I either have to allow null values for first names, or make up a fake first name for her. I don’t know what her name is, but I do know she has one! That’s a null value. In this case, the right approach is to allow null values for that field in that table.
The following function illustrates each of these functons.
Public Function ShowDates()
'==================================================================
' Grover Park Consulting ShowDates
' George R. Hepworth 10/24/2016 07:47 AM
' This function illustrates some basic date functions
' Error handling not included
'
' Declare the variables you need to manage dates
'==================================================================
Dim dtToday As Date
Dim dtTomorrow As Date
Dim dtChristmasday As Date
Dim intDaysLefttoChristmas As Integer
'Set the value of a variable called dtToday to the current date.
dtToday = Date
' Use the DatePart function to determine the current year.
' The year is concatenated with the month and day (12/25/ to set the value of the variable
' to Christmas of the current year.
' use CDate to convert the result to a date datatype
' If you fully qualify all parts of the DatePart Function, Intellisense supplies appropriate constants.
' The last two arguments --FirstDayofWeek and FirstWeekofYear -- are optional
' and default to the constants shown in this example if not supplied
dtChristmasday = CDate("12/25/" & DatePart(Interval:="yyyy", Date:=dtToday, FirstDayofWeek:=vbUseSystemDayOfWeek, FirstWeekofYear:=vbUseSystem))
' An alternative is to use the Year() function instead of DatePart
' Comment out the preceding line and uncomment this line to test
' dtChristmasday = CDate("12/25/" & Year(Date:=dtToday))
'Use the DateAdd function to add 1 day to the current date.
dtTomorrow = DateAdd(Interval:="d", Number:=1, Date:=dtToday)
'Use the DateDiff function to calculate the number of days between tomorrow and Christmas
intDaysLefttoChristmas = DateDiff(Interval:="d", Date1:=dtToday, date2:=dtChristmasday, FirstDayofWeek:=vbUseSystemDayOfWeek, FirstWeekofYear:=vbUseSystem)
MsgBox Prompt:="Today is: " & dtToday & vbCrLf _
& "Tomorrow is: " & dtTomorrow & vbCrLf _
& "Christmas is " & intDaysLefttoChristmas & " days after tomorrow.", _
Buttons:=vbOKOnly + vbInformation, _
Title:="Can You Wait That Long?"
End Function
When you create an index on a field, you can designate whether values in that field can be duplicated or not. Obviously, a primary key field must be set not to allow duplicates because an primary key must always have unique values in each record. Other situations may call for setting this option to not allow duplicates. We’ve already discussed reasons for not using natural keys, such as social security numbers or employee IDs, as the primary key in a table. However, when you do include these values in your table, you want to ensure that they are not duplicated. Setting the “Unique” option to “yes’ will handle this restriction at the table level. Only one instance of each Employee ID will be permitted in the table if the index for that field is set to prevent duplicates. Access will warn you about the duplication and prevent you from saving the duplicate record.
However, setting the limit-to-list property to “yes” brings up another issue—handling “errors” gracefully. I put errors in quotes because there is more than one kind of error.
Bugs
Bugs in software occur when the developer makes some kind of error in logic or syntax that causes code to fail when it runs. Those errors, unfortunately, are almost inevitable. I may be going out on a limb a bit with this statement, but I’d be willing to bet that no programmer who ever lived has produced completely bug-free code in every application. The error trapping and handling code I showed you in chapters 8 and 9 of my book Grover Park George On Access, is mainly aimed at that type of error.
Invalid or Inconsistent Data
The second type of error message users see really isn’t a program or application error at all.
I’m talking about the type of “error” caused when a user enters a value into the combo box which isn’t in its list. Access considers that an error because the limit to list property tells it to accept only values on the existing list. If you or your users don’t select one of those pre-existing values, an error is raised. Such errors occur all the time because people just don’t think the way computers do.
Good, consistent error handling can maintain a smooth, cordial relationship between your users and your database. If you let Access tell them they’ve committed an error, when all they did was try to enter a new name on a list, well, that’s just not a good thing.
Your application should offer the user a chance to handle the problem gracefully. For example, in the case of the missing list item, you could offer users a way to add a new item to the list.
The primary key of a relational table uniquely identifies each record in the table so that relationships between that table and other tables in the database can be defined. With rare exceptions, Access tables should have primary keys. The database developer is responsible for selecting or creating the primary key from among the possible keys identified for that table. Natural keys, or keys based on naturally occurring attributes such as name or Social Security Number, should not be used as primary keys. Primary keys should be meaningless outside of the database in which they are used. As a general rule, users of the database should not see or even be aware of its primary keys. So, the next question that arises is, “What makes a good primary key?” The best answer, as far as Access is concerned, is usually the AutoNumber, as illustrated in Figure 4 13.
This is, in fact, the "Best Practice" for all database applications.
The "Front End" consists of the interface elements needed to run the application, including forms, queries, reports, code (macros and VBA).
The "Back End" consists of the data storage: Access tables, SQL Server tables, Excel worksheets, text files, other databases like MySQL or Postgres, and many others, including Dbase . As soon as you have completed the initials phase of development, you should split the Access accdb into the Front End and Back End. This way, further development can proceed on the interface elements while not having to worry about altering the tables or data in them.
When you create a "Front End--Back End" split database for Access, for example, the Database Splitter Wizard does this for you. You can also create links to tables in other Access files or other data sources manually. In fact, you can link to many other types of files this way including Excel (*.xls and *.xlsx), Dbase (*.dbf), paradox (*.db) and ordinary text files (*.txt).
You can move your tables from the local Access accdb file into a SQL Server instance, either locally on your own computer, a server in your network environment for which you have credentials, or to a remote server for which you have credentials.
Let's look at the process of linking one Access accdb to the tables in a second Access accdb.
You'll follow similar steps to link to other data sources, which we can learn about in another tip.
Start with the External Data Tab. Select the "Import Link-->Access" option to get external Access tables. Other options are also available if you have an ODBC source (e.g. SQL Server) or an Excel workbook, etc.
Navigate through File Explorer to find the file which contains the table(s) you want to use. Select the table(s) to which you want to create a link.
When the "Link Tables" dialog opens, select one or more tables from the list, and click "OK". Access will link the selected table(s) to your current database. The data in linked tables remains in those tables in that other data source. It is available for your users to add, modify or delete records. Therefore, you can use the data in linked Access tables as you would data in any other local tables. However, you can not make changes to the structure of linked tables; that is, you can NOT do things like add new fields or change datatypes in linked tables. To do that, you'd have to open the application (Access, Excel, SSMS, etc.) through which you can modify tables.
As you can see in Table 4-1, the input mask incorporating 9’s for the last four positions will allow the user to enter partial extensions, which isn’t desirable. However, the alternative, is to use 0’s and require the user to enter the full nine-digit ZIP code for everyone, or nothing for everyone. Of the two alternatives, the second even less desirable. That’s why the default input mask is the way it is. Access provides you with other built-in input masks for phone numbers, social security numbers, dates and times and a special password input mask. You can also construct your own input masks using the characters you just learned about, along with several others. I’ll show you how to use some of them in other fields, and you can learn how to use others on your own. Input masks are very useful in controlling entries made by users both by giving them a guide to follow and by preventing inappropriate values from being entered.
Object libraries are a great way to bring functionality into your Access database. For example, if you want to automate MS Word or MS Excel, you can set a reference to their respective object libraries and use their functions directly in Access. However, there is a downside. "Missing References" means that an object library reference has been set to a component— such as an ActiveX control on a form— which doesn't exist, or isn't properly registered on the machine. In my experience, this is often the case when an mdb or accdb was created using an earlier OS and Office environment, and then moved to a newer OS with different applications installed.
When that accdb or mdb is copied onto a computer where the installed version of MS Access is one older than the version on the development computer, references go missing. MS Access can't resolve a reference to MS Excel 2016 when it is loaded onto a computer where Office 2010 is installed. You'd think Microsoft could figure out how to handle this, but it's been more than twenty years since MS Access was introduced and this has always been an issue — at least since MS Access 2.0 was launched.
Many (but not all) crashes at start up and unusual code failures are caused by this problem.
To check whether a Missing Reference is the problem— and to fix the reference if that is the problem— follow these steps:
Step 1. Press and hold down the [Shift] Key on your keyboard while opening the accdb. Hold it down until the database is fully open. This will bypass any start up processes when the database opens and prevent Access from crashing or raising errors due to missing references.
You may or may not see the Navigation Pane at this point. If the database was set up to hide the Nav Pane, you'll see only the blank screen with the ribbon at the top.
Step 2— optional. If you need to display the Navigation Pane, press the F11 key. To fix references problems, though, this shouldn't be necessary.
Step 3. To check for missing references, you need to open the IDE, or Integrated Development Environment, for VBA. The quickest way to get there is to press [ctrl] G. You can also get there through the ribbon, using "Database Tools — > Visual Basic". (The ribbons look a bit different in Access 2007 than they do in later versions.)
Step 4. When the Code Window in the IDE opens click "Tools — > References" on the Menu Bar to open the references dialog. (In the IDE, you'll find the old-style menu bars are still used, not the ribbon as in the user interface.)
The references listed in the dialog box point to all of the components available to Access (and other Office and Windows programs) on the machine. Components used, or supposedly used, by the database are displayed at the top of the list. They are checked. References to components which are available, but not required, are not checked.
Step 5. If any references are checked, but not available on the machine on which the database is installed, they will appear just below the checked references at the top of the list, but they will be indicated as "MISSING". If you see missing references, correcting them should also correct function failure and crashing problems. In any event, you need to correct them to prevent other problems in the database.
There are two possible reasons for a "MISSING" references.
First, the component was checked on the original development machine, but not actually required by the current database.
Second, the component is checked because it is required by the VBA in the database, but is not installed on the current machine.
Step 5a. If the problem is a reference to a component that is not required, simply unchecking and recompiling the VBA code will resolve the problem. Uncheck the reference, Click "OK" to close the references dialog box. Click "Debug — >" Compile from the menu bar.
It is generally a good practice to include only references actually needed by the current accdb.
If the code compiles properly at this point. Save and close the code module. Then, close and reopen the database normally. If it opens without crashing, you have resolved the issue.
Step 5b. If the code doesn't compile after you uncheck the missing reference, or if the database crashes when you try to reopen it, you need to find and re-add the missing reference.
Reopen the references dialog and inspect the reference marked as missing. The bottom section of the dialog displays the full path to the DLL or ActiveX component selected in the list above. Note that most of them are in either Windows system folders, or the common files folder under Program Files. Depending on which OS version you are using, though, the exact locations may vary. You'll need to browse these folders for the missing reference first. Note the name of the DLL or OCX that contains the missing component. This will actually refer to the component's name and location on the original PC where the database was created. The component may be in a different folder on your PC, or it may be missing completely.
Of course, if it's an Office version problem, take note of the version referenced and locate the proper version as installed on your computer.
But again, if you never use MS Word, MS Excel, MS PowePoint or MS Outlook functions, the better choice is simply not to set references to them.
Step 6. Click on "Browse" to look for the component in the "MISSING" reference. If you find it, select it to register it. Follow the dialog prompts to register and use the reference.
If you can't locate the component, you'll need to locate and install it. Contact your IT support desk for assistance, or search the internet for it.
Step 7. Once you locate and register the referenced component, close the dialog, compile and save the code and try to reopen the database again.
You should also learn about "Early Binding" and "Late Binding" if you plan to use MS Office automation. Late binding is a coding technique which does NOT require setting references to specific versions of MS Office so that your code is more installation independent.
ghepworth@gpcdata.com
What is not obvious at this point, is that you will seldom, if ever, use one of these “real world” or “natural” key attributes as the primary key in a physical table. Consider, for example, the problem we’ve identified with names and social security numbers. In our logical data model, it is the combination of these four attributes which uniquely identifies a person. Access would allow you to set them up as a compound key in a physical table, but that would quickly lead to a lot of problems, problems you’ll want to avoid as much as possible. SSN by itself is more attractive as a key attribute, and in some contexts, it is adequate as a key attribute in a logical model. Still, for reasons you’ll learn in Chapter Four, it doesn’t quite work as the primary key in a physical table.
You have little control over Input Boxes. You can specify the message text, title and position of the input box, and provide a default value. Not much more.
However, you have a lot of control over Message Boxes: size, background clolor, font, font size and color, command buttons, message text and title , etc.
You can even create custom forms that serve this purpose. You'll find an example of this approach to providing error feedback in the Northwind Developer Edition Template.
An example
The event procedure below displays an input box to collect a name. Then it displays that name in three slightly different message boxes.
Public Sub sSolicitInput()
Dim strName As String
strName = InputBox("Enter a Name", "New Name", "Washington")
MsgBox "You entered """ & strName & """", vbQuestion
MsgBox "The Name you entered is """ & strName & """", vbInformation + vbYesNo
MsgBox "Are you sure you want to use """ & strName & """?", vbExclamation + vbYesNoCancel, "Here's Your Answer"
End Sub
Here's how a problem can occur:
Most of the time, users will enter the expected value in the input box. Your database asks for a name, a number or other piece of information and the user provides it because they need it to complete their task. But sometimes, the user doesn't do the expected.
Suppose the user responds to the input box by clicking "Cancel" instead of entering a name and clicking "OK". Instead of a value for the variable "strName", you'll end up with an emptry string from the input box.
How do you handle such "unexpected" responses? Add a conditional statement to check for them, and take an appropriate action.
Public Sub sSolicitInput()
Dim strName As String
strName = InputBox("Enter a Name", "New Name", "Washington")
If strName <> "" Then
MsgBox "You entered """ & strName & """", vbQuestion
MsgBox "The Name you entered is """ & strName & """", vbInformation + vbYesNo
MsgBox "Are you sure you want to use """ & strName & """?", vbExclamation + vbYesNoCancel, "Here's Your Answer"
Else
MsgBox "You did not enter a name. Please enter a name, or cancel the addition of this record.", vbInformation + vbOkOnly, "No Name Entered"
End If
End Sub
This is only an example of the logic you'll use in your database. You will be doing something more complicated with the input string than simply using it to show a message back to the user.
In a many-to-many relationship, each of the entities in one entity set is related to one or more entities in a second entity set. Each of the entities in the second entity set is related to one or more entities in the first entity set. For example, as we saw earlier, Company XYZ’s business rule about company cars says that any car in the car pool can be checked out by any employee, that creates a many-to-many relationship. An EMPLOYEE checks out one or more COMPANY CARS. A COMPANY CAR is checked out by one or more EMPLOYEES. Of course, there is another business rule which says an employee can only check out one car at a time, but on any given day, any car in the company car pool can be checked out by any employee. Many cars, many employees.
Well, yes, actually there is a way to do that; and it is relatively easy to do because Access provides a Query Wizard to do just that. This query wizard creates a query with an OUTER JOIN for that purpose. To use it, select "New" from the Query Object window; then when the query dialog opens, select "Find Unmatched Query Wizard". Follow the prompts to create your query.
The SQL statement created by that query wizard, featuring a LEFT JOIN follows.
SELECT tblCompany.pkCompanyID, tblCompany.CompanyName, tblCompany.fkCompanyHeadID
FROM tblCompany
LEFT JOIN tblAddress ON tblCompany.pkCompanyID = tblAddress.fkCompanyID
WHERE tblAddress.fkCompanyID Is Null;
In this SQL statement, the LEFT JOIN, also known as a LEFT OUTER JOIN, tells Access to include ALL records from the table on the "left" side, e.g. tblCompany, whether there is a matching record on the "right" side or not.
This ensures that all companies are included whether they have a matching address record or not.
If you wanted to see all companies and any existing addresses, the last line in that SQL statement would be omitted. However, we only wanted to see a list of companies without addresses. To create this result, the wizard added the "WHERE" statement to exclude all address records which do have a foreign key from the company table. The resulting recordset will show all companies which do not have a matching address record. The "OUTER JOIN", combined with the WHERE clause, makes that possible.
Our first field, HouseholdName, is a text field. Data type tells you what kind of data a field contains. Text, for example, includes letters and numbers used in names of persons, places or things, or telephone numbers and ZIP Codes. Numbers , on the other hand, consist of numeric data which can be used in mathematical calculations.
To use the Expression Euilder in a query, for example, open the query to the Query Grid in design view and click the Builder Button on the Toolbar. (It has three dots on it "…", along with the Wizard Wand. )
The Expression Builder opens, giving you access to all of the objects in the database along with built-in Functions and and functions you've created yourself.
Doubl-click on elements in the lower-middle or lower-right sections of the builder to add them to your expressions. When you click "OK" the expression is copied back to your query.
The Expression Builder also provides context sensistive help and templates for functions to guide you as you create your expressions.
The last data type I’ll discuss here is the AutoNumber . I love these things; they are powerful and very useful. An AutoNumber, according MS Access Help, is “a unique, sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated.” The reason for having AutoNumbers in your database is to create primary keys for your tables. You haven’t learned about primary keys yet, so I won’t delve into them right now. However, I’m sure you’ll find them just as exciting as I do when you learn how to use them.
The Bound Column property tells Microsoft Access which column's values to use as the control source of the control. It should be the column which contains the Primary Key from the lookup table which provides the values for the control. Normally the bound column will be the leftmost column in a combo or list box. When a user clicks on a name in the list to select an employee, the value of the bound column (i.e. the Primary Key from the Employee table) is saved to the vehicle checkout table, in the foreign key field that identifies the Employee checking out the vehicle.
Normally, you will set the width of the bound column to 0 (i.e. zero inches), meaning that it is not visible to users. You want users to see only the natural language choices, not the primary key (foreign Key) itself. That can only confuse things.
Potential for Confusion: Indexes versus column numbers. "Microsoft Access uses a zero-based index to refer to columns in the Column property. That is, the expression Column(0) refers to the first -- or left-most -- column; Column(1)refers to the second column from the left; and so on. However, the Bound Column property uses 1-based numbers to refer to the columns. This means that if the Bound Column property is set to 1 (the leftmost column) , you access the value stored in that column by using the expression Column(0)."
HouseholdName illustrates three important points about standard naming conventions. •The name of the field is written out in full and unabbreviated. •It is written with no spaces between words in the field name. •All words in the name are capitalized.
This is one way Access is different from many other development tools. It gives Access an advantage because it speeds up development.
In addition, controls (text boxes, list and combo boxes, etc.) on the form can be bound to fields in the table. For example, on the form bound to tblCustomer, you can have a text box called "txtCustomerName", which gets its values from the field called "Customername" in tblCustomer. We say that txtCustomerName is bound to CustomerName.
However, controls on forms don't have to be bound to fields in the underlying table, and there are times when you want to use an unbound control on a form.
For example, you can use an unbound form to display the results of a calculation using two other controls. Let's say you have a field for "StartTime" and a second field for "EndTime" in your Project Work table. These fields are bound to controls on a form where you can enter the time you begin working on a project and the time you stop working on it on any given day. To display the total time you spent working on that project that day during that work session, you can create a third, unbound, control and use it to display the calculated elapsed time in minutes.
Note: We do not, as a general rule, store that calculated value in the table. It's only displayed on forms, and on reports, or used in other calculations. We avoid storing calculations to prevent the problem of having changes to the underlying source fields not reflected in the calculation. That's why this control on the form is NOT bound.
The control source for this unbound field would be:
= DateDiff("n", txtStartTime, txtEndTime)
where DateDiff is a standard Access function which calculates the elapsed time, "n" tells the function to calculate the time in minutes (as opposed to hours "h", days "d", etc.), and txtStartTime and txtEndTime are the two time (or date) values to be used in the function.
This unbound control, perhaps called txtElapsedTime, will always recalculate and display the correct elapsed time for the values in the two bound controls if either of them is updated.
Some calculated values can be quite complex. For example: Let's say you have a display only form on which you want to show all of the work done on any given day across multiple projects. In the query to which the form is bound, the same DateDiff function is used to calculate a TotalTime for each project. (This could also be done in the control on the form, by the way. It might be slightly more efficient to do it in the query, depending on how complex the calculation is.) Let's also say that all time spent on a project is recorded, but some of that time is billable to the client, while other time is spent on non-billable tasks related to that project, such as training on a pertinent topic which can't be billed to any one client. So, in addition, there is a calculated control on the form which takes this total project work time and calculates billable time, in hours.
This calculated field has as its control source: =Iif([Billable] <> 0, [TotalTime],0)/60 This calculation means that, if the value of the Billable flag is not false (i.e. is not 0) then divide the total time in minutes by 60 minutes per hour to get the billable hours. If, however, the Billable flag is false, i.e. it is 0, divide 0 by 60. The results of this division will always be zero. Note also, that by including the Billable flag in the underlying query, we see two lines for each project if there is both billable and non-billable work for it on the same day. On the other hand, of course, if only billable work, or only non-billable work, is completed, then only that one line will appear on the form.
One of the mixed blessings of Access is that it usually offers more than one way to accomplish a task. If you find one method more comfortable than another, that is a good thing. On the other hand, multiple choices can be a bit confusing at times. In the rest of this book, I’ll mostly show you how to do things using the methods I prefer, but I will also describe other options. Creating a new table is a case in point. To start a new table, you can double-click on one of the choices in the database window, or click “New” on the tool bar.
For example, suppose you have a table with fields for Phone and Fax Numbers for your customers. When you add a record for a new customer, you might leave their "FaxNumber" field blank because you don't know whether they have a fax number, or if they do, what it is. That's a "Null". "FaxNumber" for this customer may or may not have a value.
Later, you when you learn their fax number (assuming they do have one), you can change the null value to the fax number.
When you are writing queries, you can use the "Nulls" in a field to select only those records which are NOT Null (i.e., those records which do have a value) in that field.
For example, this query will return a list of all Records in the customer table where the customer does have a Fax Number.
SELECT tblCustomer.pkCustomerKey, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.CustomerFaxNumber
FROM tblCustomer
WHERE tblCustomer.CustomerFaxNumber Is Not Null;
Just remember that Nulls are not the same thing as Zero Length Strings.
A Zero Length String, or ZLS, is a text string which has no characters in it. How does that happen, and why do we need it
Take the previous example of a Fax Number for customers. When entering a new customer record, you mistakenly enter a value for their Fax Number (making it no longer Null) but then you realize that value should have been entered for a second voice line.
So, you do a cut-and-paste on that value, cutting it from Fax Number and pasting into the proper control. Now, that Fax Number contains a ZLS.
The query criteria above will no longer find that Customer record because the FaxNumber is NOT Null any longer; it is a ZLS. Here's a good trick to filter on "empty" values, i.e. values that can be either Null or ZLS.
SELECT tblCustomer.pkCustomerKey, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.CustomerFaxNumber
FROM tblCustomer
WHERE Len(tblCustomer.CustomerFaxNumber & vbNullstring) = 0;
Concatenating the vbNullString--which is a ZLS--with the Fax Number, will result in a ZLS, not a Null, and that value has a length of 0.
Here is a good overview of Nulls, on Allen Browne's site. Mr. Browne has been retired for a few years now, but his site remains one of the more important sources of Access knowledge available.
Access comes with several templates, and you can download others from the Microsoft web site. Frankly, I’m not a big fan of the templates Microsoft provides. For one thing, they can, and often do, create tables that aren’t properly normalized. For another, they do not observe good naming conventions. As my friends know, there are few things as irritating to me in a database as non-standard names and un-normalized tables. Once you become a competent data modeler, you can use the templates for rapid proto-typing of new databases. However, you’ll find yourself making significant changes to the resulting databases to bring them to a usable condition.
One part, usually called the "Front End", contains the interface objects: forms, reports, queries, macros and code modules.
The other part, usually called the "Back End", is an accdb file containing only the tables.
The tables in the back end are linked to the front end.
Users should be limited to using only the front end.
There are a few advantages to split databases.
Controlling Access to Shared Data. The back end data files can be placed in a shared folder to which only authorized users have access. Each user or user group can then be given copies of the front end with different features. For example, data entry users can have a front end with forms designed to add, modify or delete data, while managers can be given a front end whch includes only reporting functions.
Improving Performance. Having the front end on a local PC, with the back end in a shared network folder, can improve performance.
Updating the Database. Because the data tables are separated from the interface objects, it is easier to create and distribute an updated user interface without having to worry about losing existing records in the back end.
Previous Access versions have a Database Splitter Wizard in the Ribbon, available from "DATABASE TOOLS --> Move Data --> Access Database" which walked you through the process of splitting your database.
This was replaced in current versions of Access by a separate, free tool Called SQL Server Migration Assistant for Access, SSMA.
Download SSMA for Access
Split the database before it is distributed for production.
Access appends the extension “mdb” to these files. MDE and MDW Files You’ll see other extensions, such as “mde” and “mdw”. They are also Access files, but they’re beyond the scope of this book. Briefly, “mde” files are “compiled” databases in which the tables, forms, reports, etc. can no longer be changed. They’re good for distributing a finished database. “mdw” files are part of Access security.
The choices are:
-Go to the start of the field
-Go to the end of the field.
-Select the entire field
These choices affect all forms in the database.
To set this option got to "File-->Options" in the ribbon. When the dialog box opens, select the "Client Settings" property. There are three radio buttons for the "Behavior entering field" option. Select the one you want for your database.
In this chapter, you’ll also get your first introduction to standard naming conventions. Most professional Access developers, including myself and my students, use some variation of these conventions. These conventional ways of naming Access objects, such as tables and fields, have an interesting history. It is often referred to as Hungarian notation in honor of the man credited with originating it, a Hungarian named Charles Simonyi. Beyond the scope of this book, it is worth your time to learn more about its history and development
Here is the syntax:
SELECT "Customer" as ContactType, tblCustomer.CustomerID AS CompanyID, tblCustomer.CustomerName AS CompanyName
FROM tblCustomer
UNION
SELECT "Supplier" as ContactType, tblSupplier.SupplierID AS CompanyID, tblSupplier.SupplierName AS CompanyName
FROM tblSupplier
ORDER BY ContactType, CompanyName;
The first SELECT statement retrieves records from the customers table. The second SELECT statement retrieves records from the suppliers table. The UNION keyword puts both sets of records in the same columns in the query.
There is only ONE ORDER BY Clause in a Union query. It must refer to columns in the first SELECT clause. Of course, as this example shows, those columns can either be field names from the table (CompanyName) or calculated values (ContactType).
The requirements for a UNION query include the following:
Each SELECT statement must return the same number of fields, in the same order, left to right.
Using the "AS" operator, you can rename the fields in Union Query to reflect the combined values.
The following union query renames the CompanyName field to "Supplier/Customer Name" in the query output.
SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Suppliers]
UNION SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Customers];
NOTE: This example is intended for illustration only. I would not have separate tables for two closely related entities. Rather there would be one "company" table containing the names of all organizations, and separate, related tables with details pertaining to companies in the role of Customer and companies in the role of supplier. In fact, it is not hard to think up scenarios in which one company would be both a supplier to, and a customer of, the organization for whom the database is built.
In this chapter, I’ve given you an overview of the initial phase of creating a new Access database, starting with the cardboard box-to-filing cabinet metaphor. You learned that data becomes information when it is organized according a logical (as opposed to physical) model. You saw that, before you can transfer your raw data from a “cardboard box” into a “filing cabinet”, you need to spend time analyzing the bits and pieces of data that make up the information in which you are interested, figuring out how you and your users want to use that information, the business rules that will apply, and resolving the inevitable ambiguities that arise.
Perhaps the best way to explain them is with an example. A large corporation operates a fleet of company cars for the use of employees. No vehicle is assigned permanently to any one employee, and no employee has exclusive use of a vehicle. Any vehicle in the fleet can be checked out by any employee, and any employee can check out any of the vehicles in the fleet.
That's "MANY" employees using "MANY" vehicles.
A database that tracks employee use of vehicles needs one table for vehicles, a second table for employees, and a third table, usually called a junction table, which records employee use of the vehicles. Each record in the junction table in this case would include foreign keys from both the employee and vehicle tables, along with check out and check in dates.
Each time an employee checks out a vehicle for the day, a new record is added to the junction table. The new record consists of the foreign key for that employee from tblEmployee, the foreign key from tblVehicle for that vehicle and the Check out date. When the employee returns that vehicle, the record is updated with the check in date.
I am spending a good amount of time on this subject, not because I’m particularly obsessed with phones, email and mailing addresses, but because deciding how to handle them in your data model is a special case of a more general issue that frequently comes up when it is time to convert your logical data model into physical tables in Access. Sometimes, you just have to make a choice between two valid approaches. The decision is based, as much as anything, on the way the data will be used and the business rules that apply to that use. And, as I’ve stated before, that requires you spend considerable time with your customers, gaining an understanding of their workflow and requirements.
Here is a brief overview of one very common approach.
Prefix all names with a three (or four) letter designation: "tbl" for tables, "frm" for forms, "rpt" for reports, "qry" for queries.
-tblCompany
-tblLocation
-tblAddress
-frmCompany
-frmLocation
-frmAddress
-rptSalesbyMonth
-rptSalesbySalesRep
-qrySalesbyMonth
-qrySalesbySalesRep
Object names should NEVER have spaces between words.
-rptSalesbyMonth not "Sales by Month Report"
Some developers further differentiate between "regular" tables, "tblXXXXX" and "lookup" tables, "tlkpYYYYY", or even "tjxXXXXX" for junction tables, and so on.
Many Developers use prefixes and suffixes on fields in tables and controls on interface objects. For example, the primary key in a table might be called "CompanyID", or "LocationID". Some also prefix a primary key with "pk" and a foreign key with "fk", so in the Company table, the primary key would be "pkCompanyID" and the corresponding foreign key in the Location table would be "fkCompanyID". It's not a hard-and-fast rule to do that. I know some developers who do not even bother to change the default name Access assigns to new AutoNumber Primary Keys from "ID" to something else. Their reasoning is that it is simpler and fully qualifying references to that field is adequate, e.g. "tblCompany.ID", or "tblLocation.ID".
By far the most important principle to observe, though, is consistency. Whatever naming convention you adopt, use it the same way through out your entire Access application.
Normally, a key attribute is one of many attributes of an entity. For example, the serial number on the back of my PC, 07130-2M2-0120, is unique; no other PC has that identical serial number. The PC has other attributes of potential interest, such as the CPU speed, amount of RAM it holds and the storage capacity of its hard drive. However, it is that serial number which uniquely differentiates it from all other PCs with similar attributes.
Another place where this principle applies is in searching and selecting items for a list or combo box. "Pre-Filtering" the records to display helps improve performance and makes searching that resulting sub-set of records easier.
You create queries to filter records in a table. For example, the following SQL will return one record from a table of tens of thousands of US Zip Codes:
SELECT tblZIPCode.ZipCode, tblZIPCode.City, tblZIPCode.State
FROM tblZIPCode
WHERE tblZIPCode.ZipCode = "98043";
The record returned by this filtered query will be the one US city (Mountlake Terrace, WA), which has the 98043 zip code.
To show all of the records where the zip code starts with "980", you can use a wildcard:
SELECT tblZIPCode.ZipCode, tblZIPCode.City, tblZIPCode.State
FROM tblZIPCode
WHERE tblZIPCode.ZipCode Like "980*";
Note two things about the WHERE clauses which apply the filters. First, even though US ZIP codes are composed of digits, they are actually stored as text (and more precisely Short Text in an Access table), not as numbers. A simple way to verify this is to ask, "Could you add two ZIP codes together and get a meaningful result?"
Of course not, so these digits are text, not numbers. That means they are marked with the text delimiter: " , i.e. "98043"
Second, when you apply a single filter criteria, you use the equal sign: = "98043"
However, when you apply a wildcard filter, you must use the Like operator and the wildcard symbol and syntax: Like "980*"
For all of those reasons, a database, such as my personal contact database, cannot rely on SSN. If I decided to ignore these problems and use it anyway, I would have to create fake SSN’s in order to avoid violating the first principle listed above. No primary key can have a null value. The Oregon District Court cited above, for example, would be forced to create fake SSN’s for prospective jurors who refused to divulge their real SSN. And that could create a whole additional set of problems.
Create the query from the tables which contain the data you want to display in your report.
Then, save the query with a name reflecting its purpose, e.g., "qryEmployeeVehicleCheckoutsByDate".
When you start the report wizard, select your new query as the report's record source.
You can modify that query after it is created, of course.
Unlike forms, which are designed for data input and modification, reports are static displays of the selected data, so we do not worry about whether queries used in reports can be updated or not. This allows us to combine tables, fields, and additional calculations as appropriate for the intended report.
In a one-to-many relationship, each entity in one entity set is related to one or more entities in a second entity set. For example, an potato grower may cultivate one or more fields of potatoes. This is a one-to-many relationship. If you were building the data model for a government agency tracking pest control for local potato farmers, your data model would need to accommodate this relationship. A POTATO GROWER cultivates one or more POTATO FIELDS. A POTATO FIELD is cultivated by one POTATO GROWER. One-to-many relationships are the most common, and usually the easiest to define and set up in a database.
When you create a query using those tables, Access creates the default joins between those tables in the query using the relationships you previously defined when you created the tables.
Relationships are defined as:
One-to-One. Not very common, these relationships restrict each record in one table to one and only one record in another. (You'll see more about this in a future Top of the day.)
One-to-Many. The most common, these relationships allow one record in the "parent" table to be related to none, one, or more records in the second, "child" table. (You'll also see more about this in a future Top of the day.)
Many-to-Many. Less common, but frequently found in more complex databases, these relationships call for three tables. Each record in one table can be related to none, one, or more records in another table. And, each record in the second table can be related to none, one, or more than one records in the first table. We need a third, intermediate, table between these two other tables in which to create the relationships that enable this "many-to-many" to work. The most common name for this intermediate table is "Junction Table". I've seen other names, so look for the relationships involved to recognize them. (You'll also see more about this in a future Top of the day.)
To support these relationships, there are different ways to "Join" tables when creating queries to return records from them:
There are three different types of joins.
The "basic" type, the one which is used by default, is called an "INNER JOIN". An inner join means Access selects records from both tables only when the values in the joined fields are equal. That's what you want to see in situations where you want to show customers and their addresses, but don't want to see customers for whom you don't have an address. You'd want to do that, for example, in creating mailing labels.
Example SQL:
SELECT tblCompany.pkCompanyID, tblAddress.pkAddressID, tblCompany.CompanyName, tblAddress.fkCompanyID, tblAddress.Address, tblAddress.City, tblAddress.State
FROM tblCompany INNER JOIN tblAddressON tbCompany.pkCompanyID = tblAddress.fkCompanyID;
The other two types of joins are called "LEFT OUTER JOINS" and "RIGHT OUTER JOINS". We'll learn about them in future "Tips of the Day."
In a one-to-one relationship, each entity in one entity set is related to one, and only one, entity in a second entity set. For example, many corporations provide company cars for their employees. ... ... a company can assign one car to one employee for his exclusive use. For example, the ABC Company provides each of its sales managers a car. This is a one-to-one relationship, assuming of course, that no sales manager is allowed to have two company cars at the same time (another business rule).
One example of a one-to-one relationship is a company which operates a fleet of company cars. Each car is permanently assigned to one and only one employee. Each employee has use of one and only one company car.
When we talk about an entity, we are referring to the thing itself. The tables in your Access database will be based on entities. We are also interested in facts about entities. For example, one thing we want to know about a DOCUMENT is the kind of information it contains. We called these DOCUMENT GROUPS. Another thing we need to know is the TIME PERIOD to which it applies. In database terms, attributes are facts about an entity in which we are interested. To put it another way, one purpose of the database is to capture attributes about the entities in the database.
Resist the temptation to use lookup fields in your tables. Most of the time, they are more trouble than they are worth and they can lead to number of problems, not the least of which is fostering bad table designs.
Lookup tables themselves are very useful tools, but you should always store the foreign key from a lookup table in the main table not any data value.
When you want to display choices from the Lookup table to your users, create a combo box on a form to do that.
Even though this is a very simple database, there are two ways to define the relationships between DOCUMENTS, DOCUMENT GROUPS and TIME PERIODS. Therefore, we have to choose between two data models. Which one is the right one? I’ve had to address this question in the process of designing every database I’ve ever built. It is almost always possible to express relationships between certain entities in more than one way. Often, different approaches can be equally valid on the surface. Therefore, I believe the answer to that question is, “Use the model which best serves the purpose of the database.” It is only partly dependent on the data itself. Selecting the right data model means carefully evaluating the purpose for which the database will be used, and the workflow which it will support. Who will use it and how will they use it? What rules apply to using it? And that, in turn, requires that you spend enough time interviewing your customers to fully understand the purpose of the database and the workflow which it will support.
because you must ensure that the icon is available to everyone in a common, shared folder.
A.Select "File-->Options-->Current Database" from the Ribbon.
B. Use the Browse button to locate and select your application Icon
You can use either an icon file (*.ico, *.icn), or an icon within a program file.
C. You can also make that icon appear with all forms and reports by checking "Use as Form and Report Icon".
My filing cabinet contains all kinds of documents: canceled checks, credit card statements, bank statements, bills from our doctor and dentist, renewal notices from our insurance company along with Declarations Pages for our policies, and mortgage papers from when we bought the house. We keep some of them for legal reasons (tax returns, for example) and others for historical reasons (insurance documents, for example). All of these documents are important enough for our family to want to keep them in a safe place where we can refer to them when necessary. These documents are the entities around which our filing system is built. In database terms, an entity is an object in which an organization is interested and about which the organization wants to collect and maintain information. To be an entity, a thing must exist and be distinguishable from other objects. It can be concrete—like the pay stubs in my filing [cabinet]. You can feel and see them. It can be abstract—a fact or concept like a “forty hour work week”. You can’t see a “forty hour work week”, but we all know what it is, at least in theory. We define entities by stating what kind thing they are and how they are different from similar things of that kind.
A. Create an unbound form. A good starting point might be approximately 2 inches tall by 5 inches wide. Other dimensons will work, depending on your environment.
Experiment until you get a result you like. Larger monitors can support larger sizes.
B. Using labels and images, such as your company's logo, add the name of your application and version, your company's name, and any author credits.
Be sure to include copyright information, as appropriate. Add contact information (email and phone number) as appropriate
C. You can use a textbox to display the current date, etc.
D. Save the form as "frmSplash", or "frmStartup"
E. To display the splash page when your database first opens, select "File-->Options-->Current Database" from the Ribbon.
F. In the "Display Form/Page" property, select your splash form, i.e. "frmSplash".
The next time your database opens, the splash screen will display. You normally want the splash screen to close automatically after a few seconds. Use the form's "On Timer" event to do that.
A. Open the form in design view and display the property sheet.
B. Set the Form's Timer Interval to 1000. This represents 1 second.
C. Insert code like this into the Form's Timer Event:
Private Sub Form_Timer()
10 On Error Resume Next
20 intTimerCount = intTimerCount + 1
30 Me.Controls("BoxProg" & intTimerCount).BackColor = vbGreen
40 Me.Refresh
50 If intTimerCount > 3 Then
60 DoCmd.Close objecttype:=acForm, objectName:=Me.Name
70 DoCmd.OpenForm FormName:="frmMenu", view:=acNormal
80 End If
End Sub
Now, let’s add our second field to our new Household table. It will be the Primary Key for the table. To understand what it is and why it is needed, we’ll need to take another short side excursion. This time we’ll delve into the subject of Primary and Foreign Keys. I’ll keep this discussion as short as possible, but there is a lot to say on this subject, much more than I have space for here. I’ll try to tell you enough to understand primary and foreign keys without boring you.
In the Ribbon, select "Queries", then click on "Query Wizards". When the dialog box opens, select "Find Unmatched Query Wizard" and click "OK".
When the wizard opens, select the table which contains records that are not matched in the second table. E.g., select "tblEmail" to find email addresses not assigned to anyone. Then click "OK".
In the next page of the wizard, select the second table. E.g., you might choose "tblIndividuals". Click "OK" to move to the next page of the wizard
Here, you'll select the key field (or fields) on which to match the two tables. This will most likely be a Primary Key—Foreign Key pair, assuming your database is properly normalized. Then click "OK" to complete and name the query. You can accept the default name offered by Access, or supply one more in keeping with your own naming conventions
When the query opens in datasheet view, you'll see all email addresses which do not belong to an existing contact.
Of course, if your database is properly normalized, and you have properly defined Primary and Foreign Keys and you have enforced Referential Integrity on the relationship, there should be no orphans.
People can look at a series, or list, of values and decide which one is "the first" and which one is "the last" by referencing both the values themselves and the context in which the question was asked.
Perhaps such a question might be "Of this list of people and their birthdates, which birthdate came first?"
the birthdays don't need to be sorted in date order to answer that question because our minds are trained to recognize the context as well as the values. For example, if the list of birthdates is presented to us with the names of the people sorted alphabetically, we don't go to the item at the top of the list and pick it because know that's not the right context, i.e. not the correct sort order; we scan the entire list looking for the earliest one relative to other birthdates.
Computers are still learning indirect disambiguation. Relational Database Applications are not set up to handle it directly. We still need to make explicit the Sort Order the computer must use in order to find "the first".
Put it a different way.
If the question is phrased as it is above, the sort would need to be "earliest" birthdate to "most recent" birthdate so that the "earliest" is also "first" on the resulting set of records.
If the question is phrased differently, the sort must be different. "Of this list of people and their birthdates, which one is the birthdate of the first person on the list?"
To answer that question, of course, we have to know which sort order to apply to the list to get the result expected by the questioner. Do they want the "first" person alphabetically? Alphabetically by FirstName? Alphabetically by LastName? Or does "first" refer to some other criteria like "tallest", "top producer", etc?
When the question is asked explicitly, it's easy to see why and how the answer will be different. We're asking about birthdates in different contexts, and that means first doesn't always return the same answer.
Because of that ambiguity, many seasoned Access developers prefer to use Min() and Max() instead of First() and Last(). Min() and Max() are absolutes within the values themselves, regardless of context. The Minimum birthdate is the same regardless of any sort applied any of the other fields in that query. The same is true for Maximum birthdate. Access doesn't need to disambiguate the contextual reference for the question, "What is the minimum (or earliest) birthdate in this list of people and their birthdates?"
While it is possible to get accurate results when using First() and Last(), doing so depends on knowing both the values and the context in which the comparison is being made.
If you want to continue to use First() and Last() instead of Min() and Max(), continue to do so. Just keep in mind the possibility of potential subtle errors and make sure you use the appropriate ORDER BY clause.
You might do this for three reasons:
--Sometimes this process can help recover from corruption in the form.
--You can examine the raw source of the form to get a better idea of how it is designed.
--You can create external backups of objects to your accdb.
The decision is seldom easy, but several considerations can help you make a good one.
Typical Business Process
Is the process for which you're implementing a Relational Database Application common to a lot of businesses? Nearly every organization needs an accounting solution, for example. There should be several third-party accounting packages available. One of those packages might be adequate for your needs with little or no customization. Building a custom accounting package for makes little sense.
Specialized or Proprietary Business Process
Is your organization's business process highly proprietary or highly specialized? The more specialized your business is, the more likely you need a Relational Database Application designed specifically for your needs. Off-the-shelf solution providers simply can't afford to invest in a product that might ever have one or two customers.
Development Budget
How much are you willing to invest to acquire the appropriate Relational Database Application for your organization? Can you afford a high-end third-party solution that costs hundreds of thousands of dollars and takes many months or even years to customize and implement in your environment? Such solutions are very good indeed, but implementation costs on top of licensing costs put them out of reach for many organizations.
Evaluate Options Objectively
Do you need a cheap, simple solution that meets most of your needs without customization? Can you adjust your operations to work with the solution? Maybe doing one of your regular tasks in a slightly different way would be better than building a whole new Relational Database Application with custom features and functions.
Does your organization fall somewhere in the middle of all of those considerations? Perhaps you run a manufacturing plant, or a retail operation. And perhaps you can't find a third-party solution that's close enough to your requirements as is. The question comes down to whether you can acquire a customized version of that software at a cost acceptable to your organization. If so, the fact that you'll be working with an established vendor should carry some weight.
You may decide that a custom designed, custom built MS Access Relational Database Application is worth the investment in time and money.
There are many ways to implement this sort of filtering. I like to use the simplest possible method when I can identify one. Here's what I have come up with. It uses TempVars for the criteria. However, I also like to offer the option for a user to load all records in the table even though I would expect that to happen rarely. Therefore I added a conditional IF expression to the criteria to return "ALL" records, or one selected record.
Open Domains of Values are far more common than Closed Domains.
An Open Domain accepts a virtually endless range of values, as long as they fit the subject matter of that field in that table. For example, the potential Domain of Values for EmployeeFirstName is an Open Domain. Anyone, theoretically, can become an Employee (although probably not in a practical sense).
Sometimes a good example is the best way to explain concepts, so here are two Open Domains you might see in many databases: EmployeeFirstName and EmployeeLastName.
tblEmployee
--------------------------------------------------------------------------
EmployeeID EmployeeFirstName EmployeeLastName
--------------------------------------------------------------------------
1 Don Ameche
2 Alan Ladd
3 Alicia Keyes
4 Nancy Grace
Theoretically at least, any person can apply for a position and be hired, so there is no limit to the number of people's names that can be added. However, only valid names of actual people fit this domain. Car names, pet names, store names, and so on, do not fall into these domains.
Therefore, EmployeeFirstName and EmployeeLastName are Open Domains.
Knowing which values fall into an Open Domain makes it possible to apply validation rules on the the table or in the interface so that users are guided to enter only valid values.
See also Closed Domains.
After a short initial trial, the organization realized that they also need to track the date on which an employee's status changed from "Active" to "Inactive." The Access developer added a field to capture that information, a field called "InactiveDate." And just like that, the door was opened for bad data to get into this table.
How? Isn't the date ADDITIONAL data, additional data that helps clarify an important point about ex-employees?
Well, yes, that additional date data is important. However, adding the Inactive Date field to the "Inactive" yes/no field fosters ambiguity and can lead to invalid data. Users now must keep the two fields in synch, and it is easy to make a mistake.
An employee can be flagged as Inactive, but not have the InactiveDate entered. Conversely, it's possible to have an employee's InactiveDate filled in, but still be flagged as Active because these two fields both have to be updated simultaneously, and that's just not going to happen every single time.
The best solution is to remove the Yes/No field for Inactive status after the InactiveDate field has been properly updated.
Less experienced developers might wonder how you query for all Active employees, or all Inactive employees, if that status field has been dropped. It's pretty simple actually. If there IS a date in the InactiveDate field, that means the same thing as the Yes/No Inactive field being "Yes". If there IS NOT a date in the InactiveDate field, that means the same thing as the Yes/No Inactive field being "No". That means filtering for Active employees can be done by checking for the presence or absence of that date value.
To select only Active employees:
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, InactiveDate
FROM tblEmployee
WHERE tblEmployee.InactiveDate Is Null;
Or
To select only InActive employees:
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, InactiveDate
FROM tblEmployee
WHERE tblEmployee.InactiveDate Is Not Null;
Or
To select All employees:
SELECT EmployeeID, EmployeeFirstName, EmployeeLastName, InactiveDate
FROM tblEmployee;
A Closed Domain of values is one which is finite, complete and, for the most part, unchangeable. Lookup tables, for example, tend to be closed.
Sometimes a good example is the best way to explain concepts, so here's a Closed Domain you might see in many databases.
tblStates
----------------------------------------
StateAbbreviation StateName
----------------------------------------
AL Alabama
AR Arkansas
AZ Arizona
…. Etc.
WI Wisconsin
WY Wyoming
As of now, there are exactly 50 states in the United States, plus the District of Columbia. That means this table contains 51 records, no more and no less. The chance of a state changing its name, or of adding a new state to the United States, or of a state being withdrawn from the United States is very, very low.
This is a Closed Domain. See also Open Domain.
Here are a few of the more commonly seen errors of this type for field names in tables.
Did the Work Pass Inspection?
1) Question mark is a special character.
2) Spaces between words.
# of Items Sold
1) Pound sign is a special character.
2) Spaces between words.
Region/Location
1 )Forward slash is a special character.
2) Ambiguity introduced by two names for one attribute.
In the first bad example, the field name contains a question mark. Because MS Access itself uses this character as a wildcard in expressions, putting it in a field name is a not-so-subtle trap for the unwary developer.
The same is true of the octothorpe, or hash mark. Using the # sign--which Access uses as a date Delimiter--in the names of fields creates potential conflicts that negatively impact queries and other filtering and sorting tasks.
The third example is a combination of two problems. Again, using a special character in the field name raises potential conflicts. In addition, it reveals ambiguity about what that field actually contains. Is a region the same thing as a location? In some businesses they might be, but in other situations, they may not. If the developer hasn't clearly defined the content for this field, trying to finesse the problem with an ambiguous name won't help.
Two of the three examples exhibit another common problem that often flies under the radar--the use of spaces between words in the name of the field.
It may not be quite so obvious as the other special characters, but the space itself is a potential problem because word boundaries are frequently used to differentiate items. To Access, the spaces in table or field names mean something. As humans we might overlook them.
So, the examples above should be rewritten like this:
InspectionPassed
- No need to pose the question as part of the field name.
NumberofItemsSold or ItemsSold
- If it's possible to interpret this field as something other than a count, it's better to be precise that it is a number (NumberofItemsSold). Otherwise, it's better to be concise (ItemsSold). Don't use acronyms in either case.
Two fields:
SalesRegion
OfficeLocation
- If there are both regions and locations in the business operations, track each separately. If they are synonymous, pick one. Don't allow ambiguity to pollute your Relatinal Database Application.
On the other hand, people in this table CAN be uniquely identified by a combination of GroupID and MemberID:
GroupID MemberID
--------------------------
1 101
2 101
Although MemberID 101 appears for two different people, combining it with either GroupID 1 or GroupID 2 uniquely identifies these two different people with a Composite Primary Key.
What are the requirements for Primary Keys in a table?
A Primary Key is field or fields whose value(s) uniquely identify each record in the table.
A Primary Key, therefore, can't be repeated in more than one record.
Some developers prefer to use Natural Keys.
Some developers prefer to use Surrogate Keys.
Both work. Both have advantages and both have disadvantages.
A Natural Key is a attribute that applies to every record, and which is unique for each record. A Month Name field in a Lookup table of Months is an example of a Natural Key.
There are twelve months in the Gregorian Calendar. Each has its own, unique name.
That means MonthName is a Natural Key that can be designated as a Primary Key for a Relational Database Application table of Months. Such a table might be used for Lookups in a calendaring process.
However, in a table of people, Birthdate is not a good candidate to be part of the Primary Key. Many people can share a birthdate, so it won't be unique for each record. Even though, in the beginning, a table may contain only a handful of records with no duplicated Birthdates, each new record added increases the likelihood of a duplicate Birthdate. Eventually, the table is likely to break if the Birthdate field is designated as part of the Primary Key.
Another important characteristic of fields which make good Primary Keys is that they generally don't change over time. The names of months do not change. January is always January. August is always August.
Some fields seem relatively more permanent, but still can't be relied on as Primary Keys. Take for example, an employee's Name. While many of us go through life with the same name, there are a few different ways people's names can change, including marriage, adoption, pen names or other pseudonyms, and even voluntary name changes. In addition to the fact that more than one person can, and probably does, share any name, there's no guarantee that any one person's name won't change.
Name changes CAN be handled in a Relational Database Application, of course, but the work required to do that is usually not a good investment of resources.
Many Access developers prefer Surrogate Keys over Natural Keys.
Surrogate Keys do not have a "real world" existence outside the table in which they are defined. Otherwise, they share the basic characteristics of Natural Keys. The most obvious example of a Surrogate Key is the AutoNumber in Access, or the Identity Specification in SQL Server.
Because AutoNumber values do not change and are not duplicated within a table, they make good candidates for Primary Keys.
Add to that the fact that AutoNumbers and Identity Specification values are numeric and, therefore, usually more manageable in an interface, they are often the Primary Key of choice for Access-based Relational Database Applications.
Access stores dates internally in the ACE database engine as a two-part number, which can be thought of as a decimal number. For example: July 23, 2019 at 12:44PM = 43669.5305555556 The part to the left of the decimal, 43699, is the elapsed days since the starting date for Access, which is December 30, 1899 . That is rendered as 12/30/1989 (mm/dd/yyyy) in US notation, or 30/12/1989 (dd/mm/yyyy) in the rest of the world. The part to the right of the decimal, 5305555556, is the elapsed time since midnight of that date. In other words .5305555556 of a day is approximately 12 hours and 44 minutes. To format dates for DISPLAY to humans, you can apply different Format(). For example: DisplayDate: Format(#2019/07/23#, "mm/dd/yyyy") results in a Display showing 07/23/2019 DisplayDate: Format(#2019/07/23#, "dd/mm/yyyy") results in a Display showing 23/07/2019
July 23, 2019 at 12:44PM = 43669.5305555556
The part to the left of the decimal, 43699, is the elapsed days since the starting date for Access, which is December 30, 1899 (12/30/1899 in US notation, or 30/12/1899 in the rest of the world).
The part to the right of the decimal, 5305555556, is the elapsed time since midnight of that date. In other words .5305555556 of a day is approximately 12 hours and 44 minutes.
To format dates for DISPLAY to humans, you can apply different Format() expressions.
For example:
DisplayDate: Format(#2019/07/23#, "mm/dd/yyyy") results in a Display showing 07/23/2019
DisplayDate: Format(#2019/07/23#, "dd/mm/yyyy") results in a Display showing 23/07/2019
CAUTION:
When you use Format() the underlying value is converted from a Date datatype to a Text, or String, datatype. That value can no longer be used to sort or filter dates reliably. It will sort as a text string.
Remember: Only apply formatting to dates that you want to show on a report or in a form. Do not format dates that you need to use for data management tasks. Sometimes this means you need to include two fields in a query for the same date field -- one for the Display format and one for sorting or filtering on the underlying date value.
Show the formatted version to users. Sort or filter on the unformatted version.