Final Project Review

I certainly have learned more about Access 2007 and databases in general. I believe I will be using it on the job in the next few months as the main financial reporting tool for my department.

I realize that if I am going to be using Access for reports at work I will need to go over the things I’ve learned in this class in quite a bit more depth and detail. I had a hard time remembering tasks I had completed in prior chapters when it came time to prepare my final project. I’m sure it will come more easily when I use Access regularly.

Chapter 15 - Using Visual Basic in Access

Here's my first Visual Basic Sub Procedure in Access.

Here's what happens when I open the module click on Run Sub/User Form, select the sub procedure I want to run and click run. Obviously my sub procedure opens the G3170 table.

Here's a macro that does the same thing as the sub procedure in my module, only all I have to do is double click it in the Navigation Pane to open the G3170 table.

The macro makes a whole lot more sense to me and is easier to use.

Our text says that modules are a collection/organization of procedures which can be used for a specific purpose. I certainly need a set of related procedures to manipulate my inception to date data for printing out the monthly reports. However, the Access Visual Basic commands seem much less intuitive than the Excel Visual Basic commands I use. In addition, Access doesn't have a macro recorder similar to the one in Excel, which records keystrokes automatically.

I will most likely use Excel to manipulate the inception to date data and then import it into Access to make good use of its excellent report formating capabilities.

Chapter 14 - Macros

Here's the first step of my simple Access macro. I was trying to open my Inception to Date (ITD) table and then sort by first org, then fund, and finally by account group. These actions are relatively simple to perform in datasheet view by selecting the first column heading, holding down the shift key and then moving the cursor to the second and third columns. While continuing to hold down the shift key you just click the down arrow on one of the selected columns and choose sort smallest to largest.

Here's the second step of the macro. The table opens just fine.

In the third and final step I was not able to figure out which Access macro command selects columns to sort. I was only able to sort by the first column using the macro.

There doesn't appear to be a macro recorder in Access like the macro recorder in Excel. Here's what the macro looks like in Visual Basic using the macro recorder in Excel.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 11/25/2007 by University of Alaska
'
' Keyboard Shortcut: Ctrl+h
'
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

The macro I'd really like to create would take the VistaPlus inception to date report, add two columns on the left, copy down each of the grant numbers in column A, column down each of the fund numbers in column B, sort by org number in column C, delete any row without an org number, then sort by grant, fund, org and account code. The macro would then insert a column to the right of account codes, look up and return the account group, then delete the account codes. The inception to date data would then be ready to be used for the Vista Plus ITD Report in my Chapter 14 database.

Changing Data in Access Reports

Once you've spent all this time to get your report formats just right, you'll want to be able to use the report more than once for different sets of data at different times/dates. This is very important for my use of Access.

Here's what I found about changing the data used in a report through Access Help.

Change the record source of the report

First open your report in Design view.
Select the whole report by clicking on the uppermost lefthand box (just to the left of the ruler).
If the property sheet is not displayed, press F4 to display it. (Or you can right click the mouse and choose properties.)
In the drop-down list at the top of the property sheet, click Report.
In the property sheet, click the Data tab.
In the Record Source drop-down list, select the table or query that you want to use for a record source, or click to display the Query Builder.
When you go back to report view you'll see the new data in your report.

Chapter 13 - Avoiding Harmful Attacks

I’m fortunate to be working at the University of Alaska. Although my computer data is shared with many other users, I am fairly well protected from harmful attacks on my computer. We know all too well what devastating effects a worm or virus can have and how quickly it can spread.

My individual computer has multiple firewalls which block worms and viruses. My privacy setting for internet options was so high I was unable to post anything to Blogger when I first started this class. I also always have pop-up blocker turned on, which sometimes gets in the way of performing certain tasks of my job.

I never open any e-mails from people I do not personally know. If I accidentally open one of these e-mails by accident I immediately delete it, without opening any attachments.

UAF has software called Pointproof that places suspicious looking e-mails in quarantine for 14 days after which time they are automatically deleted. You wouldn’t believe the amount of absolute junk and porn this software detects, 172 for yesterday alone!

We also have a program called Spybot Search & Destroy which automatically scans my computer system every day for spyware and all other threats.

At UAF we often get warnings about phishing scams from our Procurement Office. There are so many employees here we often get reports and advice about specific phishing scams which have affected one of us. I never give any personal information to any e-mail request.

I also have access to a great OIT (Office of Information Technology) Support Center and on-site computer techs for my specific college if I ever have any problems or suspect that I’m in harm’s way.

Chapter 12 - Creating A Switchboard

Here's the switchboard I created for people in my department who don't know how to use Access very well (even less than me). Hopefully, they will be able to open the Inception to Date database and immediately choose which report they want to print. I didn't see instructions in the text for having the switchboard run automatically when the database is opened.

Chapter 11 - Importing and Exporting

I would like to use Access for my department’s inception to date reports because Access, unlike Excel, has so many wonderful reporting features. One would think this would be a fairly simple process, right?

The best source of my inception to date data is an SQL based program called QMENU. When I save the QMENU files as text documents I am not able to import them into Access because there doesn’t seem to be a set delimiter to the data. I am also not able to import them into Excel as text files. When I save the QMENU files as html documents I am able to import them into Excel, but not into Access. Access shows the html file as 11 different tables, none of which has the complete data I need. So I seem to be stuck with using Excel to manipulate the QMENU data.

Another of the obstacles I've encountered when trying to produce inception to date reports in Access is in the sub-totaling of data. I need three levels of sub-totaling for these reports, at the account group org and fund levels. The Microsoft Office pivot tables normally work extremely well for sub-totaling on multiple levels. However, because the pivot table command doesn't work well in Access I need to create pivot tables in Excel. Unfortunately, these Excel pivot tables do not export into Access well. The column headings which are text in Excel, i.e. Sum of BUDGET, show up as error messages when I try to create an Access link to the Excel pivot tables. So I wind up spending more time manipulating the data in Excel so that I can just copy it into Access.

This would be very time-consuming but an okay solution if I were just creating a one-time report. However, I was hoping to be able to somehow have my Access report link to the Excel/VistaPlus/QMENU data which changes weekly.

I was able to export the Access data back into Excel and Word successfully once I remembered to save the Access 2007 databases as Access 2003, because that's the version of the Excel and Word software I have on my computer now.