Daily Access Tips

This Microsoft Access security log on solution will work with all versions of Access

Four steps to create this solution:

Step One
Create a new table called “tblUsers”
Add these fields:
Field Name                   Data Type

UserName                    Text
Password                     Text
Administrator_Rights    Yes/No

Make this a hidden table and do not show hidden table in your Access program (See the video below on how to do this.)
http://www.youtube.com/watch?v=kJjQaAWHmG8

Step Two

Create a new module and paste this code into it:

Public user As Variant
Public pw As Variant

Public Function getuser()
getuser = user
End Function

Public Function getpw()
getpw = pw
End Function

Step Three
Create a new query based on this SQL statement:

SELECT tblUsers.UserName, tblUsers.Password, tblUsers.Administrator_Rights
FROM tblUsers
WHERE (((tblUsers.UserName)=getUser()) AND ((tblUsers.Password)=getPW()));

The above is done by going to the SQL View of the query and pasting in the above SQL statement.

Name the query “qryRS”

Step Four

Create a form “frmLogOn”

Add a text box control and call it “txtUserName”
Place this code in it’s After Update event:
user = txtUserName

Add a text box control and call it “txtPassword”
Place this code in it’s After Update event:
pw = txtPassword

Add a button and place this code in it’s Click event:

On Error GoTo errline
If IsNull(Me.txtUserName) Then
MsgBox “Please enter a User Name.”, vbInformation, “Missing User Name”
Exit Sub
End If

If IsNull(Me.txtPassword) Then
MsgBox “Please enter a Password for the user name ” & Me.txtUserName & “.”, vbInformation, “Missing User Name”
Exit Sub
End If

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(“qryRS”)
With rs
If .EOF And .BOF Then
MsgBox “Your log on information was incorrect.  Try again.”, vbCritical, “Failed Log On”
GoTo exitline
Else
If !Administrator_Rights = False Then
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
GoTo exitline
End If
End If
End With

If chbxEditTable = -1 Then
DoCmd.OpenTable “tblUsers”, acViewNormal
DoCmd.Close acForm, “frmLogOn”
Else
DoCmd.OpenForm “frmMenu”
DoCmd.Close acForm, “frmLogOn”
End If

exitline:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Exit Sub
errline:

Select Case Err.Number
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”

GoTo exitline
End Select

A demononstration of this solution is here:
http://www.youtube.com/watch?v=0RwU1roBJ6I

Hide Table Video:
http://www.youtube.com/watch?v=kJjQaAWHmG8

from youtube
Log On Demo:
http://www.youtube.com/watch?v=0RwU1roBJ6I

from youtube

Bonus article from Smart Access

Even some experienced Access developers shy away from writing SQL directly. That’s a shame, because, unless you’re willing to write SQL, you can’t use subqueries, which are a powerful tool for solving some especially thorny data retrieval problems. In this article, Mike Gunderloy introduces subqueries and shows how you can use them in Access.

http://www.vb123.com/smart/go.asp?rfdby=myap

Article Summary of DATA MODELING FOR THE ACCESS NEWCOMER, PART 2 by Glenn Lloyd:

Last month, Glenn Lloyd started outlining his approach to data modeling. This month, he finishes his discussion of the rules that ensure that your database will actually work after you’ve built it.

He emphasizes “that good data modeling isn’t a ‘nice-to-have feature.’ It’s essential to success.”

Get your free full article reprint here under “Latest Link”:Full Article

Why not get hundreds of great Microsoft Access articles written by the leaders in this field.   All past issues of “Smart Access”, fully indexed for searching, are now available to you.

Since you are a Daily Access Tips / My Access Program subscriber, don’t forget to take your Coupon Code.

Enjoy!

Bob Heifler

Time to cover some basics.  Grab a drink and snack while you receive your first gold coin from the treasure chest of ‘Smart Access’ republished articles:

Article Summary of DATA MODELING FOR THE ACCESS NEWCOMER, PART 1 by Glenn Lloyd:

Thorough, thoughtful, and accurate data modeling should be the starting point of detailed database design.  But a surprising number of developers have little or no understanding of data modeling and shy away from what sounds like a non-profitable and time-consuming task.

