MS Access Functions | Domain Functions

MS Access supports several functions for manipulation of data. One such set of functions that are available throughout Access maybe it in Queries or VBA are the Domain functions.  

What are Domain Functions?

Domain Functions are some inbuilt functions that can access the Data in Tables. In simple terms Domain functions are simplified Queries. If you are ever in a position to need to Count the number of Customer in the table who has their first name as 'Paul', you start typing..
    
    SELECT Count(FirstName) As CountOfFirstName 
    FROM Customers
    WHERE FirstName = 'Paul';

So if you are someone who is very lazy (like me !) or you have very limited knowledge of writing Queries or even if you think writing Queries is a bore, then you are in luck. Someone thought to just do this is a simpler way, just tell the compiler what you need, where to look and it gives you a result. That is the discovery of the Domain functions. 

Identifying & understanding a Domain function

In general Domain functions start with the letter 'D'. There are about 8 Domain functions.

• SELECT Domain functions
  • DFirst 
  • DLast
  • DLookup
• AGGREGATE Domain functions
  • DAvg 
  • DCount
  • DMax
  • DMin 
  • DSum

As you can see the Domain functions can be categorized as SELECT and AGGREGATE. SELECT can return Data from a Domain, where AGGREGATE returns the value of multiple records combined together.The beauty of Domain function is that it can work on both Tables and Queries. 

Things to know !

1. All parameters are treated as Strings. So make sure you enclose them in Double Quotes.
2. A Domain function can return only one Value even if the records returned are more than one.
3. A Domain function can return a Null value, if the condition is not met. So be prepared to capture that scenario.
4. Domain functions can be heavy, so best to limit its usage.
5. The criteria should be evaluated to a String, based on the Data type of the field the condition would vary. Look at the common usage syntax for more information.

Anatomy of a Domain function

All domain function can be broken down into three parts

• expression - the field name where the function needs to look at
• domain - name of the table/query where the function needs to be applied
• criteria - (Optional) if you are looking for something in specific, then you add it here.

So as you can construct a Domain function as,

    DFunctionName("expression", "domain", "criteria")

For the example we are dealing with, the function we would use be Count.

    DCount("*", "Customers", "FirstName = 'Paul'")

If you have seen the above example, I have used the * as the Column/Field name, this is because, specifying a particular column in a Count function would slow down the compiler. To be efficient using a * would be faster. 

Common Usage Syntax

The first example of every Data Type would be written basically for normal use, the second uses Form reference and how to concatenate them into the Criteria.

For Inbuilt constants/Numeric,

    DLookup("FirstName", "Customers", "ActiveCustomer = True") 

    DFirst("LastName", "Customers", "CustID = " & Me.IDTxtBox)

For Strings,

    DCount("*", "Customers", "FirstName = 'Paul'")

    DAvg("annualPay", "Customers", "City = '" & Me.cityTxtBox & "'")

For Dates,

    DSum("QuantityOrdered", "Orders", "dateOrdered = #12/01/2013#")

    DMax("QuantityOrdered", "Orders", "dateOrdered = " & _

          Format(Me.dateTxtBox, "\#mm\/dd\/yyyy\#"))

Fundamentals of Database Design

Hello, Welcome to Paul's learning curve! It's been quite a while since I had written a Blog. Now I promise to keep this consistent.

So I am here to help you out with some of the basic and simple tips and tricks with Access, that could make your life a little bit easier! The topic today is essential, and fundamental for any Database structure! This is available on YouTube !

As we have covered in my first post, JET is a Relational Database Management System. This means that the Database is dependent purely on Relationship Models. 

Throughout this post I will be using a simple example of Student information, to help you understand the fundamental principles. Let’s begin!

Rule - 1: Every row in a table must be unique.
    What it technically means by is that, consider a class of students, it is impossible to have two students who are exactly the same, to identify each student we use a unique identifiers in this case a Student ID, this is called the 'Primary Key'. The functionality of this Key is that it will not allow you to enter a record which will be a duplicate. Simple!

Rule - 2: Data should be "Atomic"
    Data in its raw form might be clunky, in a Relational DB you should not be condensing information in one single column. Simple examples would be Address of a Student. The address would contain several bits of information, House number, City, County and Post Code. All these information are by itself separate and needs to be in their own columns as such.

