If you're new to programming, Access Modules are going to take you to the next level of database work. If you're an old hand at coding in FoxPro, PERL or SAS, you probably know that modules are your ticket to getting under the hood of Access into the functionality you're used to getting from those programs.
Modules mean you're no longer limited to the query functions Access provides for queries, and you're no longer forced to depend on the Import Wizard to dive into text files.
In this article we'll see a module that takes a nightmare text file with no delimiters and data spread over multiple lines and suck it into a database for analysis. You can't do THIS with the Import Wizard!
But first we'll warm up with an introduction to the concept of modules for those who haven't looked into them before.
A quick 'Hello'
Launch Access and open the exercise file (Henderson.mdb) or open any Access database. The exercise file can be downloaded from the NICAR Web site, www.nicar.org. Click on the Modules tab and hit the 'New' button. You may see a couple of lines of text like this:
Option Compare Database Option Explicit
Don't worry about what they mean for now. UNDER those lines, type this:
Sub SayHi()
And press 'Enter.' Notice that as you move off the line, Access colorcodes the keyword 'Sub' (for subroutine--a kind of program), adds a line setting it off, and automatically adds another line:
End Sub
These are the starting and ending points of our program. All our program code will go between them, so type these lines there:
Dim theName As String theName = InputBox("What's your name?") MsgBox ("Hi there, " & theName & "!")
Modules are your ticket to getting under the hood of Access
If you have trouble, make sure you didn't add spaces to 'theName', 'InputBox' or 'MsgBox' - these must be single words. Notice the way Access follows your typing and nudges you in the right direction at every turn. If you mistyped or moved off a line before it was finished, Access complained. Menus appeared and guided you at various points.
Now, to run your program hit F5 (or use Run to/Continue, or the arrow on your toolbar).
It may not be the most useful program in the world, but you've already declared a variable, assigned it a value based on user input, and given feedback to the user. You'll use these basic building blocks time and time again in any programming environment. If you're intrigued and want to get more familiar with Visual Basic for Applications (VBA) before continuing, I suggest the introductions and tutorials at www.athree.com/modules/index.html
Subroutines and functions
Let's talk quickly about subroutines. As in the example above, they run independently and leave no trace behind when they're finished. We're going to stick to subroutines here because they're easier to explain. But you should know that you can also create functions to supplement the Access built-in functions, like Trim() and Substring(). The coding procedure is very similar, and once created in a module, the functions become available in all your Access queries, just like the built-in functions. For examples of Functions check the Neatcode link from Microsoft (see greybox on the side) which includes many helpful and instructive functions you can import into your Access files. Add your own code, and you'll have the foundation of a library of custom functions available whenever you query your data.
Newsroom database pros often build such a library to solve recurring problems like formatting text files for publication in a particular front-end system, a dependable way of calculating age from date of birth, or extending the Proper() function to do a better job of normalizing capitalization.
Importing tricky data
Now let's get into a meatier project, where we'll learn to use code to scan through a text document for fields to import to a database. This is the kind of thing you would have done in a FoxPro .prg file way back in 1995 when I joined NICAR.
Make sure pacer.txt (the text file available as part of the download from the NICAR Web site) is in the same directory as Henderson.mdb and open the Access file, if you haven't done that already. Open pacer.txt in a text editor like Notepad, make sure Word Wrap is off and take a look. What a mess, huh? We need information on cases in the table called Bankruptcy. The saving grace for the text file is that at least we can predict where the information, such as debtors' names, will fall within each line so we can pull them out with the Access MID() function.
Hit the Modules tab, select Bankruptcy and hit the 'Design' button to see the code. Examine the ImportBankruptcy() subroutine and run it with F5 to see that it does in fact fill the Bankruptcy table with values from the file.
A good place to start a job like this is not programming at all but documentation. Notice that there are lines in green that serve only as reading material for someone looking at the code - to do this, you precede the line with a single quote mark. It's called 'commenting' and it's what we all do best. It's a good idea, to get started on a complicated coding job, to just go ahead and write what you want to do, step by step, in comments. For this job, it would look like this:
'Declare variables we need for all the 'jobs below 'Open the text file we want to import 'Read each line into a text variable and 'note the line number 'When we get to a line with fields for 'our table, pull them out into variables 'Put together an SQL statement to 'update the table 'Run the SQL statement.
Now you can start coding each task separately between the comments, and you've broken the job down so it doesn't seem quite so intimidating.
Inside and out
The cool thing about modules is that they let you automate tasks you could do anyway in the point-and-click part of Access, but also let you delve into areas beyond that.
For instance, part of what you're doing in this module is creating and running an SQL query, an Append query, that you could have produced in the query grid or in SQL View.
You do that with these lines of code:
Dim db As Database Set db = CurrentDb
(code making an SQL statement and assigning it to the variable 'theSQL')
db.Execute theSQL
But at the same time modules give you the power to create and manipulate text files, and otherwise interact with Windows in ways you can't with interactive Access. We open our import file and read it in line by line with this code:
Dim myFile As Integer Dim myFileName As String
myFileName = "pacerfile.txt" myFile = FreeFile
Open myFileName For Input As #myFile
To open a file, you need to give it a numeric 'File Handle' and the FreeFile function assigns it a free handle automatically so you don't need to worry about it. If you wish, you could accomplish the same thing this way:
Open "c:/data/uplink/pacerfile.txt" For Input as #1
"For Input" just means that you're going to read stuff from the file into your program. See Help on the Open statement for more options.
Once we have the file open, VBA doesn't give us a straightforward way (that I know of) to move to a particular line in the file. But the Line Input function will read a new line into a variable every time we call it, so we deduce the line we're on and keep track of it in the 'linecount' variable.
For efficiency you'd probably want to stop the Line Input statement after it reaches Line 8 - since we don't need to read any more of the file at that point - but I left it as is to make the code more portable for your own purposes.
Once we've gathered the data we need into variables, we parse those variables into an SQL statement and run it. I've included just one file, with fictitious names, but you could easily loop this code through all the files in a directory to populate a database.
Bugging out
The MsgBox function from our first exercise is a great way to handle the inevitable bugs that pop up when you write any kind of code. Here we've commented out a section that shows us our SQL in a message box, rather than executing it, so you can be sure it's OK before you run the program.
That's how I figured out I need to surround the text values with single quotes, and the dates with pound signs, in order for the SQL to successfully transfer the values into the table.
If something's going wrong with your program, a message box here and there can report the value of various variables that might be causing you trouble and will save you hours of agonizing.
Moving along
Now that you've been titillated by the possibilities of modules and VBA, try following some of the links with this article and check out some of the sample modules supplied by Microsoft in Neatcode, free for the downloading. Take a look at some of the online tutorials and take advantage of your Help files to learn about specific functions available only in VBA. And the NICAR-L crowd includes masters of VBA ready to respond when you get stuck. So go ahead, code yourself silly.
Tim Henderson can be reached by e-mail at thenderson@herald.com