Glenn Lloyd looks at the typical design pitfalls that trap Access beginners and shows the basic techniques that ensure success.

He discusses database design, where to start, relational tables  in relationship to application requirements, types of relationships, data modeling to represent the real world entities, and Normalization rules.

Get your free full article reprint here:  http://www.vb123.com/kb/200511_gldatamodeling.htm

Don’t forget to take your discount coupon (On the right side of screen).

Place your comments in a reply below:

Bob Heifler

First let’s start with the definition of a split MS Access Database:

A Microsoft Database that is split into a front-end database that contains the application (queries, forms, reports, macros, and modules) that are linked to MS Access tables stored in a back end shared database file containing only the data tables.

You will split MS Access Database when moving an application to a multi-user environment rather that have everyone using a single file on a shared network drive.  Doing otherwise puts your data at risk of corruption.  However, as you will find out in the next article, even a single user database will benefit from being split.

When you split a database, creating a front end and a back end, the back end Microsoft Database file is stored in a shared folder on a network drive and you link Microsoft Access data to MS Access front-end file. The front-end database file is distributed to each user’s desktop.  You would link Microsoft Access front-end to MS Access back-end, just prior to distribution. Using this design, each user has a copy of Microsoft Database program installed on their workstation machine along with their front-end application database file. Each user interacts with the data by using a local copy of the front-end Microsoft Database file. This reduces network traffic since the front-end does not need to be sent over the network path for each use, and allows the front-end database to contain settings or temporary data specific to the individual user. This split database design also allows development of the application, the front-end, to be totally independent of the data.  This allows for front-end to be easily replaced without affecting the data in the back end database. You will at that time link Microsoft Access front end file to MS Access back end file using the Linked Table Manager or some code to do it. The Microsoft Database program has two built-in utilities (Database Splitter and Linked Table Manager) to aid in your linking to MS Access data.  Look-up tables that rarely change their data are also possible items for the front-end. However, if you have tables with fields that look-up to other tables, those tables should be in the same back end database. Otherwise, if you open that source database and that table, it’s look-ups will not appear and this can cause data entry and data integrity problems.

The next article in this series will go over the risks when you do not split MS Access Database or link Microsoft Access front-end file to MS Access back end file.  The pros and cons of using a wizard to do the split for you will be discussed along with how to manually accomplish this procedure when the wizard would not do the job adequately.  At the end of the series we will discover code written by an Access MVP Microsoft Most Valuable Professional by the name of Dev Ashish. This code ensures that the data linking is working each time the program starts.  You will also be directed to a Microsoft KB Knowledge Base Article to further illustrate a manual method of splitting and linking your database.

In the next article you find out why it’s vital to Link Microsoft Access Application to MS Access back-end data file even if you only have one person using the database.

Continue to part 2: http://www.dailyaccesstips.com/why-link-microsoft-access-application-to-ms-access-backend-data-file-part-2-of-4/

Here are the main benefits of a split database. (See Split Database definition in Part 1)

Data Integrity:

Sharing a single database file over the network, rather than split MS Access Database, has been found by Microsoft to be the number one cause of database corruption.

A common cause of corruption is a dropped network connection when writing to MS Access back-end file.  When multiple users open a single Access database file on the file server, the chance that a network connection being dropped when one of them is writing data file to MS Access back-end is much higher than when each of these users opens his own copy of the front-end Access database file.

If a user, using an MS Access link to data, encounters a problem and the database closes unexpectedly, any database file corruption is usually limited to the copy of the front-end database that the user had open.  Because the user will link Microsoft Access application file to MS Access back-end file database by using linked tables, the back-end database file is much less likely to lead to MS Access data corruption.

Improved Performance:

The performance of the database will greatly improve when you link Microsoft Access application file to MS Access back-end file containing only Access data tables. The reason is that only the data is sent across the network.

In a shared database that is not split, the database objects themselves (tables, queries, forms, reports, macros and modules) must be sent across the network, not just the data. Because only the data is sent across the network, database record transactions such as record edits are completed faster. This reduces network traffic and that results in greater network speed.

Loading the entire application (forms, controls, code, etc) locally and using only an MS Access link to your data will make your application interface run faster because the form or report is sitting on the local workstation hard drive saving time retrieving it across the network.