Rule - 3 : Data should be single valued
    Similar to Rule 2; although a bit different, let me explain with an example. If a student is taking up 3 major subjects, These subjects should not go in one column "Subjects taken" and store all the information in that column : Maths, Science, English. Instead they should go in a new table linking to every single student record, thus creating a relationship between the two tables.
   
    Why you may ask? Well the reason is quiet obvious, if you create three columns Main subject 1, Main Subject 2, Main subject 3 and then you need to collect other information regarding the subject maybe course start date. If you keep adding new columns there will be three additional columns each for one subject, the more information you need the more columns you will end up adding. So this stage you create a new table, tbl_courseInfo specific to courses. As the DB structure grows you will understand that DB does not contain just one table, but several tables broken down and combined together forming the information we require.

Rule - 4: Data Redundancy
    So as the words plainly mean, never have the same information in multiple tables, this will only cause trouble of what you are referring to. The simple example would be from Rule 3, all you need to link the two tables together is the ID, the Name and address can be obtained by simply JOINING them in Queries. There is no need to save the information on to the new table.

Rule - 5: Avoid calculations/dependent values
    This is a major and very important aspect. Example could be Grading system. You should not create a Column in the table for this, if you need grades you calculate when and where required using Queries. Never store such calculations.

So the above five rules, simply dictate how a DB should be designed. This is the tip of the iceberg, there are several more to be discovered, Normalization being another huge topic. There should be plenty of tutorials available online for you to learn this.

So let’s finally recap: Five fundamental principles to a DB Design:

• Unique Records
• Atomic Data
• Single Valued
• Data Redundancy
• Dependent data.

If you follow this simple 5 step rule, I am sure you will not be going wrong in any aspect of your design. If in doubt make sure you ask for help !

Playing around with Dates

I have not been able to do any post over the past few months, have been a very busy with work. Anyways, I am planning to make sure I do post, does not matter if it is going to be small or big. 

So, what has been keeping me busy then ?? Hmmmm... one issue which has been troubling me for a long time is simple Dates. Well it might sound a bit bizarre, but it got me banging my head on the wall for a few days, until I had figured out some tricks. 

# How can we obtain dates in Access?

In general Dates in VBA are simple to use you have the Date() that returns the current System date, you can also use the Now() that will return the Date and Time. 

Example of usage:  
Date() would return 21/11/2012
Now() would return 21/11/2012 22:42:39 

As simple as they may sound there are some complications. 

# Difference in Value

Date() and Now() even though have the same information, they do not have exactly the same information. In the sense Date() combined with Time() is the value present in Now(). If you used the Immediate window in VBA editor and type 

? Date() = Now()

The answer you would get is False, as Date() is never equal to Now(), the only way they can be equal is when you extract the Date part of the Now function, using the DateValue() method.  

? Date() = DateValue(Now())    'This will return True

# Regional settings

As the VBA compiler looks into the system for its time and zonal location, you are in big trouble if you are not in the United States. If you are using an American standard, the Date Format used is mm/dd/yyyy where as the normal format is dd/mm/yyyy. Unusual in Oracle/SQL servers where the date format is yyyy-mm-dd; since we are not comparing them, we will stick to the topic of Access. Some ways to trick the system are using format function, this is not the ultimate choice as Format can be a bit tricky as they might convert to String. Say for example you give the function the Date - 01/03/2012, in non US format it is 01-March-2012, However there might be a possibility that the system will mean 03-January-2012. 

Some ways of using Format,
Format(Date() , "mm/dd/yyyy")    'This will return 03/01/2012
Format(Date() , "dd-mmm-yyyy")    'This will return 01-Mar-2012
Format(Now() , "mm/dd/yyyy")    'This will return 03/01/2012

You can play around with the Format method but will not always help you, As Format is basically what it makes you see not the real data.

# Solution ?? !!

