Using Arrays in Access

Testing if a value is in an Arrays using Functions

A client of mine recently had a situation where they were granting scholarships based on a particular major.  They needed a program that would "check their work" and either identify students who qualified or verified that recipient's were correct.

Although there were a number of ways to complete this task, I ended up using an Array and figured this would make a good example for the blog.

An Array is used in many programming languages and refers to a set of numbers or objects that will follow a specific pattern.  The set of values for this array was the list of major codes.  There really wasn't anything specific that related these majors as they were picked by a committee based on many different factors.  I could have stored these values in a table, but they will change year to year and are really only needed for this one function.

I'll go through the code here:

1.  First we create a function name ScholarshipMajor.  I will use this function in Queries and pass to it one argument [Major].  We define Major as a string value.
Public Function ScholarshipMajor(Major As String)

2.  Next we define our Array.  I name our Array ValidPlans and define it as Variant.  This way I'm not limited to the number of majors I can list it in.
Dim ValidPlans As Variant

3.  Now we have to load values into our array.  I've shortened the list of majors to make writing this easier.  The original list had 67 majors.  We are going to use the "Split" function to load our values into the Array.  The Split function asks for the string and the delimiter as its 2 arguments. The values are all included between one set of " " and we put the delimiter here in " " as well.  We are using a comma delimiter. 
ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")
At this point, we need to break from this function and write a second one.

4.  Now that we have an Array that lists all valid majors for this scholarship, we need to write a function to test if the passing value (major) is in that array.  The function here can be used to test if any text value is in any array.  You just have to pass the function the array and the value to test for.  The "As Boolean" will default our function to return a -1 or 0 for True or False.

 
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function
5.  With this function to test if a value is in an array, we can finish our original function.  Here we will write an IF statement using our newly created IsInArray function.  We will pass that function the major to test for and the array we created.  If IsInArray returns as True (-1) then this function will return True.  Otherwise both functions will return false.

If IsInArray(Major, ValidPlans) Then
ScholarshipMajor = True
Else
ScholarshipMajor = False
End If

This could have been built into one function but I've split out the test part to make it more usable to other functions without re-writing the code over and over.

I've placed all the code in order below.