Maintainability:

Because each user works with a local copy of the front-end database linked to MS Access back-end, each user can develop additional queries, forms, reports, and other database objects without affecting other users. They just need to ensure these changes get added to the next distributed version of the front-end.

Another advantage, individual users can customize some of the settings in Access without affecting other users’ settings. Similarly, distributing a new version of the front-end database will not disrupt access to the data that is stored in the back-end database. So it’s easy to maintain and update the program by just replacing the application file and link Microsoft Access front-end to MS Access back-end. Since the data is in a separate file, no data will be overwritten.

Greater Security:

Since you link Microsoft Access application file to MS Access back-end file located on a central server, different users may be assigned with different levels of permission. You may assign some read only permission to your database while assigning others read and write permissions. The server can be used to grant various permission levels for the MS Access link to the network folder. Your network or system administrator can set these for you.

Before you start splitting your database, you need to choose the best method.  Read the next article in this series to find out what’s best for you. http://www.dailyaccesstips.com/manually-link-microsoft-access-to-ms-access-data-part-3-of-4/

I am showing the manual methods first before the automated “Database Splitter” wizard method because it offers several advantages over the split MS Access Database wizard to Link Microsoft Access front-end to MS Access back-end.

The advantages of the manual approach are:

• It will result in smaller sizes for both front-end and back-end databases.
• It will not leave your back-end unsecured. A secure database split with the wizard will leave the back-end database file unsecured.
• Table relationships will not be lost.
  

If you want to link Microsoft Access front-end to MS Access back-end there are two processes you can use. You can use either of these methods on any version of Access program.

Here is the first manual method:

1. Copy the database.
2. Give it a name similar name but add the word program or ‘prg’ to the name. This will be your front-end database program file.
3. Delete the tables from this front-end. Do not delete any “lookup tables”, if their data is not going to change. Example of a good local table: A list of State abbreviations.
4. Rename to original file by adding the work data to it. This is your back-end file.
5. Delete all the queries, forms, reports, macros and modules from the back-end file.
6. In the front-end, use the Linked Table Manager to link to all the tables in the back-end or to get your MS Access link you may manually link to each table.
7. Compact both database files.

This first method will not lose any table relationships should you have any.

An alternative method is:

1. Create a new empty database and name it with the word data in the name. This will be your back-end file.
2. Import all of the tables into this back-end file.
3. Create a second new empty database and name it with the word program or ‘prg’ in the name. This will be your front-end file.
4. Import all of the queries, forms, reports, macros and modules into the front-end file.
5. Use the Linked Tables manager to link to all the tables in the back-end file or manually link to each table to get your MS Access link.
   

There is a Microsoft KB Knowledge Base Article on how to do this at: http://support.microsoft.com/kb/304932

The Microsoft Developer Network MSDN also has an article on this subject: http://msdn.microsoft.com/en-us/library/aa141493(office.10).aspx

As a side note, Microsoft has always allowed an upgraded newer version of your front-end to Link Microsoft Access to MS Access Data back-end that are in earlier versions. For example, you might upgrade your Access 2002 version to Access 2007 for some of your users getting a new computer, however you will not have to upgrade the back-end data tables to 2007.

Continue to the next article in this series: http://www.dailyaccesstips.com/use-wizard-to-link-microsoft-access-to-ms-access-data-part-4-of-4/

Each version of Microsoft Access has a built-in wizard to link Microsoft Access front-end file to MS Access back-end file in order to get your MS Access link between the two files.

You will find the wizard on the menu bar or ribbon at the top of the Access window. Here are the menu paths to get to your wizard.

Access 95-97 Tools |
Add-Ins | Database Splitter

Access 2000 – 2003 Tools |
Database Utilities | Database Splitter

Access 2007 Database Tools
| Move Data | Access Back-End

Here is how to use the Wizard in Access 2007:

Note: The wizard doesn’t copy the table relationships, if you have set any up.