Does not matter what the format is as along as the data is precise that is all you need. Displaying a date is simple, you can show it as per your need, if you prefer showing mmm-dd-yyyy or dd-mm-yyyy or dd/mmmm-yyyy any Format is achievable through the Format(), but all you need is to store the right information. Did you know all dates can be (actually they are) represented as Decimal numbers in Geek terminology it is a Double Number. What I mean by that is, for example in your immediate window if you type 

? CDbl(Date())

you will get a value (for date 21/11/012) 41234.00, which is a Double representation of the Date, So the numbers before the decimal represent the Date Part and the number followed by the decimal point represent the Time bits. So if you try,

? CDbl(Now())

you will get some value like 41234.96684, so when using a UPDATE or INSERT statement that involves time, to preserve the originality of the Date/Time simply convert them to double values by using the inbuilt function CDbl(). 

UPDATE logTimeTable SET agentID = 24, dateTimeLoggedIn = " & CDbl(Now) & " ;

Once the update is completed if you check the table it will have the data as the same format that you have set in the design view and the Date and Time value will be preserved. The main beauty of using this is because you do not have to use ## when dealing with dates, you do not have to worry about formatting. When dealing with queries, you do not need to throw in a Format to match the one in the table. 

So this is one work around dates, that makes your life with Access a bit ore easier. 

If you have any access related questions the best place to clarify would be Access World Forums, in which I am an active member of (under the name pr2-eugin). Apart from ('not really that great') me, there are also very talented and highly experienced MVP's, who can help you out with your questions.

DATA CORRUPTION - Avoiding & Recovering (Part 2)

In my previous post I explained to you on what are the various ways you can avoid corruption in the first place so that you are not here. If you are here, well not all is lost. We might be able to do something about this. So keep reading to help yourself out of this mess. 

Recovering from Data corruption

There are only few ways which you can try before giving up on your data, that is the main reason you should make sure that you have them in the right format in the right place at all times. If in case you have a corrupted database the first you can do is: 

# Compact and Repair your DB
Whenever you add new records to the DB, the JET engine allocates the set of memory to the record. Let us assume each row in our DB takes up to 1MB of space (should have been all 255 columns.. LOL). You add 5 records to an empty DB, the size of the file would now be 5 MB. So the next day you decide I only need 3 rows, so you delete two rows from the table. Now if you check the size of the file it will still be 5 MB. Access is intelligent but not a genius, though you have deleted two records the allocated space is still there as wasted space. Unlike other high sophisticated programming language, Access does not have the concept of Garbage collection. So, if you keep doing this over and over and over again you will have only 500 records but your file size might be 1 GB. This not only bloats the DB, it also looses its tendency to properly index and store data, which again leads to inconsistent data. Please use the inbuilt functionality of 'Compact & Repair' to clear out all wasted space and also for smooth functioning of the DB. 

'Compact & Repair' can be accessed from DB Tools

There is a small disadvantage for using the compact and repair nothing too hard. Just make sure that you are the only person who has access to the file at the instance when you perform a manual compact and repair. Specially when you are trying to do a compact and repair on the Back end file of a split DB. This is because, when you perform compact and repair, access opens the file creates a copy and to the copy it tries to optimize the wasted space, reorganization of Auto numbers and then if successful deletes the original and leaves the new compacted and fresh version in its place. Thus we do not want anyone performing a deletion of the file, whilst Ben is still updating a record. This by its own will lead into a corruption. Access also prompts you to perform a Compact & Repair now and then but responsibility lies with you to perform such activity. You can automate this process on close by going into Access Options --> Current Database --> Compact on Close.

# Decompiling your DB 
Compact and Repair solves majority of the Data Corruption that has occurred through orphaned data or incorrect information that has been entered into the tables by people. However sometimes there is a possibility the code that has been written could also cause the error. This is either by unintentional piece of code (or) incorrect syntax (or) not properly closing Sub/Function. This however does not mainly affect the backend, but will however stop you from accessing the records through the Form. Before decompiling the Database, it is VERY IMPORTANT that you make a backup, as sometime this may make situation so much worse that it was before. Hence Decompiling should be sought out as a last resort. 

When you are trying to decompile, you are requesting the Debugger to do the reverse of a compilation, in simple terms you are asking it to leave stuff as it is in the coding region. This will avoid the compiler jumping into conclusion of whose fault it is, thus allowing us to find out where the error might be. The steps for achieving this is very simple and easy. If your Access has hung up and wants to restart, let it run its course; as Access tries to reclaim your data by creating a BACKUP. Which then you can work on to check for errors.

