Create Or Change Security Password

Create or Change Security Account Password

A security account password is created to ensure that no other user can log on using that Username. By default, Microsoft Access assigns a blank password to the Admin user account and any new user accounts you create in your workgroup.

Start Microsoft Access by using the workgroup the user account is stored in, and log on using the name of the account for which you want to create or change the password.

You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.

1. Open a database
2. On the Tools menu, point to Security, and then click User and Group Accounts.
3. On the Change Logon Password tab, leave the Old Password box blank if a password hasn't been defined previously for this account. Otherwise, type the current password in the Old Password box.
4. Type the new password in the New Password box.
5. A password can range from 1 to 20 characters and can include any characters except the ASCII character 0 (Null). Passwords are case-sensitive.
6. Retype the password in the Verify box, and then click OK.

Caution: Be sure to record the exact account name and Personal Identifier (PID), including the correct use of uppercase and lowercase letters, and store them securely. If you ever need to re-create a deleted account or replicate it in a different workgroup, you must provide the exact same name and PID. If these details are lost or forgotten, they cannot be recovered, and access to the database may be permanently lost.

Click Next to see how to clear a security account password.

Goto Main

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Delete MS Access Security Group Account

Delete a security group account

To complete this procedure, you must be logged in as a member of the Admins group.

Note: The Admins and Users group accounts can't be deleted.

1. Start Microsoft Access by using the workgroup that contains the account you want to delete. You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.
2. Open a database.
3. On the Tools menu, point to Security, and then click User and Group Accounts.
4. On the Groups tab, enter the group you want to delete in the Name box, and then click Delete.
5. Click Yes to delete the group account.

Repeat steps 4 and 5 if you want to delete additional group accounts.

Click Next to see how to create or change a security account password.

Delete MS-Access Security User Account.

Goto Main

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Delete MS Access Security User Account

Delete a Microsoft Access Security User Account

To complete this procedure, you must be logged in as a member of the Admins group.

Note: The Admin user account can't be deleted.

1. Open a database.
2. On the Tools menu, point to Security, and then click User and Group Accounts.
3. On the Users tab, enter a user in the Name box, and then click Delete.
4. Click Yes to delete the user account.

Repeat steps 3 and 4 if you want to delete additional user accounts, and then click OK when you have finished.

Click Next to see how to delete a Security Group Account.

Goto Main

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Remove Users From Security Groups

Remove Users from Microsoft Access Security Groups

To complete this procedure, you must be logged in as a member of the Admins group.

Notes: You can't remove users from the default Users group. Microsoft Access automatically adds all users to the Users group. To remove any user account from the Users group, you must delete the account.

There must be at least one user in the predefined Admins group at all times.

1. Start Microsoft Access by using the workgroup containing the user and group accounts.
2. You can find out which workgroup is current or change workgroups by using the Workgroup Administrator.
3. Open a database.
4. On the Tools menu, point to Security, and then click User and Group Accounts.
5. On the Users tab, enter the user you want to remove in the Name box.
6. In the Member Of box, click the group you want to remove the user from, and then click Remove.
7. Repeat step 5 to remove this user from any other groups. Repeat steps 4 and 5 to remove other users from groups.

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Add Users To Security Groups

Add Users to Security Groups

To complete this procedure, you must be logged in as a member of the Admins group.

1. Open a database.
2. On the Tools menu, point to Security and then click on User and Group Accounts.
3. On the Users tab, enter in the Name box the user name you want to add to a group.
4. In the Available Groups box, click the group you want to add the user to, and then click Add. The selected group is displayed in the Members list.

Repeat step 4 if you want to add this user to any other groups.

Repeat steps 3 and 4 to add other users to groups.

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

MS Access Security Group Account

Create a security group account

As part of securing a database, you can create group accounts in your Microsoft Access Workgroup that you use to assign a common set of permissions to multiple users.

To complete this procedure, you must be logged in as a member of the Admins group. Start Microsoft Access by using the workgroup in which you want to use the account.

Important: The accounts you create for users must be stored in the workgroup information file that those users will use. If you are using a different workgroup to create the database, change your workgroup before creating the accounts. You can change workgroups by using the Workgroup Administrator.