1. On your computer, make a copy of the database that you want to split and place it on your local hard drive.
2. Open the copy that is on your local hard disk drive.
3. On the Database Tools tab, in the Move Data group, choose Access Database for the Database Splitter Wizard to start.
4. In the Create Back-end Database dialog box, give the back-end a name. I always place the word ‘data’ in the name.
5. To get your MS Access link for the correct back-end location, in the File Name box, you enter the path to the network in front of the file name. If the network location for the
   back-end database was \\server_fruit\share_folder_apple\ and the file name for the back-end database is MyDB_Data.accdb, you would enter \\server_fruit share_folder_apple\ MyDB_Data.accdb in the File Name box.

Note: It’s better to use the double slash UNC (UNC: Acronym for Universal Naming Convention or Uniform Naming Convention) rather than a mapped drive letter that might get unmapped.

After the database splitting procedure is finished, you can re-link the linked tables using Linked Table Manger to link Microsoft Access front-end file to MS Access back-end file.
Go to Database Tools from the Access Ribbon and select Linked Table Manager. In earlier Access versions, it’s under Tools, Database Utilities. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location. Select the table names that you want to update and click on Ok button. If you ever want to link to a database located in another drive or folder, check the box ‘Always prompt’ for new location.

In Access 95-2003 do the following:

1. Open the database to be split on a local drive.
2. From the main menu, choose Tools then Database Utilities (Ad-Ins for Access 95 or 97) then Database Splitter.
3. The default file name for the back-end will show as DatabaseName_be.mdb (for Access 2003). The letters ‘be’ stands for back-end. I choose to use the word ‘data’ instead of the letters ‘be’.
4. Browse to the server and folder where the back-end file will be stored.
5. Now click on the Split button.
6. A message will appear when the split is complete.
7. Test the front-end database to confirm that the proper links have been created.
8. Now that you have your MS Access link, distribute the front-end database to your users, so that they can start to use the database.

Access MVP, Microsoft Most Valuable Professional, Dev Ashish has got some code that looks during program start up at each table in the database, and tries to find the data source for that table if the Connect property is populated. If the database specified for the linked table is not present, the code brings up a dialog window so that the user can select an alternative source. The code will then proceed to link Microsoft Access front-end file to MS Access back-end file.

Find Dev Ashish’s code here: http://www.mvps.org/access/tables/tbl0009.htm

Dev Ashish also references some code, for when one Access database has linked tables from different data sources (ODBC, Excel, FoxPro etc), that can create your MS Access link to all linked sources, regardless of their source type.

Here’s the code: http://www.mvps.org/access/tables/tbl0012.htm

Well that wraps up this series of articles.  Your comments and acknowledgments are always welcome.

Bob Heifler

Here is a common situation:  Bill the salesperson / order taker is on the phone with a new customer and discovers that Ajax Wire Co., the new customer, is not yet available on the “Select a Customer” drop down pick list on the computer screen.  Bill must now keep this new customer on hold while he leaves one computer screen to go add the new customer before continuing.  Bill explains that the computer is slow as an excuse when the customer gets a bit impatient. Failure to automate this costs your company by presenting a poor first impression and possibly a lost sale now or a non-returning customer.

This can be easily avoided if you know what to ask your computer programmer for…

Here is what your computer automation should be doing:

1. The program must first detect automatically when a user is trying to add an item that is not on the selection list.
2. Find out from the user if it should be added and gaining the user’s permission.
3. Add this to the list by opening a maintenance form automatically and add the first bit of data that the user has already type in the drop down list.
4. Save the data about this newly added item and close the maintenance form.
5. Return the user to the original data entry form that the user was on.
6. Refresh the drop down list so the newly added item now shows on the list.
7. Select the newly added item for the user.

Why do you need the drop down pick list to restrict data entry to only acceptable values?

• A drop down list (a type of form control on a Microsoft Access form) can be used to restrict data entry to an existing list of items thus preventing wrong type of data from being entered.
• It prevents in-consistency of data (Example: Spelling variations – wrongly spelled versions of same item).
• Allows for additional data to be stored out side of the current Access form and the data that feeds it.
• Speeds data entry by presenting a list of previous items.
• Allows for relational links between data tables to be maintained, if a unique value in the lookup table* is stored.

*Microsoft Access Definition: A lookup table is an Access table that serves as a data storage area for data that will be used more than once and will be stored in an other Access table.

Here is a video demonstrating this:

The code below may used by your programmer or if he needs an example working demonstration file it’s found at:

Microsoft Access Show Me Demontration Files

Select example file #48

The programming code to be place in a public (Application Wide) code module:
Option Compare Database
‘Declarations
Public Not_In_List_In_Progress As Boolean
Public frmNot_In_List_Form As Form
Public ctlList_Control As Control
Public frmLookup_Form As Variant
Public ctllookup_Control As Variant
Public ctlLookupKey_Control As Variant

Code used in the NotInList Event of your combo box:
Public Sub Not_In_List_Starting_Handler(Not_In_List_Form As Form, List_Control As Control, Lookup_Form As Variant, Lookup_Control As Variant, vNewData As Variant, vLookupKey_Control As Variant)
On Error GoTo errline
Dim MsgBoxAnswer As Variant
‘Set public variables
Set frmNot_In_List_Form = Not_In_List_Form
Set ctlList_Control = List_Control
frmLookup_Form = Lookup_Form
ctllookup_Control = Lookup_Control
ctlLookupKey_Control = vLookupKey_Control
‘Request permission
MsgBoxAnswer = MsgBox(“Do you want to add this new item to this list?”, vbYesNo, “Add New customer?”)

If MsgBoxAnswer = vbNo Then
List_Control = Null ‘Make the list control empty for the time being.
Not_In_List_Form.SetFocus ‘Move cursor to the form.
List_Control.SetFocus ‘Move cursor to the list control.
GoTo exitline ‘Exit this Subroutine code.
Else ‘Permmision granted add new list item, so open lookup maintenance form to fill out a new list entry record.
DoCmd.OpenForm (Lookup_Form) ‘Open the lookup maintenance form.
Not_In_List_In_Progress = True ‘Set the Not In List flag for this coded solution.
DoCmd.GoToRecord , , acNewRec ‘Open new record to add list item.
DoCmd.GoToControl (Lookup_Control) ‘Move cursor to the control to receive the value the user tried to enter on data entry form.
Screen.ActiveControl = vNewData ‘Write the value that the user tried to enter in the list, in the correct control on the now open lookup maintenance form.
List_Control = Null ‘Make the list control empty for the time being.
End If

exitline:
Exit Sub
errline:
Select Case Err.Number
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
End Select
End Sub

Code used for the OnClick event of the maintenance for used in for the look up table:

Public Sub Not_In_List_Returning_Handler()
On Error GoTo errline
If Not_In_List_In_Progress = True And CurrentProject.AllForms(frmNot_In_List_Form.Name).IsLoaded = True Then
ctlList_Control.Requery ‘Refresh the list, so item just entered is now on list
DoCmd.GoToControl (ctlLookupKey_Control) ‘Go to the control containing the key value for the list control to store
ctlList_Control = Screen.ActiveControl.Value ‘Select the key value in the list.
Not_In_List_In_Progress = False ‘Turn off the Not In List flag for this code solution.
DoCmd.Close acForm, frmLookup_Form ‘Close the lookup form
frmNot_In_List_Form.SetFocus ‘Put cursor back in the original data entry form
ctlList_Control.SetFocus ‘Put cursor back in the original list control
Else
DoCmd.Close acForm, Screen.ActiveForm.Name
End If
exitline:
Exit Sub
errline:
Select Case Err.Number
Case 2467 ‘Form variable frmNot_In_List_Form references a form that is not currently open.
‘ This means that the user closed the form that had the not on list trigger before pressing the ‘Save/Close’ button
‘ on the lookup form that is now active.
Not_In_List_In_Progress = False
DoCmd.Close acForm, Screen.ActiveForm.Name
Exit Sub
Case 91
DoCmd.Close acForm, Screen.ActiveForm.Name
Exit Sub
Case Else
MsgBox “There was an error in the program.  Please notify database administrator of the following error: Error Number: ” & Err.Number & “  ” & Err.Description, vbCritical, “Please write this error down and note what you were doing at the time.”
GoTo exitline
Resume Next
End Select
End Sub

Find example file at:

Choose Example file #48

Bob Heifler
MyAccessProgram.com

Contacthttp://myaccessprogram.com/Contact.html

Example fileshttp://myaccessprogram.com/Download.html

http://myaccessprogram.com/Download.html