Need to convert your Macros to Visual Basic? I never imagined Microsoft had a tool like this, but come to find out, it's been in Access since 1997.
http://www22.brinkster.com/accessory/macros/004.shtml
Saturday, January 17, 2009
Monday, December 29, 2008
Database Design and Abstract Thought
We're hard at work with a new product to be released very soon, and some interesting things have come up in user testing. One of the most common challenges we see for Microsoft Access users is the database design itself. And by design, I mean the tables and how they relate to one another. When you are working in Access, the form and report design are addictive tools because they are largely what-you-see-is-what-you-get (wysiwyg). But of course, without the underlying tables to store and present the data, your forms and reports aren't going to get you too far.
In the end, database design is an exercise in the abstract. Its taking very concrete concepts like:
Product X can be blue, red, or yellow
Product Y can be orange, gray, white
And then figuring out how to create a database that can help users select from the available options. To do so, you need to move from concrete to the abstract. Otherwise, you get an ugly database where you have a table "tblProduct" with check box fields for each possible color, and bunch of awkward code to make it work. But by thinking of the abstract concepts, which isn't always an easy feat, it's clear you want to actually have a separate table for color. Then, because each product may have more than one possible color, you have to create another table where you can marry them together.
So what's the tip for the day? When thinking of how to architect your database:
Happy New Year!!!!
In the end, database design is an exercise in the abstract. Its taking very concrete concepts like:
Product X can be blue, red, or yellow
Product Y can be orange, gray, white
And then figuring out how to create a database that can help users select from the available options. To do so, you need to move from concrete to the abstract. Otherwise, you get an ugly database where you have a table "tblProduct" with check box fields for each possible color, and bunch of awkward code to make it work. But by thinking of the abstract concepts, which isn't always an easy feat, it's clear you want to actually have a separate table for color. Then, because each product may have more than one possible color, you have to create another table where you can marry them together.
So what's the tip for the day? When thinking of how to architect your database:
- If you think of the "thing" in the plural, it's probably a table unto itself. Products, customers, contacts, notes, colors, etc. In some cases it's just a simple lookup table.
- If you are naming the field and you label it in the plural, you may be describing a many-to-many relationship as opposed to just an attribute of the thing your table contains (as in the example we started with where you're tracking Products, if you started to name a field "Possible Colors," it probably deserves some reconsideration as just a field in your Products table).
- Try to avoid creating separate tables just to differentiate the state of something. For example, if you want to track Customers and Prospects, and all the fields are the same, it's highly recommended that you keep them all in a single table, and have a field that refers to each records' state (customer or prospect). You can then filter your forms, queries, and reports if you need to show only one. Plus, if someone changes from a Prospect to a Customer, it's a single field value that changes. No messy transfer of data from one table to another to signify the change of status.
Happy New Year!!!!
Thursday, November 13, 2008
Access "14"
After returning from a brief session with Microsoft on some of the new features of Access "14", I thought many would be pleased to know that they are letting us confirm that VBA will live on in the next release of Access.
I can't share any other details due to confidentiality agreements, but I'm sure they won't mind me saying the Access community will be thrilled with the next release.
So cheers to the incredibly hard working folks at Microsoft that continue to make Access the leading desktop database, and to the promising future.
I can't share any other details due to confidentiality agreements, but I'm sure they won't mind me saying the Access community will be thrilled with the next release.
So cheers to the incredibly hard working folks at Microsoft that continue to make Access the leading desktop database, and to the promising future.
Monday, September 15, 2008
eBook Topics - Your chance to provide input
We're considering creating a free eBook on Microsoft Access. Yes, in part a selfish way to create awareness about our products. But equally important, as a way to help the many Access users that find the phone-book sized "how-to" guides available at bookstores too expensive and too unwieldy. We've got a general theme already, but this is your chance to weigh in. What would you want to see in a free resource for Microsoft Access? What are the questions you have about how to create really powerful Access databases?
Monday, June 30, 2008
That's a Nice Little Macro You Have There...
On occasion I run into comments regarding Microsoft Access that range from simple derision to downright vehement hatred. Seriously. But by and large, you'll hear things from "serious" developers like "oh, it's just a few macros in an Access database." When in reality, you may have a fully respectable business application built using Visual Basic. Why the hostility from professional software developers and IT? I'll do my best to explain as I see it.
The first reason is that, quite frankly, Access isn't everything that a C++, Java, or .NET application can become. In the eyes of some developers with a BS in computer science, Access ranks right along side Microsoft Excel (thus the reference to "macros") in terms of capability, reliability, and extensibility. Moreover, Visual Basic is the descendant of BASIC, which is sort of like training wheels on a bicycle. Most professional developers (I'm speaking in the sense of C++/Java/.NET developers. There are plenty of VB professional developers, too) think in terms of how deployable, extensible, flexible, reliable, and robust a software application can be, which is generally a function of the programming language. A Microsoft Access database can be all of those things, but not typically to the degree as, say, a J2EE-based server based application that can easily handle hundreds of users simultaneously and change your oil, too. Since life is all about perspective, that's often their frame of reference. And rightly so, that's what they get paid for. But that doesn't explain the hostility.
The more interesting reason, in my mind, for a strong distaste for Microsoft Access is that Access solutions can be perceived to be a threat to a full-time developer's position of importance and purpose. Now, no one will ever come out and say as much. But let's face it, in many cases you can churn out a quick VB-based application to solve a business pain in a matter of a few hours or days. Will it be a full fledged application in the same sense as a C++ or Java application that goes through a development/test cycle? Possibly not, but it can get the job done in the timeframe needed to meet a business problem. And that is a key source of irritation to those who believe Access applications are mere toys.
This is not to say that a C++/Java/RoR/whatchamacallit programmer can't also turn out a fast solution to a problem too, but an Access-based solution may be just what the doctor ordered at the time it was needed, for the number of users, scope of functionality, etc. And that can serve as a direct threat to a full-time programmer's role. The reaction? What appears to be an elitist snuff at Visual Basic and Microsoft Access. But I can guarantee you that I've also seen (and built) Microsoft Access applications that look and function far better than applications built in more powerful programming languages.
The last cause for scorn, and this is often fully warranted, is that Visual Basic and MS Access applications are often created as a learning experience. The result? Spaghetti code, bad error handling, you name it. And while I can't say I agree with what is often a mocking tone from a higher order programming language, I do agree that you have to draw limits to what sorts of Access applications you create if you aren't an expert. It's the classic "know enough to be dangerous" scenario where you may be able to create a Visual Basic application that runs your business, but if it's handling critical tasks and you're still a novice, it may be time to look for a professional. That professional may still create your application in Visual Basic, but that's really a matter of what your underlying needs and long term growth expecations are.
So for all you Access developers out there, novice or lifers, hold your heads high. For all you higher order programming language professionals out there, give them a break. You'll never be obsoleted by suddenly pervasive MS Access applications, and besides, it gives you something to do when the user/customer/business needs outgrow a desktop database application.
The first reason is that, quite frankly, Access isn't everything that a C++, Java, or .NET application can become. In the eyes of some developers with a BS in computer science, Access ranks right along side Microsoft Excel (thus the reference to "macros") in terms of capability, reliability, and extensibility. Moreover, Visual Basic is the descendant of BASIC, which is sort of like training wheels on a bicycle. Most professional developers (I'm speaking in the sense of C++/Java/.NET developers. There are plenty of VB professional developers, too) think in terms of how deployable, extensible, flexible, reliable, and robust a software application can be, which is generally a function of the programming language. A Microsoft Access database can be all of those things, but not typically to the degree as, say, a J2EE-based server based application that can easily handle hundreds of users simultaneously and change your oil, too. Since life is all about perspective, that's often their frame of reference. And rightly so, that's what they get paid for. But that doesn't explain the hostility.
The more interesting reason, in my mind, for a strong distaste for Microsoft Access is that Access solutions can be perceived to be a threat to a full-time developer's position of importance and purpose. Now, no one will ever come out and say as much. But let's face it, in many cases you can churn out a quick VB-based application to solve a business pain in a matter of a few hours or days. Will it be a full fledged application in the same sense as a C++ or Java application that goes through a development/test cycle? Possibly not, but it can get the job done in the timeframe needed to meet a business problem. And that is a key source of irritation to those who believe Access applications are mere toys.
This is not to say that a C++/Java/RoR/whatchamacallit programmer can't also turn out a fast solution to a problem too, but an Access-based solution may be just what the doctor ordered at the time it was needed, for the number of users, scope of functionality, etc. And that can serve as a direct threat to a full-time programmer's role. The reaction? What appears to be an elitist snuff at Visual Basic and Microsoft Access. But I can guarantee you that I've also seen (and built) Microsoft Access applications that look and function far better than applications built in more powerful programming languages.
The last cause for scorn, and this is often fully warranted, is that Visual Basic and MS Access applications are often created as a learning experience. The result? Spaghetti code, bad error handling, you name it. And while I can't say I agree with what is often a mocking tone from a higher order programming language, I do agree that you have to draw limits to what sorts of Access applications you create if you aren't an expert. It's the classic "know enough to be dangerous" scenario where you may be able to create a Visual Basic application that runs your business, but if it's handling critical tasks and you're still a novice, it may be time to look for a professional. That professional may still create your application in Visual Basic, but that's really a matter of what your underlying needs and long term growth expecations are.
So for all you Access developers out there, novice or lifers, hold your heads high. For all you higher order programming language professionals out there, give them a break. You'll never be obsoleted by suddenly pervasive MS Access applications, and besides, it gives you something to do when the user/customer/business needs outgrow a desktop database application.
Monday, June 2, 2008
The Kind of Thing That Can Ruin a Day
If you're using Access 2007, be sure to read the Access Team's blog on an issue that can wipe out your database when you run the "Compact and Repair" utility.
Click here
Click here
Wednesday, May 14, 2008
Microsoft Access Video Tutorials: Tips and tricks, news, links, downloads on Microsoft Access
Helpful resource with many videos for Access 2007...
Alex & Access: Microsoft Access Video Tutorials: Tips and tricks, news, links, downloads on Microsoft Access
Alex & Access: Microsoft Access Video Tutorials: Tips and tricks, news, links, downloads on Microsoft Access
Subscribe to:
Posts (Atom)