#ms access database system

[benbeitler1423-blog]

Microsoft Access Database Tutorial

Microsoft Access eBook https://accessdatabasetutorial.com/access-2013-2016/ gives you more clarity about using Access Database.............. We will show you how to use Microsoft Access Database by...

[benbeitler1423-blog]

Action Queries - Archive data

"Who is going to take responsibility of your Access database?

The author may not be the end-user, the developer may not be the analyst. There are multiple roles when planning, designing, building and implementing a system not forgetting the running and maintenance of the Access database application.

Assign one person, and in his/her absence a second, to back up the data and store it off site on a regular basis without overwriting the last back up.  

How often you back up is determined by how much data are you willing to re-enter and how much time might have gone by until the loss is detected.

There are several ways to back and restore data from the standard back-up and restore of the whole database to individual tables using either the crude copy and paste action to the more sophisticated ACTION queries (namely Append, Delete, Make-Table and Update).

Action queries can wrapped up into calling macros or VBA procedures to automate this important task.

Related article: https://accessdatabasetutorial.com/microsoft-access-database-archive-system-in-access-using-queries/ "

[benbeitler1423-blog]

Validation Rules (and no programming required)

Use validation rules to help prevent data entry errors by restricting what information can be entered in a field.  There is the optional message box or on screen area (via the status bar) to let the users know what is needed prompting them to enter the correct value.

No programming skills are required (though you are welcome to use VBA for the more complex of validations) and can be set in a table or form via the properties sheet/window pane. A validation rule is a logical rule that yields true or false to accept or reject that entered value. The message box is simply a replacement message (from the default Access provides) that allow you to interact with the user when violating a rule.

To see some examples and a quick video tutorial on validation rules, check out https://accessdatabasetutorial.com/microsoft-access-validation-rules-in-tables/

[captainvtrenderus-blog]

Validation Rules (and no programming required)

Use validation rules to help prevent data entry errors by restricting what information can be entered in a field.  There is the optional message box or on screen area (via the status bar) to let the users know what is needed prompting them to enter the correct value.

No programming skills are required (though you are welcome to use VBA for the more complex of validations) and can be set in a table or form via the properties sheet/window pane. A validation rule is a logical rule that yields true or false to accept or reject that entered value. The message box is simply a replacement message (from the default Access provides) that allow you to interact with the user when violating a rule.

To see some examples and a quick video tutorial on validation rules, check out https://accessdatabasetutorial.com/microsoft-access-validation-rules-in-tables/

[benbeitler1423-blog]

Create your own custom forms and reports for reuse

When designing many forms and reports which will often be the case due to you having other users who need to interact with your application, there are many ways to create these types of objects. Which method do you use? The wizard tool? The template icon (where applicable)? Manually in design view? The answer is of course personal to your style and method approach and sometimes you may use several to achieve the desired look and feel versus the time you have allocated. For me, I use the design method in most cases but I then carry out one more extra task which is often overlooked and that is create my object into a template. In essence, the template is no different from a standard form or report and is saved in the same way. Just create a standard (uniformed) form and a report that will be available to at least 90% of all objects which means for a form, you will probably want a couple of command buttons (a close and cancel action) with generic code to close the form too. Do as much as you want including the formatting, sizing and fixing controls before saving the object. I use the naming convention (all in uppercase); FRM_TEMPLATE and RPT_TEMPLATE. Now I simple copy and paste with a real name for the final object and start to modify it giving me a head start. You can attach a form and report object to Microsoft Access options as the default template too but I find this technique more flexible (to me anyway).

  MICROSOFT ACCESS DATABASE FORMS: SHOULD WE USE UNBOUND FORMS?

https://accessdatabasetutorial.com/microsoft-access-database-forms/

[benbeitler1423-blog]

AutoKeys Macros are also simple to use

"Macros have been around forever and if you recall back on the 'week 8' weekly tip which focused on the AutoExec macro that automated how you started an Access database.

There is another and just as important pre-defined macro starter known as 'AutoKeys' which again is a preserved keyword for macro names that fires up when your Access database system starts.

This time, it handles your keyboard shortcut assignments and the more you add to this object, the more you customise how users engage in using those keyboard shortcuts to control functionality over and above the standard functions you may have used in Access.

It's a great feature which will supersede your standard keyboard shortcuts that can sometimes expose users to features not always required and help maintain a protective application state. For instance, I don't always want users to print an object by making multiple choice options like number of pages, changing printers or even reconfiguring the printer itself which a standard Print dialog box provides so I overide CTRL  + P to something else!

As mentioned, the keystroke for printing is CTRL + P (if you weren't sure) but by adding this command into your AutoKeys macro it will execute whatever action or object you wish to assign:

The CTRL accelerator is represented by the following character: ^ (caret) which means the following macro name: ^P is the action; CTRL + P  and you choose a command (or two) to attach to this macro name. I normally use the 'RunCommand' keyword and call VBA code making it more flexible.

Truly a way to control Access and is one justification why macros are still supported in Microsoft Access. " USING THE MS ACCESS MACRO TO AUTOMATE YOUR DATABASE https://accessdatabasetutorial.com/using-the-ms-access-macro-to-automate-your-database/

[benbeitler1423-blog]

VBA - Option Explicit reduces design code error

For those about to rock, I salute you! As you can see, I'm a bit of a AC/DC rocker (rock band from the 80's) but when Access developers gear up to using VBA and start programming in Microsoft Access, this is where the application starts to rock 'n' roll.

When adding modules (which is where VBA code is stored) learn to always use the 'Option Explicit' statement in your VBA code to ensure there are no undeclared variables being used. By using the two keywords at the top of a module, the whole module is checked and scanned for any unauthorised variables as it acts like a real VBA keyword (from a library) when compiling the code. It will stop you from creating errors and resolving the problem at design time.

You can automatically set this as an option so that every new module added, will add the two important keywords for you. It can be found in the VBA window view (code view) from the Tools, Options dialog box and is the third checkbox option 'Require variable declaration'.

You can choose your subject:

Ms access database tutorial

Microsoft access training

Microsoft access database

MICROSOFT ACCESS FORM DESIGNS: USING VBA WITH ON TIMER EVENT

Microsoft Access Form Designs: Using VBA With On Timer Event

[benbeitler1423-blog]

AutoCorrect names can be buggy and drain your database

Some of the Microsoft Access features were introduced to help improve functionality and automation. However, some of Access features became a hinderance rather than a benefit and one I want to quickly mention here is the 'AutoCorrect' option in the Access Options interface. This feature was introduced with Microsoft Access 2000. It tries to help users who rename a table field, so that queries, forms and reports automatically recognise the new field name. Microsoft has addressed some of the problems with this feature, but is it still buggy, incomplete, and casue a strain on performance and should be avoided. It is better to therefore turn the feature off and depending which version of Access you use can be accessed via the 'Options' command. For Access 2000/2002/2003 it can be found via Tools, options, General tab. In Access 2007 it can be found via the 'Office' button, Access Options, Current Database tab. Finally in Access 2010 i(and later versions) t can be found in File tab, Options, Current Database. Final point here is that you must remember to do this every time you create a new database, preferably before creating any other objects.

MICROSOFT ACCESS DEPENDENCIES: WHAT IS THE OBJECT DEPENDENCIES PANE? https://accessdatabasetutorial.com/microsoft-access-dependencies-what-is-the-object-dependencies-pane/