1. Open a database.
2. On the Tools menu, point to Security, and then click User and Group Accounts.
3. On the Groups tab, click New.
4. In the New User/Group dialog box, type the name of the new account and a personal ID (PID).

   Group names can range from 1 to 20 characters, and can include alphabetic characters, accented characters, numbers, spaces, and symbols, with the following exceptions:

   • The characters ' \ [ ] " | <> + = ; , . ? *
   • Leading spaces
   • Control characters (ASCII 10 through ASCII 31)

   Caution: Be sure to record the exact account name and Personal Identifier (PID), including the correct use of uppercase and lowercase letters, and store them securely. If you ever need to re-create a deleted account or replicate it in a different workgroup, you must provide the exact same name and PID. If these details are lost or forgotten, they cannot be recovered, and access to the database may be permanently lost.

5. Click OK to create the new group account.

Note: A user account name cannot be the same as an existing group account name.

To create more Group Accounts, repeat steps 3 to 5 above.

Click Next to see how to add users to security groups.

Go to Main

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Create MS Access User Account

Create a Security User Account.

Right-click to open a Large Image in New Tab/Window

1. Start Microsoft Access and Open a Database. On the Tools menu, point to Security, and then click User and Group Accounts.
2. Click the New button on the Users tab of the User and Group Accounts dialog box and enter a User-Name and a unique Personal ID (PID) in the New User/Group dialog box, and then click OK.

   The user name can range from 1 to 20 characters, and can include alphabetic characters, accented characters, numbers, spaces, and symbols, with the following exceptions:

   • The characters "\ [ ] : | < > + = ; , . ? *
   • Leading spaces
   • Control characters (ASCII 10 through ASCII 31)

   Caution: Be sure to note down the exact account name and PID, including whether letters are uppercase or lowercase, and keep them in a secure place.

   If you ever need to recreate an account that was deleted or created in a different workgroup, you must provide the exact same username and Personal Identifier (PID). Otherwise, the recreated account will not retain the original permissions or the object Ownership.

   If you forget or lose these entries, you can't recover them.

   Notes: The PID entered is not a password.

   Microsoft Access uses the PID and the user name as seeds for an encryption algorithm to generate a secure identifier for the user account. The new name will appear in the user name box.

3. Click on the Admins group name in the "Available Group" List and then click on the Add>> button to join the Admins Group.

   Notes: The procedure for creating user accounts for others is the same. However, you should assign each user to a specific group—such as Data Entry, Supervisors, Managers, or any other group you define—based on the access rights they need. Grouping users this way helps manage permissions effectively when sharing your database across a workgroup.

4. Now that you have created your own Administrator account, exit Microsoft Access and start again.
5. This time, log on with your new Administrator account.

   You have not yet set a password for your new Administrator account, so leave the password box empty on the login dialog box.

6. Select the Tools menu, point to Security, and select User and Group Account. Select Change Log on the Password tab. Type a new password in the new password box.
7. Verify the password. Leave the old password box empty.

As a security measure, we have removed the default Admin user from the Admins group. Equally important is revoking all permissions assigned to the Users group. To do this, go through each object type—Database, Tables, Queries, Forms, Reports, and Macros—select the relevant objects in the Object Name list, and deselect all permission checkboxes.

This step is crucial because every user is automatically a 'Users' Group Member. Unlike the Admins group, the Users group itself cannot be deleted. Even if you remove all object-level permissions for a specific user account, the user may still inherit permissions from the Users group. Therefore, group-level and user-level permission settings will have no effect unless the Users group permissions are properly removed.

Create group accounts and assign object-level permissions at the group level, such as Data Entry Group, Supervisor Group, Manager Group, and others. This approach eliminates the need to assign permissions individually for each new user. Once the permissions are defined at the group level, you only need to add the user to the appropriate group(s). The user will automatically inherit all the permissions assigned to that group.

• Set Open/Run only permissions to Forms, Reports, and Macros for User Groups.
• You can assign ownership to tables that are regularly overwritten during data processing tasks (such as when running Make-Table queries), so they can be safely recreated without causing access-right issues.

Notes:

The workgroup information file contains only the user name, Workgroup Names, Personal IDs, and passwords.

The permissions setting is stored, along with the database.

When creating a new database, ensure to remove all permissions from the Users group account before assigning permissions to individual users or user groups.

Click Next to see how to create a security User Group Account.

Goto Main

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. RunPermissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Microsoft Access Security Implementation

Securing Your Microsoft Access Database.

