Sunday, December 9, 2007
Tuesday, November 20, 2007
Databases enable you to store information in efficient ways such that you minimize the amount of replication (within the database) for any given data element. Trackvia, similar to Excel, makes you repeat the same information with each record you might want to store. For example, if you have a contact database, you must repeat the same information (e.g., customer name, address) for each related contact.
The leap from where Trackvia is to where they need to go isn't that far, but I'd hold off any serious purchase decisions until they mature. And by then....Google or Microsoft can help them enhance the software after the acquisition.
Monday, October 22, 2007
- 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
- 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
- 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)
- 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.
Wednesday, October 3, 2007
I've just started playing around with it, I'll post more as I come to conclusions.
-offline access/synchronization is a big need
-security will be an interesting issue...databases generally hold more important information than just term papers
Sunday, September 23, 2007
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.
Wednesday, September 5, 2007
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!
Monday, August 27, 2007
| 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 |
|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|
Tuesday, August 14, 2007
Tuesday, August 7, 2007
Consideration #1: Cost/Benefit
This is key for any undertaking at work, and here it is especially important to weight the costs of your options versus the net benefit you expect. A back of the napkin calculation should suffice:
Option A: Do-It-Yourself
Option B: Contract Development
There may be no clear winner, and it will also depend on how many great new gadgets you plan to add to your database app. If it's just a few simple features, Option A may do just fine. If you are planning to do some fairly comprehensive automation, make use of the Windows APIs in many ways, or even need to depart from Access given the complexity, B may be a better option. But at least you can make a rational decision with this being one of the data points.
Consideration #2: Commitment
This is a big one, whether you do it yourself or outsource. The question of commitment is, in many cases, an underlying indicator of the benefit you expect when the project is complete, but also speaks to how willing you are to go to bat for your project if things start to take longer or get more expensive than you expected. If you do choose to outsource the work, how committed will you be to hound the developer, if needed, to get it done right and on time? Unlike a do-it-yourself project where you can generally just walk away if things get too hectic or difficult, with a contractor, you're making a commitment to pay even if you get tired of the project. And believe me, no matter how simple the project seems, they generally never are, and it will take a good deal of guidance on your part to get the right end product, even if you can get an all-start contract developer (and there are sure to be some out there). So assess your level of commitment one way or another.
Consideration #3: Who Do You Call?
You have a lot of options out there, fortunately, when it comes to picking a contract developer for Microsoft Access. I won't recommend any since I don't keep abreast of that sort of thing. But you can easily do a search and find several sites that can take you through the process of creating your project scope, requesting bids, etc. There are many, many freelance contract developers out there, and their quality will run the spectrum from downright awful fly-by-night sort of operations to really world-class folks. Some of the better contract developer clearinghouse websites will help you evaluate prospective bids by letting you look at past comments and ratings of people who are bidding on your projects. Referrals should definitely be treated seriously, just like picking someone to paint your house or help with the birth of your child!
So what is a recommended process to acquire, manage, and complete a contract development project? Here is my recommendation, comments on other, better methods also welcome!
1) Make sure you've considered #1 through #3 above.
2) Define your requirements. Focus on exactly what you expect the final product to do from a functional perspective. That means "what the application should do." If you start talking about how you need a VB function to get data from a new table XYZ and put that in a query to produce a report, you're focusing on the wrong level. Document (definitely document!) your requirements thoroughly, and focus on what you expect to be able to do when the project is complete. I'll be posting an example or two in the near future. The more complete and clear your requirements are, the less time you'll spend during the project taking advil as you go back and forth on what something should do or what something means. For those of you that are Seinfield fans, remember the episode where Jerry gets his kitchen cabinets replaced and the contractor asks him a question every five seconds? And when Jerry says "you figure it out!" he ends up with a completely different result than what he wants. Don't let your contractor be that guy, and don't be Jerry.
3) Get multiple bids. Check references. Confirm the scope of the project, the billing rate or fixed fee, and sign a contract! Make sure the contract includes the design, development, testing, progress updates, and some period of post-release support or warranty.
4) Monitor the contractor's work closely, if you don't include it in the contract in the beginning, ask for regular progress updates and samples of what their doing. It ensures you don't fall victim to "throw-it-over-the-fence" contract work where the end product is vastly different than what you wanted because you assumed your contract knew what you meant, and your contract assumed the end product is what you wanted.
5) Avoid scope creep...on both sides. Don't start asking the contractor to add X,Y, and Z if they weren't in your original scope/requirements, unless you're willing to pay for the extra work. And don't let the contractor expand scope without verifying it's all right with you, and agreed upon a fee (if any). You don't want to be on one of those long cab rides from the airport where you're sure there is a shorter way.
6) Make sure the end product is what you asked for, and what you and the contract agreed to during the course of the project. If not, many contract clearinghouses have ways for you to dispute billing.
7) Recommend your contractor, if they were good. Otherwise, let other's know you weren't satisfied, and why.
If you choose to use a contractor, you can often get a better, cleaner end product than what you might have created with the "learn-as-you-go" method. But it takes more effort than just finding the contractor and paying them for the final software! In any case, good luck!
Wednesday, August 1, 2007
If you don't have the money to buy an off-the-shelf product that contains the right tools to ensure user activity and application processing is tracked and timestamped, take the effort to incorporate similar functionality into your own applications. A little story for your entertainment:
About eight years ago I took on a position at a local business, a temporary position until I went to grad school (well that never happened, but that's another story). My first full day was a training on how to process service order records that were sent on to telephone carriers on the Eastern seaboard. My primary role would be to spend eight hours a day performing this laborious twenty-five plus step process over and over again to take raw data, put it into Excel, then output it into other formats for processing. I spent some time with Microsoft Access and got that process down to a two click activity, relying on user input only where it was critical and couldn't be readily automated. These records, by the way, effected the way 9-1-1 calls were routed, and emergency services were dispatched. So you can be sure I had every single step audited: timestamp, user, machine, what was happening to the data, before/after snapshots, etc. To my knowledge, that little database is still processing about 10-20% of the 9-1-1 records on the Eastern seaboard. Scary, but I'm confident it's completely error free.
My point to you, the reader, is that whether it's life critical or business critical data, be sure you've covered your bases, and your behind, when you decide to create Access databases that will do more than just store addresses and names. I won't go into great depth on how to do it, but some general guidance:
1) Make sure you get the handy functions available from Dev Ashish's MVP site: API to retrieve a user's network username, API to retrieve the local machine name,API to retrieve the user's operating system name.
2) Create a function that you can call from forms and other VB functions that will handle the logging events for you. I typically write to either a local table, linked table, or file depending on whether it's a multi-user Access application or sitting on someone's PC. Alternatively, you can use a logging API like what comes in UI Builder for Access, instead of creating your own.
3) Insert the logging function wherever you need to track user behaviour, errors, or other system events. This could be as granular as when the user logs in, changes a record, etc. Or it can be just for errors.
No only is it a great way to protect yourself from potential liability, help debug your apps, it also ensures your solution has a greater level of legitimacy and value to your organization.
Saturday, July 28, 2007
1) Determine your requirements. What is it that the end product needs to do to answer your particular problem or need? To help think through the process, you can either spend a lot of time pondering your requirements in your head, or you can whip up a few form/report prototypes and see if you've thought of everything you'll need to meet your needs.
2) Create your design. What is it that your application will need to do in the background to meet your requirements. If you've done a good enough job of thinking through what your requirements are to solve your problem, your design will show that. For example, if you know you'll need to be able to create an extensive number of reports based on dates or other data attributes, your design will be sure to take that into consideration. Similarly, if your requirement is that your application needs to be readily deployed to multiple users without a lot of time copying databases to each person's PC, you're probably going to design your application differently than if you just start creating tables and functions without a thought for the end product.
3) Start creating. Seems simple, but this is where you'll get the opportunity to test your commitment to some of the items you thought of in (1). That is, you may find that actually creating your solution to meet your requirements is harder than it seems, and you'd prefer to sacrifice a few of your requirements to make it easier to develop or maintain.
4) Test. Preferably use someone other than yourself, that may have familiarity with the problem space and desired solution, but hasn't been deeply involved in the development. Why? Because the second you started designing and creating your solution, you begin to think like a developer, not a user. You'll know intimate quirks and their workarounds, something an uninitiated user won't. Thorough testing, especially where your tester purposely tries to "break" things, will ensure you aren't inadvertently signed up to do a great deal of recurring training.
5) Repeat 1-4. The process never ends, does it!
Sunday, July 22, 2007
Rather than putting a form over a table, user driven design will help you to look at the world from a user-process perspective. You are more likely to discover you need to "merge" multiple data sources the user can view or update in a much more efficient manner. For example, while creating new orders (one table), perhaps you need to let the user quickly view key customer information (from a different table) on the same screen, and be able to make changes to your contact history with that customer (yet another table) from the same screen. All if this is missed if you just rely on the Form Wizard.
And while we’re on the topic, I’d suggest avoiding the Access Switchboard too. Use something a bit more friendly for users.
Saturday, July 21, 2007
Here is the link:
A few notes:
- The runtime version of Access is terrific, but you need to be much more diligent in your error handling (at least that was the case in previous versions, I'm sure it's the same with 2007). If your application encounters an unhandled error, it will terminate.
- The database window will never appear to your users, so you have to be creative in how you expose reports, queries, and data to your users.
- The nice toolbars for sort, filter, etc aren't visible to the user. Again, I haven't had time to check out the 2007 version, but that was the case in earlier releases. So it means you have to create form buttons to allow your users to perform the same actions.
Friday, July 20, 2007
Kathy Sierra's blog (I'd suggest looking at all of it!) is a fantastic place to consider your user experience. It's a tragedy that she's ceased posting since she received some vicious threatening messages.
Wednesday, July 18, 2007
When I say “invest,” of course I mean time and energy. The first answer is, as much as you feel like. The second answer is, as much as makes sense. This advice holds true for any business professional, but is particularly useful if you are engaged in a project like creating a useful database application that can save time: be sure the cost/benefit works out in the right direction. If you are making $60K per year as a program manager, and you begin to focus more on creating a database to help your team save time, make sure you’re still giving your company $60K worth of effort. If you can justify the solution you are creating into the $60K, and many times you can, then proceed, my friend. But if you find yourself spending more and more time learning VB or SQL, and less time successfully delivering on Job Requirements A through Z, no one will benefit.
I’d recommend you do the following, really for any professional project you undertake, but especially one that doesn’t fit your “core responsibilities”:
Get your manager’s buy-in
Realistically evaluate what it may take to get to the end product. As a beginning, this will be hard to do without a solid amount of experience, so figure in between two to six months to be pessimistically generous.
Realistically evaluate what benefit you and your organization will get from the end product. If (3) is less than (2), you’d best do the project on your own time.
Sell the value upwards. Make sure what you create, including a return on investment figure, is publicized to your manager. Don’t assume your manager will see the value without some help, smart as they may be.
The upshot is, make sure you focus on your core responsibilities, and don’t let a project take on a life of it’s own to the detriment of your career. Do you have experiences related to this to share?
Sunday, July 15, 2007
1) Good Form. If you’ve ever played piano, golf, or even used a pencil, you know it’s important to practice good form…it reduces future physical or mental pain.
2) Minimize re-work. If you start a project without enough consideration for design, you’re likely to pay for it later in re-work or lack of functionality.
3) Create happy users, avoid extensive training. If your application will be used by anyone other than yourself, the last thing you want to do is spend a great deal of time training each new users on the idiosyncrasies of your solution. From making sure the field names are well labeled, creating intuitive forms with field arrangements that map to how a user thinks, to capturing all the data needed for someone to carry out their duties, doing a bit of design up front can save time, energy, and frustration in the long-term.
So what is the secret to good design? Well, I won’t pretend to be the expert, there are hundreds of books written on the topic. But for me, it is the same as creating a good presentation or letter…think like your audience. In this case, not you as the user, but someone who will wasn’t involved in the thought process for the design, but needs to understand how to use your application nonetheless.
How much time you actually spend designing is very subjective and dependent on how complex your project is going to be. But do put the horse before the cart when it comes to design.
Tuesday, July 10, 2007
Think of learning VBA in Access as the equivalent to learning a new spoken language. Sure, you can't order a sandwich and bottled water from a sidewalk cafe with VBA, but it is a great way to challenge yourself and keep your mind nimble. And don't give in to elitism when it comes to programming. Some might call what you create "scripts" or "macros," but make no mistake, when you learn to code in Access, you're doing more than just recording macros. You'll find that a good majority of Access is exposed for you to command from VBA, and with some extra learning, you can find out how to command Windows interfaces to create even more comprehensive solutions.
Like I mentioned in my last post, one of the easiest ways to learn is by example. When you start, it will look very scary. Spend some time taking baby steps (yes, the dreaded "hello world" examples). Try recording macros and then look at the code to see what it took in VBA to automate what you did using the keyboard and mouse. Then move on to learn about the general framework for how things get done using VBA (the object model in tech-speak). Curiosity and a few hours messing around can get you quite far if you are patient with yourself. The same would hold true if you had a few hours to sit with someone that speaks another language...
Friday, July 6, 2007
Report sample database
Query sample database
Northwind Traders sample database
1. What will the database be used for?
If you're thinking of creating your own database to store orders, key customer information, or any other sort of "transactional" or business-critical information, think about it carefully. Now this will largely depend on the size of your organization, but if you have any sort of development or IT resources, or adequate money, consider leveraging your development staff to create the solution you need, or buy one off the shelf. If you work in a small company, creating your own database is probably the best, and appropriate, option you have. If your database is going to be used to store data for what I'll call "secondary use," like analysis or data crunching, creating your own solution is a great way to go. You don't need to worry about multiple simultaneous users, audit trails, etc.
2. Who will be using it?
I'm reminded of a project I undertook years ago when I was in sales. Our customer relationship management (CRM) implementation was a disaster, and my VP wanted something that our group of eight customer-facing staff could use to track activities and opportunities. Simple enough, I had created plenty of databases at that point. I failed to realize just how slow a desktop database application like Access performs when it's sitting on a shared file server. When more than one user was in it, it was abysmal. My point is this, if it's just you and a few others working in the database infrequently, Access is a good choice. If it's you and ten+ colleagues looking to maintain important information on a daily basis, you're probably better suited for a more robust solution. That might come in the form of purchased software, your own development group creating a more scalable application, or putting in a SQL Server or MySQL database with an Access front-end.
3. Where do you want to go?
After years commuting sixty miles, I took a job with one of the largest software companies in the world, much closer to home. One of the things we tell customers when they are evaluating our software versus their own in-house solution is this...do you want to continue focusing your energy and time developing software that is already available and evolving over time? To be clear, this blog has nothing to do with my primary employer or what they sell, but I'll pepper my comments with experience from there. My point is this, if what you're doing is considered to be fairly "mainstream" in terms of enterprise software, it's important to think about whether ten hours here and ten hours there may add up to a whole lot of time just to create something that already exists (time-value of money). In many cases, you're probably creating something very specialized for your business needs that just doesn't exist in any pre-packaged software. But be sure to do the research. If something already exists, is it light-years ahead of what you're doing and in your budget ballpark? You'd be amazed at what is out there. If something does exist, but it's out of your price point or doesn't quite fit your needs, D-I-Y on with confidence.
4. Will it get you praise
If you happen to report to someone (i.e., you're not your own boss), test the waters with what you're planning to do. In most cases, you're no where near a software developer much less in a development/IT role. So if your manager isn't keen on you becoming one on the side, find out early. You may be able to bring them around ("we can save X hours a week if you let me"), or get them on your side to push for the "right" people to do it. Either way, taking on projects like this can get your promoted, or stall your career if your management thinks you're not focusing on what's really important.
You're a professional out in the wide world who one day discovered their set of Excel spreadsheets was getting out of hand. You were curious about this item on your start menu, Microsoft Access, and decided to see what it was all about. Once you got past the deceptive key icon, which hints that Access is some sort of security application, you started to see potential to take that spreadsheet with ten tabs and thousands of rows into something a little more useful for you and your office mates. A few months later, and a few hours of self-learning under your belt, and you've got a fairly slick database that saves you time and money. Now you're wondering....what else could I do to make it better...should we migrate to SQL server...how can I make it easier to change forms...how do I handle multiple users...do I turn this over to our development group...do I buy off the shelf software...and so on.
I'm you, well, not exactly. But I can say I've been there myself, faced many of the same decision paths, asked many of the same questions, and found it incredibly fulfilling to easily create my own applications with an interface that is just the way it should be for my business. I started out trying to reduce an awful, ugly 28 step manual process of taking machine data, putting it into Excel, changing it, and then dumping it back into a machine readable format or pretty fax report. Interestingly enough, that ugly manual process was used to manage about 20% of all 9-1-1 emergency address records on the Eastern seaboard until 2000. So when they hired me fresh out of college, I sat through the training on this nasty little error-prone process and thought....there must be a better way.