Just copy the file to another location, and try to open the file from there - This might not be official or one standard method, but at times have worked for me thus avoiding the usage of decompiling. 

Steps for Decompiling:

1. Create a shortcut on the location of the file.
2. In the path first create a path to the location of the MS Access EXE file normally as,  "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
3. Followed by the path to your corrupted file  "D:\Work Related\OFFICE\Interface.accdb"
4. Followed by the word  /decompile
5. Thus your entire shortcut should look like..

    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"  "D:\Work Related\OFFICE\Interface.accdb"  /decompile

Now click Next, you will have a shotcut, now double click on the Access icon you will be able to see your interface, now quickly press ALT+F11 to open the Code Browser to browse for any errors. Try compiling, if there are any errors it will be highlighted for you to correct. If not then whatever reason the file did not open would now have been resolved and will be working now. 

I have now finally covered the topic of 'How to Avoid corruption and Recovering' from them. These are the ones that I had learnt in my experience and usage with Access. There might be still so many ways. Knowledge is an ocean, what I know is only a raindrop. Thank you for reading this post. Please feel free to share and comment your thoughts.

DATA CORRUPTION - Avoiding & Recovering (Part 1)

People who use MS Access as a part of their day to day job, sometime find themselves in a situation where they look at the screen with a 'nervy peek' thinking all is gone. This happens when they try to open the file and the records are corrupted, thus not allowing to open the file in the first place or may lead to inconsistency of large volumes of data which still poses a threat. Well if you are reading this, I am just trying to let you know not all is gone. You might still have a fighting chance to save your data. 

Before going into the details of how to recover your file from a corrupted state let us think a step ahead. Have you every heard about the expression "Prevention is better than Cure"?? Exactly, if you do not mess about with data which is highly sensitive; you might not even end up here. So let us see some basic steps of how to properly do things to avoid such disaster. 

Avoiding Data corruption

# Create a Front End/Back End of Access

Creating Front end (FE) and Back end (BE) might seem very complicated and time consuming and at times even very terrified. But fear not. Splitting the DB into FE and BE is very common and is always helpful in making Form usage smooth and allows multiple users access more easier and efficient. The splitting process though not very hard, I will describe in a new post. Splitting is just organizing what goes where. When you split your DB, the FE contains all your Forms, Queries, Macros and Modules. The BE will contain purely your tables. Thus a link will be created between the Forms to interact with the data which is commonly used by other people. This not only solves for the issue of concurrent users but avoid the data being corrupted because of faulty coding; that crashes on open and or trying to compile enormous amount of data. 

This is what we wish to achieve once we have split the DB

Let me tell you one another great thing of splitting a DB; you can now have two access files each for up to 2GB . Thus you can have more data in the BE and also you can have more functionality that can be added to front end to make sure data stays safe and secure. 

# Avoid MEMO and Multi-valued Fields

Multi-valued fields like attachments can be stored for each and every record alongside of the other data unlike the BLOB data type in SQL, which stores them in a separate table. Sometime it feels appropriate to have some files like pictures or PDF's relating to a particular record along with the record for referencing. Well it sure is, we do not want to have opening Mrs. Jane's ultrasound, under Mr. Smith's kidney scan. Utmost care should be taken for using such fields. As this is one major area of corruption. This not only takes a lot of space, which bloats the DB, but also if not properly taken care can lead to corruption. The best way to avoid it very rarely use them. If there is a need for such fields; let us follow the example of SQL. Create a separate table and have a one-to-one relationship with the master records, thus if in case a corruption occurs it will at least leave the master records intact. 

# Avoid Storing Calculated Data or Lookup's on table level

Calculated data does not mean that you should not save values that are calculated by VBA on a FE.

Example maybe using a form to auto fill Name of the day on a field. 

This however is calculated does not have any problem in saving. When the word 'calculated' is used it means data depending on other fields. 

Good example would be the field 'Amount to pay', which is obtained by using multiplying 'Quantity' and 'Price'. 