Microsoft Access provides flexible options for securing your database objects. By default, security features are hidden from both designers and users. However, you can apply security settings as needed—for example, restricting access to specific objects to prevent unauthorized modifications. For higher levels of protection, Access can be configured to limit access paths to your data, allowing only controlled methods for retrieving information from tables. In networked environments, implementing a well-structured security system not only protects your data but also improves application maintainability by minimizing potential security risks.

Security Concepts.

To understand Access security, you'll need to grasp four basic security concepts: users and groups have permissions on objects.

• In Microsoft Access, a user represents an individual who interacts with the application. Each user is identified by a username, password, and a unique Personal Identifier (PID). To access a secured Access application, users must enter their username and password. Without valid credentials, they cannot open or interact with any database objects.

• In Microsoft Access, a group is a collection of users. Groups are typically used to represent organizational roles (e.g., Development, Accounting) or security levels (e.g., High, Low). Instead of assigning permissions to each user, you can assign them to groups. Then, by simply adding users to the appropriate groups, you make the security system easier to manage and maintain.

• Access permission is the right to perform a single operation on an object. For example, a user can be granted read data permission on a table, allowing the user to retrieve data from that table. Both users and groups can be assigned permissions.

• An access security object is any one of the main database Container objects (Table, Query, Form, Report, Macro, or Module) or a database itself.

Because both users and groups can be assigned permissions in Microsoft Access, determining a user's actual access level may require checking multiple sources. A user's effective permissions are the least restrictive combination of:

• Explicit permissions — those assigned directly to the user.

• Implicit permissions — those inherited from any groups the user is a member of.

For example, suppose Mary has not been granted direct (explicit) permission to open the Accounting form. However, she is a member of the Supervisors group, which does have permission to open that form. In this case, Mary will still be able to open the form because her group membership grants her that access. Group permissions (implicit) are combined with user permissions (explicit), and the most permissive setting takes effect.

Creating a Microsoft Access Workgroup Information File

When you install Microsoft Access, the setup program automatically creates a default workgroup information file named System.mdw, using the name and organization details you provide. Since this information is often easy to guess, unauthorized users could potentially recreate this file and assume administrative privileges (by joining the Admins group) within that workgroup. To secure your database, it is recommended to create a new workgroup information file and assign a Workgroup ID (WID)—a unique, secret value. Only those who know the WID will be able to recreate the file and access the corresponding administrative rights.

The procedures outlined in this document assume that Microsoft Access 2000 is installed on your computer. While the same steps generally apply to other versions of Microsoft Access, the location of the Workgroup Administrator utility (Wrkgadm.exe) and the default workgroup information file (System.mdw) may vary depending on the version you are using.

1. Exit Microsoft Access (Access 2000 or earlier versions)

2. To start the Workgroup Administrator, open the language folder (C:\Program Files\Microsoft Office\Office\1033 is for US English), then double-click Wrkgadm.exe. The Workgroup Administrator image is given below:

   

   Right-click to open Large Image in a New Tab/Window

3. Select the Create option to create a new Workgroup Information File.

4. Select the Join... Option to join a Workgroup Information File that you have created earlier.

Alternatively, you can use the Microsoft Access Workgroup Administrator shortcut in the \Program Files\Microsoft Office\Office folder.

To run Workgroup Administrator in Microsoft Office 2003:

1. Start Microsoft Access 2003

2. Select the Tools menu, point the mouse at Security, and click the Workgroup Administrator option.

3. In the Workgroup Administrator dialog box, click Create.

   

   Right-click to open Large Image in a New Tab/Window

4. In the Workgroup Owner Information dialog box, type your name and organization, and then type any combination of up to 20 characters for the workgroup ID.

   Important: Be sure to write down the exact entries for the Name, Organization, and Workgroup ID (WID)—including the correct use of uppercase and lowercase letters—and store them in a secure location. If you ever need to recreate the workgroup information file (for example, due to corruption or accidental deletion), you must enter the exact same information. If you forget or lose any of these details, they cannot be recovered, and you may permanently lose access to your secured databases.

5. Type a new name for the new workgroup information file, and then click OK.

By default, the workgroup information file is saved in the language folder (C:\Program Files\Microsoft Office\Office\1033, for U.S. English). To save it in a different location, type a new path, or click Browse to select the new path. The new workgroup information file is used the next time you start Microsoft Access. Any user and group accounts or passwords that you create are saved in the new workgroup information file.

