Macros or Visual Basic in Access?

If you are starting out with a database project and just learning how to create Microsoft Access solutions, one key question is: Should I use macros or Visual Basic (VBA)? We'll just go over some pro's and con's, you make the right choice for your situation.

Using Macros
Pros

• Easy to learn
• Support for conditional statements (if this is true, then perform this action, otherwise do this other thing)
• Easy for users to launch directly from the database window

Cons

• Unable to support advanced functions (writing to files, reading files, manipulating form objects, complex conditional statements, recordset manipulation)
• Cumbersome to migrate to VB if you choose to eventually using VB instead of macros (converting to VB is initially a pain, and so is maintaining both macros and VB simultaneously in many cases)
  

Using Visual Basic
Pros

• Ability to offer many powerful features in your forms and database (including access to Windows APIs for things like retrieving the NT user name, manipulating window appearance, position, etc)
  
• Ability to create extensive error handling to reduce user frustration and handle exceptions (e.g, when a user can't access a given network file share, taking an alternate course action instead of just stopping your processing)
• Manipulate strings and other variables (e.g., change a name like "Johnson, William" to "William Johnson")
• Easier to debug by inserting breakpoints, seeing what the current value of a variable is, and adding complex conditional statements (if...then)
  

Cons

• More upfront time required to learn how to use VB
• If you don't get formal training, more upfront frustration until you master the language

Certainly there are additional items that could be added to each of the categories above (feel free to comment!), but this condensed list is all I could muster, and should give you a decent foundation for making the right decision.

A new twist on databases?

I've been waiting for this to happen, I just expected it to come from Google rather than a startup. But here it is, hosted databases similar to what we're seeing come out of Google and Microsoft for their respective documents/spreadsheet apps online.

I've just started playing around with it, I'll post more as I come to conclusions.

www.trackvia.com

First reactions:

-great concept

-offline access/synchronization is a big need

-security will be an interesting issue...databases generally hold more important information than just term papers

Creating Application Frameworks for Multiple Projects

Once you create one Access database project, you're likely to find another reason to build one. And with each one you create, you'll likely evolve your techniques and refine each new database. A few tips that have helped me in the past, that you don't often find in the MS Access books or help sites:

1) Create a reusable code library. Create an empty database that will serve as your code library. Create a VBA reference to that database in your new project database (open the VBA Editor, click on Tools...References, then locate your file using the Browse method). Then, for each generic function you create that might be used in more than one project, put it in your code library database. Why is this helpful? For one thing, as you improve the VBA functions in your code library, it becomes easier for each of the separate database projects you've created to use that improved code without updating each database. This is especially true if you deploy front-end databases to multiple users. You can place the code library database on a central file server, then reduce the amount of maintenance you need to do on each separate front-end copy.

2) Create a reusable application framework. That is, as you begin to build each latest and greatest database, you'll likely find they have commonalities you can begin to standardize. Whether it's menu design or a popup reminder form, you'll have items that you use over and over again. Those are the ones you'll want to put into a framework template (for lack of a better name). Then when you go to create your newest project, you've got n% of the project ready to go.

3) If you are creating a database for multiple users where each user has a separate front-end, consider placing important configuration data in the back-end database. Some configuration information you might store in a local table (user-level preferences as an example), whereas you will want to put configuration data in a remote linked table if you may need to change it remotely or want the configuration to be the same for all users. For example, the output location of reports, or a parameter that determines whether you want logging turned on or off on the front-end databases.

It was only after creating about 20 different database projects that I came to use this approach, and it saved a lot of time maintaining front-end database copies and starting a new database project. The lessons really served as the foundation for UI Builder, which is intended to give Access developers a ready-made framework to create their own projects so they can focus on important things like creating user-friendly forms and sound database architectures. But whether you use something like UI Builder or create one for yourself, creating a reusable framework is a must if you're going to develop lots of Access databases.

We'd love to promote Janet...but who's going to keep up that database?

Being an innovative member in your org can be a double-edged sword. Watch out for the edge you might not anticipate. Namely, where being indispensable because of that neat time-saver of an application you created turns into the reason people hesitate to promote you. You probably have a plus sign in the column labeled "Helps Drive Efficiency" or "Increases Revenue,", but unless you have someone capable of carrying on your work, you might get a question mark in the "Able to Backfill" column. If you're an IT worker, probably not as much of an issue. But if the standard expectations of your core role don't include any sort of MS Access knowledge, your employer may be in a tough spot.