***************************************************
Public Function ScholarshipMajor(Major As String)
Dim ValidPlans As Variant
ValidPlans = Split("ARVA,ARTH,ARTT,DHAR,DHEN,",")

If IsInArray(Major, ValidPlans) Then
    ScholarshipMajor = True
    Else
    ScholarshipMajor = False
End If

End Function

 
Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Use queries to do your work

Many people think of queries as just a way to ask the database a question and get a response.  The typical query is a select query.  This is one in which you are selecting a sub set of data from a table, or multiple tables, and either filtering rows and/or columns to create a new recordset.

Queries can be so much more than just select queries.  Here I will go over 3 types of queries that you can use to modify the data in your database.  The queries DO NOT return a set of data, but instead can add, edit, and/or delete data in your database.



 INSERT  (Access refers to this as an Append query)

An Insert Query is used to insert a new record, by listing values that match existing fields in a table.

This example would insert my data into the Customer table in the database:

INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES (‘Rob', 'Buonocore', ’130 Mapple Ave', 'NJ', ‘Dumont', 07628);  

• The fields do not need to be in the same order as they are in the table.
• You do not need to enter data into every field unless the field is set up as required on the table side.

The above example is a bit silly since you would rarely have the opportunity to hard code values into an insert query.  So to show a more practical application, you can use the same query to refer to fields on a form.  Then call the query from a VBA statement or Macro and it will insert the values that you entered into fields on a form, directly into your table.

            INSERT INTO Customer (FirstName, LastName, Address, State, City, PostalCode) VALUES
            (Forms!myForm.Fname, Forms!myForm.Lname, Forms!myForm.Address, Forms!myForm.State, Forms!myForm.City, Forms!myForm.Zip);


In this example, the user would enter data into unbound controls on a form and the query would pick up those values and insert them into your table.

You can also use an insert query to append many records at one time.  Usually this is appending all the records from one table or from a query.  I often use this type of query to append all the ID numbers of a group into another table to record that I performed some action on them (for example, sent them a letter).  I'll also use this type of query to perform archiving of records from one table into a master table.Rules:

• Inserted data must have the same data type as the field Or be convertible to the same data type
• An inserted value can not violate a check constraint
• If a column is omitted the field is assigned:
• Default value if one is set
• NULL

 UPDATE
An UPDATE query is used to edit existing data in the database.  It can be used to update one specific row in a table or all rows in a table.  This kind of query can use the WHERE clause in order to select the specific records to be updated.  Not entering any criteria will cause all rows to be updated.


                 UPDATE Customer SET FirstName = 'Robert',
                 WHERE FirstName = 'Rob';


UPDATE Rules:

• You must specify the Table to update, the Columns to update, and their new values.
• Optional search condition that specifies rows to update

 DELETE
Use a DELETE query to remove/delete:

• A specific row in a table
• All rows in a table

You can use the WHERE clause to select a specific record to delete or a sub set of records to delete.  If you do not enter a WHERE clause it will delete ALL the records in the table. 

DELETE *
FROM Customer
WHERE State ="NJ";

Rules:

• You must select the table to delete from.
• You can not delete certain fields.  You must delete the entire row.

Each one of these queries has so many more possible ways that they can be used than is presented here.  The point is to recognize that these types of queries exist and to begin thinking of queries as more than just selecting records and columns.

Robby

Where does an Access Database Application fit into my Organization

Many people don't understand Microsoft Access and have a hard time seeing how it can help them.  They hear the word "database" and think "large system", "programming", "servers", and get overwhelmed.  When they have data to store, they revert back to Excel spreadsheets and workbooks or just putting it in Word.  True, its safe and easy and I can see it there right away.  Incorporating an Access database into your organization doesn't have to conjur up negative feelings.  Hopefully, this post will help you answer the question, "Where does an Access Database fit into my Organization?"

Many data challenges that companies face are not large enough or important enough to merit a solution that cost $100,000 or more. Yet just because the challenge isn't large enough to merit this investment, it doesn't mean that it doesn't exist. Access can meet this in between range of data issues and challenges.

Storing data in Excel
As I mentioned earlier, and in other blog posts (see Beyond the Spreadsheet post), many people default to storing data in Excel.  Its is easy to get started with and usually requires no investment.  One downside to getting started quickly is that users rarely take the time to think about the data that is being stored, the amount, or how the data will be used.  One thing to make clear is that Excel is not a database.  It is not relational and thus does not have all of the advantages that a database provides.

Another thing to consider is that storing data in Excel is not secure and drives IT guys crazy.  The average cost of creating an Excel application is $500. Excel is used because it is simple. People know it and can use it very quickly.

Using an Access Database Application
Access is the most popular database in the world.   It can solve a range of problems (see Top 10 Reasons to use Access post) and can still be deployed fairly quickly.  True that it takes some knowledge of Access.  Access provides a great Return on Investment.  It serves as a great tool for prototyping.  The average cost of an Access solution is:

Access Individual user system - $3,000
Access Multi-User system - $10,000
Access Department Level system - $25,000

Using an Server  Database Application

Larger than Access is a server side database.  Examples of server side databases are SQL Server and Oracle.  These are major systems with major investments.  They are the databases that, mentioned earlier, are not useful for many small problems.  They require hardware, software, database adminstrators, security concerns, conectivity concerns, and the list goes on and on.  The avgerage costs for a server side database are:

SQL Server - $500,000
Oracle - $2,000,000

There are instance where an Access database can evolve into a server side application.  This is another advantage of Access as it allowed you to protype a system, determine the details, and work out the solution at a smaller investment, thus making the upgrade to SQL Server must more efficient and ecconomical.

Most small businesses are never going to have the funds for ability to create true server side databases unless they outsource and rent server space somewhere.


So in short, Access should be used when you are ready to graduate from using Excel and really commit to building an Application, but aren't ready to make a giant investment into the solution. 
Robby

Things to do before you get building

I recently started doing some work with Filemaker Pro as a database development tool. For those of us who are "born and bred" Access developers, this is like turning to the dark side. However, software prejudices aside, I was struck by how the stuff you do before you get into the actual building of a database are very much the same. The logic of a well designed relational database goes deeper than the software used to develop it. So, what are some of these steps?


1. Think about the data: What things will the database be about? A good way to start is simply to write a list in good old English (or any other language you happen to be fluent in).

• Watch for the nouns you use: Clients, employees, invoices, appointments. These are the data entities your database will incorporate.


• Ask yourself what you want to know about these things. Sometimes these bits of information will be the attributes (aka fields) describing the data entity (e.g. name of client, date of invoice) but sometimes they may give rise to a new entity. A good example of this would be the payment of an invoice. If you simply want to indicate whether an invoice has been paid (a verb), you can simply attach an attribute to the entity "invoice" (which will probably become a "yes/no" field in your invoice table). But if there are things you want to know about THE PAYMENT (a noun), such as the amount, date, and method of payment, then the payment itself becomes a new data entity that will have, as one of its attributes, a reference to the invoice entity.


• Create a simple Entity diagram: As you turn your narrative into a graphich representation, you will clarify the entitites involved and their relationships to each other. There are many free tools available for download to assist in this process.

2. What does the system need to do or produce?

• Initially, try to limit this to the core functions: those things that are the main reasons for the database.


• Look for any gaps in your data entity analysis. When thinking about a function that needs to take place, or a report that needs to be produced, we often discover that we have not properly included the necessary data in our design.


• Start to envision what forms will need to be developed. Create a list of forms with key functions for each (Record retrieval, data addition/edit/deletion)

3. Consider the installation environment and constraints:

• If this is to be an Access databasse, does the intended user have Access installed? If so, what version? If not, how many user licenses are needed? (Developers can also consider packaging a solution as a runtime version to avoid the need to purchase Access. However, this introduces other issues.)


• Where will the database reside: on a file server or a computer?


• Will it be shared? If so, by how many people and how will they connect to the host location?


• Are all users the same? Will you need to restrict certain data or functions for different users?


• How secure is the environment?

4. Develop a naming convention: This may seem superficial but having a logical way of naming your database objects will make it easier to follow the flow of the system and to make modifications later on. Although Access has gotten better at updating references to tables and queries if you change the name later on, problems still come up when you do this. Experienced users/developers probably have a system that works well for them. We have used some of the following conventions:

• The names of tables that relate to data entities start with "T_" and names for those that are used to look-up values used in data entry begin with "LU_"


• Don't leave spaces in the names of objects. For readability, use underscores or hyphens. This is more important if you intend to do programming in VBA.


• Try to group related objects by using with similiar names; employee_bio, employee_addresses, employee_names.

As with building a house, working out the design concepts is the first step in assuring a successful outcome.

Joe

How secure is Microsoft Access? Part 2

In our last post, we started to discuss the how secure Microsoft Access is. I provided some quick and easy ways to keep out preying eyes and that can be implemented right away. Please look back at "How secure is Microsoft Access? Part 1" to see these.

In this post, I'm going to go into a few more complex methods for securing your Access database.

I. Splitting your tables from the client database. A common practice when devleoping an Access database is to create a "front-end" and a "back-end". The front-end is the "program". It is the database that will store all your forms, reports, queries, etc. It will NOT contain the tables. The tables will be stored in a second database or "back-end". The front-end will access these tables as linked tables. This setup is used for a number of beneficial reasons, but you can utilize this to really secure your data.

1. After you create your back-end database and the tables, set a database password on the file. Then when you go into the front-end and link to these tables, you will be prompted for a password. The password will be stored and now users who don't know the password will only be able to access the tables by going into the front-end.

2. With a little VBA code, you can set up your database to link to the tables every time you open the database. If the user authenticates correctly, then the tables will be linked. If the user does not, the tables will not be relinked. Thus they can not be accessed at all.

3. You don't have to have just one set of tables. You can have mutliple Access files each storing certain related tables. Then, the authentication procedure that you built, can determine which tables need to be linked and which do not. Thus each user will ONLY have access to those tables that they have been granted access to.

II. Turn your front-end into an MDE file - Once you have finished developing your database and before you distribute it users, it is good practice to turn the front-end file into an MDE file (ACCDE file in Access 2007 and later). By creating an MDE file, you restricted the user from going into design view of Forms and Reports, and from viewing VBA code. This not only will protect your code, but it will hide sensitive information like the path to the data files and passwords needed to access them.

On a side note, an MDE file will also perform better than an MDB file.

III. Piggy back on existing security - If your database is going to reside on a network file server, you probably already have network security in place to access those shared drives. You can set Access up to piggy back on top of this existing security. In order for me to gain access to the shared drives at InfoCore, I have to sign into our network with a user login and password (lets say user name rbuonocore). This user name and password is stored in the network management system and not in Access. When I open up Access, you can write code to "pull" the network user name of the person signed in. In this case, when I open up my database, it will determine that the person signed into the network is rbuonocore. I can then build my security in Access around permissions granted to rbuonocore. I don't have to worry about authenticating or storing passwords because my network management system has already done that for me. This same concept can be used with normal Windows logins as well.

So...How secure is Microsoft Access? Although not as secure as a full blown database management system, a very secure system can be created by using all these listed methods in combination; one that protects access to the system, access to the data, and access to functional areas inside the database.

-Robby

How secure is Microsoft Access? Part 1

One complaint I hear from many IT professionals who don't like Microsoft Access is that it really isn't very secure. Although Access is not as secure as some larger Database Management Systems, there are a number of things you can do, that when used in combination, can really help to secure your database. In this post, I'll present a few simple tasks that you can do that will keep out preying eyes. These methods are quick and easy and can be implemented right away.

I. Encrypting your database. Encrypting a database compacts the database file and helps protect it from being read by a word processor. Encrypting is particularly useful when you transmit a database electronically (FTP or Email), or when your store it on a flash drive or compact disc. Remember though that most databases will grow very fast and thus transporting them becomes very difficult anyway. Also, many email programs (Outlook included) don't like to receive emails with Access files attached. They basically view them as threats and it's difficult to receive. One quick way around this is to zip the file first and then send it.

WARNING: Encrypting will slow down the performance of your database.

II. Hide your objects and database window. This is a very simple method of securing objects (tables, forms, reports) from the casual user. Through the Start up properties, you can set your database to not show the database window at startup. You can also select settings that will stop the user from un-hiding the database window. Then if a user gets into the database window they won't see the tables, etc. that would get them into the information.

WARNING: This is not a very secure method. It only stops the casual person from accidently opening these objects. Anyone who know a little about Access will know how to unhide the objects, but it is still good practice to do.

III. Create a database password. As with most Office applications, Access provides the ability to set a database password in order to open it. This again is a simple means of protecting your data from others and limits access to the system to a select few that know the password. In this case, there is only one password. All authorized users must know the password in order to access the system. Although this is a good means of keep unauthorized users out of the system, it doesn't help much when certain authorized users only need some of the data.

WARNING: There are programs out on the market that can break Access passwords very easily. I have a number of them that I use to help people when they forget their own passwords that they set.


IV. Create user sign-on. Access has its own built in user level security. You can create users, provide individual passwords, have the user authenticate when they open the database, and restrict certain users to certain objects. I have always found that this is easy to get started with but not robust enough and have always opted for developing my own security for my databases. In doing so, you can not only restrict certain users to certain objects, but you can also limit the data they see. If Joe is the salesmen for the NY area, then I can limit Joe's view of the customer table to only NY customers.

These are some very simple methods that can help to secure your database. In our next blog post, I'll discuss a few more technical methods that can really lock things down.

-Robby

Top 10 Reasons to use MS Access

Microsoft Access is the most popular database program in the world for a number of reasons. One of the biggest is that non-IT professionals can use it to develop solutions that are cost-effective and that address a wide range of data management issues. Access essentially brings Visual Basic, SQL Server, and Crystal reports all down into one nice package that almost anyone can start using.

Here are the Top 10 reasons why I like MS Access....

10. Great for projects with little or no budget


Solutions can be built quickly and, if done in house, for almost no dollar cost. Many small businesses already own a version of Microsoft Access and just aren't using it. Either in house our through a consultant, applications can be built quickly and at low cost that solve many issues and have an immediate effect on your business.


9. Allows for Rapid Application Development

Building in Access is a great Agile development tool. A non-IT professional can quickly start building an application and will quickly see how Access can address their data challenges. Access is also a great environment for prototyping.

Many databases built in Access solve a specific problem and may be short term solutions. They last for a while and then disappear as the issue is resolved.

Other databases start out to address one issue but evolve to address many other needs. These may eventually outgrow Access but, when its time to upscale from your Access database to a SQL server database, you are well prepared because you have your Access database to serve as a prototype.


8. Easily Integrates with Microsoft Office

One of the best features of working with Microsoft Access is its ability to integrate with other MS Office products. You can get the most out of Office when you let each product do what it was intended to do; MS Word was created to do Word Processing, Excel, for number crunching and graphs, and Powerpoint for presentations. Data can be easily moved between Excel and Access, letting you use the best features of both. Access queries can easily provide a data source for a Word Mail Merge document. The integration of all the Microsoft Office components creates an environment that exceeds the sum of the parts.


7. Great for Data Entry

Through the use of Forms, Access allows for very customized and easy to use forms that are great for data entry. The combination of these allow for the creation of a very user friendly interface to interact with your database. Business rules can easily be built in and through the use of combo boxes and list boxes, data entry can be simplified and uniform.

6. Interface with other Database formats

Access should be described as a tool more than just a program. You can build a solution in Access and yet link to data in other applications such as dBase, Paradox, FoxPro, as well as SQL Server, Oracle, and DB2 through ODBC connections.

You can even link to other Access databases. This ability basically solves any space limitations you may worry about. Tables can be stored in as many databases as you need; each allowing for up to 2 gig of data. Then the tables are linked back together in your front end application. Basically, the only limitation to the size of your database will be the size of your hard drive or file server.


5. Query Designer

The query design tool in Access is excellent. Dragging and dropping fields into your query couldn't be easier. It is laid out nicely and allows even the most novice query writer to begin building useful queries. As you get more experienced, you can easily switch to the SQL view to write more complex queries.

Even experienced SQL writers will prefer to use the query designer just for the ease of it...I know I do.


4. Report Generator

Now that you were able to get your data organized and in one place, you'll want reports. Bigger database systems like Oracle and SQL require a separate system to run reports. Access brings a reporting tool right into the same package. Reports can be built directly against tables or on top of pre-existing queries. Experienced and inexperienced users, through the help of wizards, can build very fancy reports quickly and easily. There are even some preformatted reports to help with labels.


3. No need for a server or extra hardware

Unlike, Oracle or SQL, MS Access does not require the use of a server. The MS Access program and Jet Database Engine run on each machine. This means, instead of a server doing the work, each machine using the database will handle the processing.

Very often, we will build our application to utilize an existing file server. The data will be stored in an Access Database on the file server and each user will have their own version of the program (the Forms, Queries, Reports, etc) on their own desktops. If the company doesn't have an existing file server, we often just dedicate one of the workstations as the server and let the datatables reside there. Another possible solution is investing in a Network Attached Storage device. These devices are small and cheep and can take the place of a file server nicely.


2. Multiple users

Although Access has advantages for the single user, sharing the the database with multiple people all at the same time is where you will really see the biggest impact. MS Access will handle all the record locking and worrying about who made what changes to which records. Unlike working with Excel, you'll never get that annoying message that someone already has your spreadsheet open. Access was designed to be used by many users (by default 255 can use it at one time). Through the use of forms and some coding, security can be set up so that certain users can only view certain data. Of course you can get very complex with security as well and get into all sorts of levels with Read/View only or Read/Write levels.


1. Programming with Macro's and VBA

One of my favorite aspects of Access is that it allows you to program using Macro's or VBA code. When I first started using Access (back in Access 2.0), Macro's gave me my first taste of programming. While building Forms, I discovered how to make buttons work through the use of macro's. I could program buttons to open new Forms, run reports, run queries, etc. They are easy to use and give users a good taste of how the database can be transformed into an application.

As if Macro's weren't enough, Access also gives you the option to program in VBA. Once you become more experienced you will realize how powerful VBA can be and how many more options it gives you. VBA is a version of Visual Basic. It is the code that controls the application and allows all the pieces of your database to work together as one, seamless, application.



These are just a few of the reasons I like using Access. While looking back at these, I can’t help but realize a recurring theme in most of them. Access provides all the tools necessary for a very inexperienced user to create a new database and start building while at the same time providing experienced users a great environment to build more complex systems.

How great is a program that really allows you to grow your skills all within the same application?

Robby