To have others join the workgroup defined by your new Workgroup Information File, copy the file to a shared folder and then have each user run the Workgroup Administrator (wrkgadm.exe) as explained above on their own PC to join the common workgroup information file.

To join a Microsoft Access Workgroup using Workgroup Administrator.

1. Follow steps 1 & 2 as explained above, depending on the Access Version (Access 2000 and earlier or Access 2003).
2. In the Workgroup Administrator dialog box, click Join.
li>Type the path and name of the Workgroup Information File that defines the Microsoft Access workgroup you want to join and click OK, or click the Browse button to find the Workgroup Information File on disk, click Open, then click OK to close the dialog control.

Next time you start Microsoft Access, it uses the User and Group Accounts and Passwords stored in the workgroup information file for the workgroup you have joined.

Log on to a Microsoft Access workgroup

Activate the Logon dialog box

Until you activate the login procedure for a workgroup, Microsoft Access automatically logs in all users at startup using the predefined Admin account, and the login dialog box is not displayed.

To activate the logon dialog box, you must set a password for the default Admin user account. This prompts users to enter their username and password to access and work with your secured databases.

1. Start Microsoft Access.
2. On the Tools menu, point to Security, and then click User and Group Accounts.
3. Click the Users tab, and make sure that the predefined Admin user account is highlighted in the Name box.
4. Click the Change Logon Password tab, click the New Password box, and type the new password. Don't type anything in the Old Password box.

   To maintain the security of your password, Microsoft Access displays asterisks (*) as you type. Passwords can be from 1 to 20 characters and can include any characters except the ASCII character 0 (null). Passwords are case-sensitive.

5. Verify the password by typing it again in the Verify box, and then click OK.

The Logon dialog box is displayed the next time any member of the workgroup that you joined starts Microsoft Access and opens a database. If no user accounts are currently defined for that workgroup, the Admin user is the only valid account.

Note: When you secure a database, you create User Accounts in a Microsoft Access workgroup, and then assign permissions for Databases, Tables, Queries, Forms, Reports, and Macros to those Accounts and to any Group Accounts to which they belong. Users log on to Microsoft Access by typing a Username and password in the Logon Dialog Box. When Users log on to Microsoft Access by using their Accounts, they have only the permissions associated with those accounts.

Keep the following points in mind while implementing MS-Access Security:

1. Members of the Admins group have full permissions on all database objects and complete authority to grant or revoke permissions for other users or groups.

2. The Owner of the Database (the User who created the database) has full authority (like members of the Admins Group) to give permissions or ownership of objects to other Users or Groups.

3. Create an Administrator account for yourself. Click to show how.

4. Remove the default user Admin from the Admins Group.

   Caution: Before proceeding with Step 4, ensure that you create a new administrator account (as a member of the Admins group) for yourself. Otherwise, you risk locking yourself out of the workgroup information file.

5. Remove all permissions on all objects for the Users group.

By default, all users are members of the Users group. Even if you assign security permissions at the individual user or custom group level, those settings will have no effect if the Users group retains full permissions. This is because users automatically inherit the permissions of the Users group.

MS-ACCESS Security Links.

1. Create a security user account
2. Create a security group account
3. Add users to security groups
4. Remove users from security groups
5. Delete a security user account
6. Delete a security group account
7. Create or change a security account password
8. Clear a security account password
9. Assign or remove permissions
10. Assign default permissions for new tables, queries, forms, reports, and macros.
11. View or transfer ownership of Objects
12. Transfer ownership of an entire database to another administrator
13. Permit others to view or run my query but not change data or query design.
14. Change default permissions for all new queries.
15. Run Permissions Property
16. Convert  Microsoft Access 95 or 97 secured databases.
17. Convert a workgroup information file from a previous version of Microsoft Access.
18. Share a previous-version secured database across several versions of Microsoft Access

Read More

Reminder Ticker on Form

Continuous scrolling Reminder Text.

Right-click to open Large Image in a New Tab/Window

This is an image of the Main Switchboard Screen, where a Reminder Ticker is actively scrolling a continuous stream of information. The Automotive Sales & Service Company manages vehicle service contracts with corporate customers for various durations, and saves all related data in an MS Access database.