Here's why. Your standard job role (that is, what your peers are expected to do) doesn't include a requirement for MS Access expertise, VBA knowledge, etc. So the same reason they are thinking of promoting you (you have capabilities beyond your current position) is also the challenge in finding your replacement. Namely, do they have to modify the job posting to indicate the person needs to know MS Access? And if so, do they pay them more or give them a title like "Business Analyst"?

Another potential challenge, albeit a better problem to have, will be that you do get that promotion, but still have to maintain your application. It might sound like a good compromise at first, but as soon as your new job takes hold of you, those little enhancement requests and bug fixes start to lose their luster.

So what can you do?
1) Remember that what you create needs to live on without you. The less "administrator" intervention required for it to continue operating when you've moved on, the better. That means solid error handling and messages, automation, and following best practices (naming conventions, VBA conventions, commenting your code, etc) so someone else can pick up your work and have a chance of being able to fix bugs without calling you.

2) Start cross-training. Find one or two people in your org that are sharp enough to start picking up what you've learned. That might mean giving them a crash course in VBA, table design, etc. While it might seem like it will make you less indispensable, it probably won't. It will secure your future, and let you take long vacations without worry.

3) Consider replacement. Is your solution available off-the-shelf for a reasonable price? Do you have a professional development org that can take up what you've done and replace it with a more robust solution? If so, think about building a business case to show the benefit of replacing your solution. Why? It shows you have business savvy beyond just increasing efficiency with an MS Access database, gives you an opportunity to dive into financial business cases, and may save you future headaches if you're stuck maintaining a database you created seven years ago and four promotions back (I've been there, it gets uglier and uglier to explain to a VP why you're doing VBA instead of your real job).

As always, happy developing!

Excel, Access, SQL, oh my!

As a professional who's full time job isn't creating software, you're likely faced with the question of whether to use Excel, Access, or something else for that project you're beginning or mulling over in your head. There is no simple answer, but the table below is intended to help compare and contrast your options.

Use	Spreadsheet (Excel)	Desktop Database (Access)	Server Database (SQL Server, MySQL) with Access/VB user interface
Validate basic user input (e.g., for a single field/cell	Yes	Yes	Yes (probably overkill by itself)
Complex user input validation or events (if field X = Y, and field Z is empty, perform some action)	Challenging to accomplish	Yes	Yes
Data arranged in a few columns without much repetition	Yes	Yes	Yes (probably overkill by itself)
More than 65,535 rows/records	No	Yes	Yes
Simple cross-references	Yes	Yes	Yes
Complex data analysis/queries	Limited	Yes	Yes
Events based on user actions (when user exits a field/cell do X, when they mouse over a field/cell do Y)	No	Yes	Yes
Multiple users, but generally accessing data at different times	Yes (but conflicts can occur)	Yes	Yes
Multiple users, accessing data at the same time	No	Yes (generally slow)	Yes
Frequent mass-update operations	Challenging to accomplish	Yes (can be slow if resource intensive)	Yes
Multiple user forms	No	Yes	Yes
Mail Merge with Word	Yes	Yes	Yes
Reporting	Challenging to accomplish	Yes	Yes
Automated backups	Challenging to accomplish	Challenging to accomplish	Yes
Record-level auditing	No	Challenging to accomplish	Yes
Ability to hide the inner workings of your project from the user	Challenging to accomplish	Yes (MDE files)	Yes
Level of Effort/Difficulty	Low	Moderate-to-High	High

User Level Access Menus

So after years of reading that people want Microsoft to provide user-level Switchboards, and seeing that Microsoft hasn't done so in 2007, my company decided to release one. This is really exciting news for anyone that has, or will have to, create a database that will be used by more than one user, and where those users have different needs in terms of forms and data access. It was a lot of fun to work on the project and we've gotten some great feedback so far from our customers (that's always the most rewarding part, isn't it!).

Free online tutorials for Access

Here is a site with some fantastic free tutorials on Microsoft Access. I'm a largely visual person, so these videos hit the spot when it comes to picking up techniques to make better Access applications.

http://www.datapigtechnologies.com/AccessMain.htm