Just in case; one fine day you decide to delete Quantity as it was just a number that you wanted to make sure is there but now not required. All your data inside 'Amount' will be automatically lost thus leaving the fields value to be set as #?Name, as it depends on the 'Quantity' field. 

Similarly Lookup at table level is again an evil thing to do. 

Example: If you have a field 'Employee name' looking up on 'Employee' table for values. 

If Jon has left the company and you remove his name from the Employee table the field now references some value that does not live in the table anymore. Thus again leads to Corruption. Lookup's in FE is the best way to use. The Tables that you use should only contain data that are being entered. This way you can be rest assured that data can never be corrupted.

Please carry on reading to find ways to Recover your Corrupted file.

MS Access - Not 'Bad'

ALL STARTED WITH A 'BIG BANG'

On my first day at work, my manager offered me a early Christmas gift, packed and wrapped. I opened up the packaging to find 'MS Access 2010 DVD'. "Oh boy !", I made a sarcastic sign.  Well the main reason was I never had found a great interest in Access, have always got in various con's of the software; rarely ever people talk about its pro's. Working with Access in the past few months, I have learnt that it is not bad after all. So many shocking truths that gave me a whole new look at Access. I am planning to mention a very few advantages of using the this software over the others available. I am not trash talking about the great open source Databases that are currently available; they are amazing and provide much better services and functionality, but I am just trying to make you all see what Access has to offer to us all. Access is one of the product where 'good things in small packages'.

# Inferior??

As with the crowd, I also believed that MS Access is nothing but an inferior Database, starting to use it I realized that Access is nothing but a simple User Interface to the real deal. Yes, Access just provides the functionality and service of connectivity to the Database server which is the JET engine. (A shocker right??)  The tables that you create and manipulate actually resides in JET, where Access just shows you a virtual reality of how it looks like. This functionality of creating a GUI helps even any novice user to get along with. Just imagine, if you are sitting in front of the computer and you wish to create a simple table, if you really do not know the exact syntax you will always face the error..  

" ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'dae) at line 1

Honestly, I will say 'Oh dear !' well, using an interface comes in handy here. Every single step is a guided tour that helps you feel more confident. You do not need to know the exact syntax, just a drop down box will list you what t needs to be Text/Date/Number...... Well that solves a great issue ! (NOTE: SQL server also comes with GUI tools to help you out)

# Size & Concurrent users matter !!

Yes, I totally agree that size does matter and the greater the size the better is the Database. JET engine allows you to store up to 2 Gig of information, though it is not a very big; for a small organization or a start up industry that is going to hold 20K records and cannot provide the funding for a 'brilliant' IT team this would do great wonders. Access provide support of accessing information from the table for around 255 concurrent users. If a company has just started with 50 employees I think this is just enough. 

# Front end development ??!!

Access uses VBA (Visual Basic Application) for precisely tune the already existing functions, in other words to 'override' them. Just as in any programming language you will be able to make it do what you want it to do. Although VBA is not a greatly sought after language, it still is good. Just because something is out of trend does not mean it will not do what it needs to do. In fact, when you understand what needs to be done in a simpler language, you will be able to transfer it across to other platforms. 

# DATA CORRUPTION

Access is highly prone to become corrupted. Yes, but this will happen only if the user is tampering with the data (or) if they are not going to use it correctly. However, Access has not always won this case, but in my next post I will post how to avoid corruption and steps to reclaim your corrupted database. 

# More to 'Access'

As we all know, MS packages often come with additional advantages such as Flowcharts, pie-charts, bar-graphs etc. This functionality is really useful to develop precise  reports that do not need a great detail to look into. On few clicks of a button, you will have perfectly described reports and diagrams that project the outcomes. This involves PivotTable, PivotChart. Querying in Access is more efficient than normal queries, you do not have to worry about the missing parentheses, appropriate punctuations. You can also forget the worry about how you are going to place the keywords.. 

"...how to define parameters? 

...will parameter be written on the first line or last?

Well the above mentioned are some of the great advantage, there are still more that can be achieved by using MS Access. It is not a waste of time after all, take it from a person who has used it and is very satisfied. Please feel free to Share and Enjoy !!