Each month, some of these contracts are due for renewal. The responsible staff must then contact the respective customers to confirm whether they wish to renew their maintenance contracts with the company.

The Reminder Ticker displays key details, such as the Customer Code, Vehicle Model, Chassis Number, Vehicle Description, and Expiry Date, with the latter appearing as the ticker scrolls into view.

The input data for the Reminder Ticker is retrieved from the Vehicle Maintenance Contract table using a query that filters records with expiry dates falling within the current month. For each contract, the Customer Code, Vehicle Model Number, Chassis Number, Vehicle Description, and Expiry Date are concatenated into a Variant variable (as a String variable may limit the length to 255 characters). This combined text is then displayed in the ticker using a Timer control.

The VBA Code

The VB code that does this trick is given below:

Option Compare Database 
Option Explicit 
'Global Declaration 
Dim strTxt  
Private Sub Form_Open(Cancel As Integer) 
Dim db As Database, rst As Recordset 
Dim rstcount As Integer, currMonth As Integer, marqMonth  
On Error GoTo Form_Open_Err  
currMonth = Month(Date)  
' Expiry_Marque is a parameter Table which holds  
' the Start-Date & End-Date of Current Month and uses to pick  
'the Contract Expiry Cases falls within this period.  

marqMonth = Month(DLookup("ExpDateTo", "Expiry_Marque"))  
If currMonth   marqMonth Then  
' when the month is changed the parameter table is  
' updated with changed period. 
' i.e. Start-Date and End-Date of the Current Month  
DoCmd.SetWarnings False 
DoCmd.OpenQuery "Expiry_Marque_Updt", acViewNormal 
DoCmd.SetWarnings True 
End If  
'checks whether any contract expiry cases are there 
'during the month.  

rstcount = dCount("*", "Expiry_MarqueQ")  
If Nz(rstcount, 0) = 0 Then
  strTxt = String(60, " ") & "*" NO CONTRACT EXPIRY CASES FOR "
  strTxt = strTxt & Format(Date, "mmmm yyyy") & " **"  
GoTo Form_Open_Exit
End If  
' builds the String strTxt with ticker data.  
Set db = CurrentDb 
Set rst = db.OpenRecordset("Expiry_MarqueQ", dbOpenDynaset)
  strTxt = String(60, " ") & "Expiry Cases:"
  Do While Not rst.EOF  
     With rst
 strTxt = strTxt & " ** {" & rst.AbsolutePosition + 1 & "}. CUST: ["
        strTxt = strTxt & ![CUST_COD] & "] MODEL :[" & ![MODL_COD]
        strTxt = strTxt & "]  CHAS :[" & ![CHASSIS] & "](" & ![DESC]   
        strTxt = strTxt & ") EXP.: " & ![EXP_DATE]
 End With
  rst.MoveNext
  Loop
  rst.Close  
'A Text Box on the Form is set with the Total Number 
'of Contracts getting expired.
  Me![mVehl] = rstcount & " Vehicles."  
' the Timer is invoked and the time to refresh  
' the control is set with quarter of a 
' second. This value may be modified.
Me.TimerInterval = 250 
Set rst = Nothing 
Set db = Nothing  
Form_Open_Exit: 
Exit Sub  
Form_Open_Err: MsgBox Err.Description, ,"Form_Open" 
Resume Form_Open_Exit 
End Sub   

Private Sub Form_Timer() 
Dim x  
On Error GoTo Form_Timer_Error  
x = Left(strTxt, 1)  
strTxt = Right(strTxt, Len(strTxt) - 1)  
strTxt = strTxt & x  
' Create a Label with the Name lblmarq  
' on your Form to scroll the values 
' The value 200 used in the Left Function may be 
' modified based on the length of the 
' Label. Format the Label with a fixed width font 
' like Courier New so that you can correctly determine 
' how many characters can be displayed on the legth 
' of the Label at one time and change the value accordingly.  

lblmarq.Caption = Left(strTxt, 200)  

Form_Timer_Exit: 
Exit Sub  
Form_Timer_Error: 
MsgBox Err.Description, , "Form_Timer_Error" 
Resume Form_Timer_Exit  
End Sub 

Ticker Active/Inactive States

The code starts automatically when the form opens and continues running until the form is closed.

When the form becomes inactive—such as when another form is opened over it—the ticker is automatically deactivated. After all, there's no point in keeping the program running when no one is watching. When the form becomes active again, the ticker resumes automatically. To add this behavior to the reminder ticker, copy and paste the following code into the form's module:

