This article is part of a series showing how to automate Office applications using VBA - Visual Basic for Applications. Previous articles have covered:
VBA by itself (VBA - The Visual Basic Working Partner)
Word and VBA (Getting Started Using VBA: The Word Working Partner)
Excel and VBA (Getting Started Using VBA: The Excel Working Partner)
Word and Excel together (Word and Excel Working Together).
Note - This article is based on Access 2010.
Access is a Microsoft's "Office" database. In recent years, SQL Server, Microsoft's "top of the line" database, has become more important and easier to use too. If you use VB.NET, you might want to consider using SQL Server as your database back end. About Visual Basic offers an introduction to VB.NET and SQL Server at A Database Centered Tutorial for Beginners. But if you want a database in the familiar Office 2010 style for small applications, Access can be your best choice.
One of the biggest advantages of Access is that everything you need is in one place. In contrast, SQL Server doesn't really have an interface. It's normally started by the operating system (because it normally runs on a server) and SQL Server database design is usually done with a different system from the one used to manage the database (because Microsoft assumes that the people who design and program a database are usually not the same people who will operate the server where the database is used). With Access, the same program is used to design, program, and use the database (although it can be used in other systems, like VBA and Excel).
If you have never used Access before, or if your only Office experience has been with Word or Excel, you may find Access very unfamiliar. The first task is to get comfortable with the Access environment. Since this article is about VBA and Access, and not just Access, we'll create the database fairly quickly so we can get to the VBA coding faster. For more on Access databases, you might want to try the Access tutorial series in About.Com databases.
When you first start up Access, you're presented with a screen that lets you ...
We're going to design a simple database from scratch to help you become familiar with the process, but we're going to rush through it in this article because the process of creating an Access database, and the relationships between tables, can be found at Databases.D106.
The database here will run on the desktop. Desktop databases can't be published to the web, and web databases don't support some desktop features, but since VBA runs on the desktop, that's what we will use.
To get started, select New under the File tab and then select Blank database and click Create.
--------
Click Here to display the illustration
--------
Access is a "relational database". That means that the entire database consists of a series of tables that link to each other using key values. A single table consists of rows and columns, much like a spreadsheet, but each column must have a name and you can't just put anything anywhere like you can with a spreadsheet. One (sometimes more) of these columns is usually designated as the key for the table.
The tables in a database are not the way the data is shown to someone using the database like a spreadsheet. They're only where the data is kept, so never try to format a database table like you might format a spreadsheet.
It's possible that your database might only have one table. In that case, the design is really easy. Just add the fields (the values that will be in the table). If your database has more than one table, then some of the columns might be designated as "foreign keys". In other words, a foreign key is a key to some other table in your database.
As an example for this article, I'm going to use something suggested to me by Ralph, a student in an in-person programming class I taught. (Hi, Ralph!) Ralph wanted to keep track of players in a table tennis tournament he helps to manage. After talking to Ralph about it, I came up with a three-table Access database design. The main table, Matches, has a record for each match played. Two other tables are used as lookup tables: Players and Tournaments.
Here's the Access Design View for the Matches table:
--------
Click Here to display the illustration
--------
Usually, it's a good idea to make the key to your table a simple AutoNumber field. Access does it automatically and it's efficient. In this case, I decided to make the key a compound key consisting of the three top fields. That way, Access will help me make sure that the combination of all three is unique.
There are several opportunities for "database normalization" here because TournamentName, Round, Table, and both Player1 and Player2 could be in related tables so they can carry other information. (For example, TournamentName might also have the date and location of the Tournament.) In this example, I'm only going to relate TournamentName, Player1 and Player2 because they can show us something more interesting.
Although, as noted earlier, you might want to store more information in the Tournaments table, I've only included an AutoNumber key field and the name for simplicity. I've added two records just to illustrate how this table works.
You can then use the "Lookup Wizard..." (under Data Type in Design View for a Table) to point to this table for the values that will appear in the Matches table.
--------
Click Here to display the illustration
--------
This works, even though TournamentName (actually defined as a Number field in Matches) is part of the compound key field for the Matches table.
You could make several other fields in the Matches table into lookup fields as well. I've also made both Player1 and Player2 lookup fields, but using the same table: Players. Here's a diagram showing how this actually works in the database:
--------
Click Here to display the illustration
--------
All the players are kept in a single Players table. I emphasized that it is just one table because it's used twice and you might think my design has two Player tables. It doesn't.
Previous articles in this "Office VBA" series have followed the Microsoft practice of calling VBA programs "macros" (even while complaining mildly about it). But the Computer Science graduate in me screams that VBA programs are not macros. In Access, the error of calling them macros creates a problem. Access can't use the term macro for a VBA program because in Access, a macro is a completely different type of programming.
In Access, you can write macros or you can write VBA programs. Macros are much more limited that VBA (so Access actually gives you a tool to convert a macro into a VBA program), but some people (not me) think they are easier to code in part because there is less that they can do and in part because Microsoft has provided a structured interface the walks you through the process for macros.
There are two other reasons why you might choose macro code even though it is less powerful than VBA.
First, macros are inherently more secure than VBA. VBA's greater power means more risk. It's almost impossible for a macro to do anything other than the specific tasks Microsoft allows. But a VBA program can do just about anything.
Second, the same macros can be used in both desktop Access databases and web based Access databases. VBA is limited to the desktop. You can use VBA for server-based databases, but if you're going to do that, you probably ought to use SQL Server and VB.NET instead.
We're going to use VBA to solve a problem that only a robust programming language like VBA can solve. Consider these records in the Matches table:
--------
Click Here to display the illustration
--------
There are a number of impossible matches shown because the same player is playing in two places at once. This is exactly the kind of thing that the computer should check for and prevent. But, unfortunately, it's very difficult for a macros to do this kind of processing. To check for this kind of error, you have to check each Player1 field to make sure that same player is not a Player2 in the same round.
Since this is the beginning of the VBA coding for this article, let's talk about VBA and Access first.
If you're familiar with VBA in one of the other Office applications, such as Word or Excel, you will find the Access VBA editor refreshingly similar. There is just one thing that you will really miss: the 'macro' recorder. (Again ... VBA programs are not macros, but that's what it's called in Word and Excel.) Evidently, the programmers at Microsoft must have decided that the Access interface was just too complicated to attempt to track with VBA code. But in Access, you're on your own. You can't do the handy trick of recording a VBA program that's 'close' to what you want to get started.
Starting with Access 2007, Microsoft has used a database engine called the ACE engine. Before that, they used something called the JET engine. (I've always thought that Access names were more clever than any other Microsoft product!) The Access 2010 ACE technology is fully backward compatible with the older JET databases. A more practical way to tell which type of data base you have is to simply look at the file qualifier:
The Microsoft Data Access Object - DAO - technology is usually used to program access databases in VBA.
Unless you have a special requirement, you usually don't have to worry about these classifications. Just write the code and use the defaults built into Access.
Before you start coding, you will probably want to decide how your code will be triggered. Another difference between VBA and Access macros is that you can't start a VBA program directly from the Access interface. Normally, you'll use an event in a form. In this example, I've simply created a new form and added a Button and a ListBox. The Button executes the VBA program and the ListBox shows the results.
--------
Click Here to display the illustration
--------
The quickest way to open the VBA editor is to select the Create tab and then click Visual Basic in the Macros & Code section of the ribbon. This opens the familiar VBA programming environment:
--------
Click Here to display the illustration
--------
After that, you're into very traditional Visual Basic coding. In this particular case, I create three RecordSet objects. Two are used to loop through the Matches database. For every Player1 in a round, the program checks to see if the same player is present as Player2. The third RecordSet is used to access the lookup table and display a name instead of a number. Here's the code:
What's Not Covered and What's Next
The subtleties of DAO programming is probably the largest single VBA subject not covered. DAO, an access layer that, like VBA itself, has roots that go back decades, has been rejuvenated by the fact that Microsoft seems to be strengthening VBA recently. Consequently, there is a lot of information at MSDN to guide you in VBA programming.
The Access Macros & Code section also displays icons for creating a Class and a Module. Access VBA is a first class programming environment and you can create technically sophisticated systems there. These are tools that you use to do that. The best advice I can give you about this is, "When you're ready to use them, you'll know what they are."
And the final subject that is given a too-quick once over here is the use of Access itself. If you're not used to it, it can be a very confusing system. For the quickest way to learn Access, I recommend Matthew MacDonald's book Access 2010: The Missing Manual.
VBA by itself (VBA - The Visual Basic Working Partner)
Word and VBA (Getting Started Using VBA: The Word Working Partner)
Excel and VBA (Getting Started Using VBA: The Excel Working Partner)
Word and Excel together (Word and Excel Working Together).
Note - This article is based on Access 2010.
Access is a Microsoft's "Office" database. In recent years, SQL Server, Microsoft's "top of the line" database, has become more important and easier to use too. If you use VB.NET, you might want to consider using SQL Server as your database back end. About Visual Basic offers an introduction to VB.NET and SQL Server at A Database Centered Tutorial for Beginners. But if you want a database in the familiar Office 2010 style for small applications, Access can be your best choice.
One of the biggest advantages of Access is that everything you need is in one place. In contrast, SQL Server doesn't really have an interface. It's normally started by the operating system (because it normally runs on a server) and SQL Server database design is usually done with a different system from the one used to manage the database (because Microsoft assumes that the people who design and program a database are usually not the same people who will operate the server where the database is used). With Access, the same program is used to design, program, and use the database (although it can be used in other systems, like VBA and Excel).
If you have never used Access before, or if your only Office experience has been with Word or Excel, you may find Access very unfamiliar. The first task is to get comfortable with the Access environment. Since this article is about VBA and Access, and not just Access, we'll create the database fairly quickly so we can get to the VBA coding faster. For more on Access databases, you might want to try the Access tutorial series in About.Com databases.
When you first start up Access, you're presented with a screen that lets you ...
- Select an existing database available to your computer
- Use a template provided by someone else (a lot are downloadable from Microsoft)
- Design a new one starting with a blank database on either your desktop or the web
We're going to design a simple database from scratch to help you become familiar with the process, but we're going to rush through it in this article because the process of creating an Access database, and the relationships between tables, can be found at Databases.D106.
The database here will run on the desktop. Desktop databases can't be published to the web, and web databases don't support some desktop features, but since VBA runs on the desktop, that's what we will use.
To get started, select New under the File tab and then select Blank database and click Create.
--------
Click Here to display the illustration
--------
Access is a "relational database". That means that the entire database consists of a series of tables that link to each other using key values. A single table consists of rows and columns, much like a spreadsheet, but each column must have a name and you can't just put anything anywhere like you can with a spreadsheet. One (sometimes more) of these columns is usually designated as the key for the table.
The tables in a database are not the way the data is shown to someone using the database like a spreadsheet. They're only where the data is kept, so never try to format a database table like you might format a spreadsheet.
It's possible that your database might only have one table. In that case, the design is really easy. Just add the fields (the values that will be in the table). If your database has more than one table, then some of the columns might be designated as "foreign keys". In other words, a foreign key is a key to some other table in your database.
As an example for this article, I'm going to use something suggested to me by Ralph, a student in an in-person programming class I taught. (Hi, Ralph!) Ralph wanted to keep track of players in a table tennis tournament he helps to manage. After talking to Ralph about it, I came up with a three-table Access database design. The main table, Matches, has a record for each match played. Two other tables are used as lookup tables: Players and Tournaments.
Here's the Access Design View for the Matches table:
--------
Click Here to display the illustration
--------
Usually, it's a good idea to make the key to your table a simple AutoNumber field. Access does it automatically and it's efficient. In this case, I decided to make the key a compound key consisting of the three top fields. That way, Access will help me make sure that the combination of all three is unique.
There are several opportunities for "database normalization" here because TournamentName, Round, Table, and both Player1 and Player2 could be in related tables so they can carry other information. (For example, TournamentName might also have the date and location of the Tournament.) In this example, I'm only going to relate TournamentName, Player1 and Player2 because they can show us something more interesting.
Although, as noted earlier, you might want to store more information in the Tournaments table, I've only included an AutoNumber key field and the name for simplicity. I've added two records just to illustrate how this table works.
You can then use the "Lookup Wizard..." (under Data Type in Design View for a Table) to point to this table for the values that will appear in the Matches table.
--------
Click Here to display the illustration
--------
This works, even though TournamentName (actually defined as a Number field in Matches) is part of the compound key field for the Matches table.
You could make several other fields in the Matches table into lookup fields as well. I've also made both Player1 and Player2 lookup fields, but using the same table: Players. Here's a diagram showing how this actually works in the database:
--------
Click Here to display the illustration
--------
All the players are kept in a single Players table. I emphasized that it is just one table because it's used twice and you might think my design has two Player tables. It doesn't.
Previous articles in this "Office VBA" series have followed the Microsoft practice of calling VBA programs "macros" (even while complaining mildly about it). But the Computer Science graduate in me screams that VBA programs are not macros. In Access, the error of calling them macros creates a problem. Access can't use the term macro for a VBA program because in Access, a macro is a completely different type of programming.
In Access, you can write macros or you can write VBA programs. Macros are much more limited that VBA (so Access actually gives you a tool to convert a macro into a VBA program), but some people (not me) think they are easier to code in part because there is less that they can do and in part because Microsoft has provided a structured interface the walks you through the process for macros.
There are two other reasons why you might choose macro code even though it is less powerful than VBA.
First, macros are inherently more secure than VBA. VBA's greater power means more risk. It's almost impossible for a macro to do anything other than the specific tasks Microsoft allows. But a VBA program can do just about anything.
Second, the same macros can be used in both desktop Access databases and web based Access databases. VBA is limited to the desktop. You can use VBA for server-based databases, but if you're going to do that, you probably ought to use SQL Server and VB.NET instead.
We're going to use VBA to solve a problem that only a robust programming language like VBA can solve. Consider these records in the Matches table:
--------
Click Here to display the illustration
--------
There are a number of impossible matches shown because the same player is playing in two places at once. This is exactly the kind of thing that the computer should check for and prevent. But, unfortunately, it's very difficult for a macros to do this kind of processing. To check for this kind of error, you have to check each Player1 field to make sure that same player is not a Player2 in the same round.
Since this is the beginning of the VBA coding for this article, let's talk about VBA and Access first.
If you're familiar with VBA in one of the other Office applications, such as Word or Excel, you will find the Access VBA editor refreshingly similar. There is just one thing that you will really miss: the 'macro' recorder. (Again ... VBA programs are not macros, but that's what it's called in Word and Excel.) Evidently, the programmers at Microsoft must have decided that the Access interface was just too complicated to attempt to track with VBA code. But in Access, you're on your own. You can't do the handy trick of recording a VBA program that's 'close' to what you want to get started.
Starting with Access 2007, Microsoft has used a database engine called the ACE engine. Before that, they used something called the JET engine. (I've always thought that Access names were more clever than any other Microsoft product!) The Access 2010 ACE technology is fully backward compatible with the older JET databases. A more practical way to tell which type of data base you have is to simply look at the file qualifier:
myDatabase.mdb - JETmyDatabase.accdb - ACEThe Microsoft Data Access Object - DAO - technology is usually used to program access databases in VBA.
Unless you have a special requirement, you usually don't have to worry about these classifications. Just write the code and use the defaults built into Access.
Before you start coding, you will probably want to decide how your code will be triggered. Another difference between VBA and Access macros is that you can't start a VBA program directly from the Access interface. Normally, you'll use an event in a form. In this example, I've simply created a new form and added a Button and a ListBox. The Button executes the VBA program and the ListBox shows the results.
--------
Click Here to display the illustration
--------
The quickest way to open the VBA editor is to select the Create tab and then click Visual Basic in the Macros & Code section of the ribbon. This opens the familiar VBA programming environment:
--------
Click Here to display the illustration
--------
After that, you're into very traditional Visual Basic coding. In this particular case, I create three RecordSet objects. Two are used to loop through the Matches database. For every Player1 in a round, the program checks to see if the same player is present as Player2. The third RecordSet is used to access the lookup table and display a name instead of a number. Here's the code:
Private Sub btnDoTheCheck_Click()Dim db As DAO.DatabaseDim rs As DAO.Recordset' m for match, p for playerDim m_rs As DAO.RecordsetDim p_rs As DAO.RecordsetDim sql As StringSet db = CurrentDb()sql = "SELECT Matches.Round, " & _"Matches.Player1, " & _"Matches.Player2 FROM Matches " & _"ORDER BY Matches.Round ASC"Set rs = db.OpenRecordset(sql, dbOpenForwardOnly)Dim currentRound As IntegerDim currentPlayer As IntegercurrentRound = -1currentPlayer = -1Do While Not rs.EOF' Capture Player1 to check for dupscurrentPlayer = rs!Player1If currentRound = rs!Round Then' Still checking this round' Check all the matches in this' round against the current playersql = "SELECT Matches.Round, " & _"Matches.Player2, Matches.Table " & _"FROM Matches " & _"WHERE Matches.Round = " & _CStr(currentRound)Set m_rs = _db.OpenRecordset(sql, dbOpenForwardOnly)Do While Not m_rs.EOFIf currentPlayer = m_rs!Player2 Thensql = "SELECT Players.PlayerName " & _"FROM Players " & _"WHERE Players.ID = " & _CStr(currentPlayer)Set p_rs = db.OpenRecordset(sql)lstErrorList.AddItem (CStr(p_rs!PlayerName) & _" has a conflict in Round " & _CStr(currentRound)) & " and table " & _CStr(m_rs!Table)p_rs.CloseEnd Ifm_rs.MoveNextLoopm_rs.Closers.MoveNextElse' New round' Capture Round to check for a round changecurrentRound = rs!RoundEnd IfLooprs.CloseEnd SubWhat's Not Covered and What's Next
The subtleties of DAO programming is probably the largest single VBA subject not covered. DAO, an access layer that, like VBA itself, has roots that go back decades, has been rejuvenated by the fact that Microsoft seems to be strengthening VBA recently. Consequently, there is a lot of information at MSDN to guide you in VBA programming.
The Access Macros & Code section also displays icons for creating a Class and a Module. Access VBA is a first class programming environment and you can create technically sophisticated systems there. These are tools that you use to do that. The best advice I can give you about this is, "When you're ready to use them, you'll know what they are."
And the final subject that is given a too-quick once over here is the use of Access itself. If you're not used to it, it can be a very confusing system. For the quickest way to learn Access, I recommend Matthew MacDonald's book Access 2010: The Missing Manual.
Source...