Private Sub Form_Deactivate()
     Me.TimerInterval = 0
End Sub

Private Sub Form_Activate() 
Me.TimerInterval = 250 
End Sub

Download

You can download a demo sample database from the download link given below:

Download Demo Database

• File Browser in Access
• MsgBox with Office Assistant
• Create 3D Headings on Forms/Reports

Read More

File Browser in Microsoft Access

SEARCHING FOR OTHER FILES FROM MSACCESS

In Microsoft Access, we can locate and open database files by choosing File → Open from the main menu. But what if we want to search for and open other types of files from the disk, such as documents, images, or PDFs?

In that case, we can use file dialog controls to browse and select files. Once a file is selected, we can perform several actions in Access:

• Create a hyperlink to the file and store it in a table field.

• Use the FileCopy Function to copy the file to a different location.

• Or store the file path for later retrieval or reference.

This makes it easy to manage and integrate external files into your Access applications.

Demo Run Preview

Anyway, let us get to work with the first part. But before that, a preview of the Run of our Project is shown below:

Right-click to open Large Image in a New Tab/Window

Designing a Demo Form

Open a Database from your Computer or from the Network Drive.

Let’s design a simple form for our project that includes a Common Dialog Control, a Textbox, and a Command Button, along with a few lines of VBA code to make it functional. The layout of the form will resemble the sample image shown below.

Tip: You can download a demo database from the bottom of this page.

Right-click to open Large Image in a New Tab/Window

The rectangular object on the left side is the Common Dialog control, inserted from the ActiveX Controls group. When the user clicks the "Browse..." button, a file dialog box—similar to the one shown in the first image above—opens up.

Now let us get to work.

1. Open a new Form and create a Text-Box Control, wide enough to hold the Path and Filename selected from the disk.
   • Change the Caption of the child label to File Path Name.
   • Select the Textbox control, display the property sheet (F4), and change the Name property value to lbldb.
2. Create a Command Button as shown in the above Design, display its Property Sheet, and change the following property values as shown below:
   • Name = cmdBrowse
   • Caption = Browse. . .
3. Now it’s time to bring in the star of our design—the Microsoft Common Dialog Control. To add it to your form, follow the steps below:
   
   • Select ActiveX Control from the Insert Menu

   • A list of ActiveX Controls will appear. Scroll down, select Microsoft Common Dialog Control, and click OK.
     If everything goes smoothly, a square-shaped control will appear on your form.

     However, if your MS Office installation is incomplete or not properly configured, you might encounter an error message like:
     "This ActiveX DLL is not registered. Please reinstall it," or something similar.

   Display the Property Sheet of the Common Dialog Control and change the Name property to cmDialog1. You can place it anywhere at your convenience; it will not be visible when you activate your Form.

4. Click on the Command Button to select it.
5. Display the Property Sheet (F4).
6. Click on the On Click Event property and select [Event Procedure] from the drop-down control.
7. Click on the Build (. . .) button to open the VBA Module Window of the form.

The VBA Code

Copy the Following Visual Basic Code and paste it, overwriting the existing empty procedure lines,  and save the Form.

Private Sub cmdBrowse_Click()
Dim VFile As String
On Error GoTo cmdBrowse_Click_Err
ChDrive ("C") 
ChDir ("C:\")
  cmDialog1.Filter = "All Files (*.*)|*.*| _ Text Files (*.txt)|*.txt|Excel WorkBooks (*.xls)|*.xls"  cmDialog1.FilterIndex = 1
  cmDialog1.Action = 1
  If cmDialog1.FileName =  "" Then
  VFile = cmDialog1.FileName 
  Me!lbldb = VFile
  End If  
  cmdBrowse_Click_Exit:
  Exit Sub
  cmdBrowse_Click_Err:
  MsgBox Err.Description, , "cmdBrowse_Click"
  Resume cmdBrowse_Click_Exit
End Sub 

Test Run

Open the form in Form View and click the Browse button. The file browsing dialog box, shown earlier on this page, will appear.

Select any file from your system and click Open. The full file path of the selected file will then be inserted into the TextBox control.

Download

Download Demo FileBrowser.zip

• Command Button Animation
• MsgBox with Office Assistant
• Reminder Ticker Control

Read More