... GPG On MS Access

Look Out for Look Ups

2013-02-10T12:13:00.001-08:00

For a long time now, I've argued that Lookup Fields in tables are One Bad Idea. I've even Blogged about it. Recently it occurred to me that maybe it's not enough to TELL people Lookups are almost always bad. There's a world famous page that offers eight significant problems with Lookups. My complaints are actually focused on the fallout from Number 6 on that list. Lookup fields in tables

Ambiguity Is For Poets, Politicians and Comedians

2013-01-29T19:22:00.001-08:00

Access developers already know this, of course, but sometimes we run into situations where we need to be reminded just how important it is. The human mind thrives on ambiguity. Poetry is the art of making one multi-faceted word speak with many voices. Politicians couldn't survive in office more than a few minutes without the deft word play that conceals as much as it reveals about their true

What Does a PCV Do Anyway?

2013-01-19T14:04:00.002-08:00

My daddy taught me a lot about living a good life. I wish I'd been smart enough to pay better attention when I was 18, but I survived long enough to be able look back and pick out the important stuff through the prism of my own experience. For example, he taught me not to try to fix a PCV without the right tools, the right parts, and the right kind of training. Let me tell you about that. My

Yes, We CAN Get There From Here

2012-11-25T18:43:00.000-08:00

The day after Thanksgiving turned out to be a pretty good day. It was the day I discovered that sometime in the last couple of weeks, Microsoft had turned on a really significant functionality in the 2013 version of Office 365. Specifically I'm referring to support for creating and deploying Access 2013 "hybrid" databases on Office 365 as well as in on-premises installations. This is a

That was a CLOSE One!

2012-08-26T15:08:00.003-07:00

A few days ago I gave in to a bit of discouragement and posted a long diatribe on why I am afraid the story for Office 365 and Access web apps is discouraging. Well, it turns out I was premature in my efforts at digging a grave for my BFF, Access. What a relief. Here are the three big reasons I was so pessimistic, and the reasons I now have a lot more reasonable view. Again, keep in mind that

Data Siloing in Access 2013 Web Apps on Office 365: the spade that could finally bury Access

2012-08-23T15:17:00.000-07:00

Sometimes, you gotta call a spade a spade. Spades, as you should know, are the tools used to bury things. I’m sitting here staring at my monitor trying to come to grips with the possibility that Access 2013 web apps on Office 365 are the spade that could finally bury Access. I’ll explain the three inter-related problems I see with Access 2013 web apps on Office 365 in a moment. First, I want to

Why I STILL Love Access

2012-07-15T14:46:00.000-07:00

Here's to Access, My BFF Access has been very, very good to me. For at least the last 10 or 12 years, it's paid my rent, contributed to my daughter's college education and helped me put on a few pounds too many at the dinner table. And that's just the monetary side of things. There's also the on-going personal satisfaction of facing a new challenge from a client, or from a poster on Utter

Hammers, Rubber Washers, Lug Nuts and Lookup Fields in Tables

2012-03-30T22:18:00.000-07:00

Many, many years ago, I learned The First Three Rules of Plumbing. Never start a plumbing project after all the hardware stores have closed. Locate the main shut off valve before you start. A hammer is not a good tool for changing rubber washers in a faucet in the kitchen sink. You can probably guess how I came by that wisdom, so I'll spare you the details. I can assure you, though, The First

Migrating Larger Tables to SharePoint

2012-01-21T17:26:00.000-08:00

This is the third, and possibly the last, in a series of blog posts on my experiences migrating data from a working Access/SQL Server database to SharePoint, where it now serves me daily. It's a "hybrid" database, in the sense that the Front End is the original Access accdb, complete with all of the original VBA intact, while the Back End lives on SharePoint. A couple of posts back I

Patching Up Relationships On SharePoint Migrations

2012-01-14T18:35:00.000-08:00

In my last previous blog post, I described one of the problems with exporting existing tables from Access or SQL Server to SharePoint. SharePoint bumps the existing Primary Key field out of its way and replaces it with a new one which SharePoint generates in its place. Here’s how that looks. In the second table in this screenshot, called “tblCustomer_SPBlog1, SharePoint has renamed the orginal “

Disappearing Primary Keys Notwithstanding, You Can Migrate to SharePoint

2011-12-21T08:40:00.000-08:00

Recently, a poster at Utter Access asked for help moving Access tables from an existing database to SharePoint tables (or more accurately, to SharePoint lists). His goal was to link to those tables in a Hybrid Access database. One of the issues raised was the supposed limit on the number of records you can export. Fortunately, a couple of really knowledgeable people, Albert Kallal and Bonnie

"Microsoft Access in a SharePoint World" Is Available

2011-12-11T17:35:00.001-08:00

I'm delighted to announce publication of a new, first-of-its-kind, book on Access 2010 and SharePoint 2010. It's been a long time in the writing, but we finally wrapped it up. My fellow MVPs, Ben Clothier (MS Access), Tim Runcie (MS Project) and I teamed up to write Microsoft Access in a SharePoint World. Tim’s company, Advisicon, is publishing the book. For more details or to buy the book,

How Much Does an Access Database Cost?

2011-10-23T20:25:00.000-07:00

Lately I've been pondering the implications of some of the questions we get at Utter Access. It used to be that I worried more about giving the questioner an answer appropriate to their situation, but lately, I’ve also been asking myself, "How much will that technique cost?" Here's an example. "I need to have users enter a value into a text box on a form. If that value is not found in the

Time Travel, or plus ça change, plus c'est la même chose

2011-08-22T20:25:00.000-07:00

I've been posting at Utter Access since June of 2002. That's nine years of getting help and giving help. Utter Access is a very fine forum, widely acknowledged to be one of the premier sources of Access help on the web. I absolutely agree that it is, although I am very sure there are other fine forums, staffed by volunteers who are as knowledgeable and dedicated as my friends at UA. I just think

Don't Be THAT Guy

2011-04-21T21:18:00.000-07:00

This one will be short. A few weeks ago I decided to update an old application I created a few years ago for a friend. It was created with Access 2002. The new version is in Access 2010 and it showcases a few 2010 specific features, as well as an updated table structure. I worked on it off and on over a couple of weeks. One night I was tweaking a form when Access threw an error, informing me it

Sometime I just want to be single, okay?

2011-03-30T18:31:00.000-07:00

(Guest blog by Banana)On the surface, it appears that VBA does not like it very much when you want to have a Singleton class. Whenever I write a class module, which is kind of rare given the general patterns in Access/VBA development, it's almost inevitable I want a Singleton. The only reason it's a class in first place is because I need some variables declared WithEvents. We can't do that in a

No, Excel is NOT a Database

2011-03-20T12:07:00.000-07:00

So, what's got my dander up today? I’m glad you asked. Excel is NOT a database. You can't import Excel spreadsheets into Access and call that a database. You can't create "spreadsheets" directly in Access and call that a database. I wish more people understood that. It would make my blood pressure go down, probably down a lot. Hold off on the Pitchforks and Torches, Please Excel is a great

New, New, New

2010-10-18T23:37:00.001-07:00

Well. It's been an interesting 6 months.New job.New home.New book project.New Laptop (Rest in peace, worthy Dell Vostro).So I'm long over due for a new blog post.The last post is badly in need of a follow up, but that is going to have to wait a bit longer still--too much on my plate to do a decent job at the moment.My last work project was an enhancement to an Access/SQL Server database for a

A work in Progress and 64 Bit VBA

2010-03-28T12:18:00.000-07:00

Recently, I revisited an Access demo I've been puttering around with for a while. In the process, I also had a great chance to take a stroll through the emerging information relating to 64 bit OS's and Access. The event that triggered this was moving this demo into Access 2010 (32 bit flavor) running on a 64 bit OS (Win 7). It didn't work, which called for some changes in API calls to handle the

Making A "DUAL" Table

2010-03-14T16:23:00.000-07:00

My friend, Banana, likes to push the envelope sometimes. He's a first class developer who's always trying out new ideas. So it was no surprise to me when he asked me if I knew what a "DUAL Table" was. I figured I was in for another trip past the edge of my existing knowledge and I was not wrong.It's More Than Just a Two-FerTurns out that a DUAL table is a feature in Oracle. I won't waste space

What is May 12th, 2010?

2010-03-14T16:22:00.000-07:00

Answer: The date on which Office 2010 will officially be released.No, I can't wait.

Access Wiki

2010-02-25T11:50:00.000-08:00

The big news this week is that Utter Access has added a Wiki to its portfolio. The Utter Access forums, which remains the number one Access help site on the internet, also has a new look and new features.http://www.utteraccess.com/http://www.utteraccess.com/wiki/index.php/Main_PageThe best just keeps on getting better.

Now, Where Did THAT Come From?

2009-11-29T21:11:00.000-08:00

I recently started thinking about setting application properties a little differently than I had been. Well, actually, it's been a while since I really gave it much thought at all, to be honest. A long time ago, I created a module with default constants like "MBTitle" and "AppTitle", and started importing it into new accdbs or mdbs. Then I'd edit the values for the new database. It works, but it

Access on SharePoint

2009-11-08T09:27:00.000-08:00

Are We There Yet?Actually, I think so. I really do. We still have to unload the car and carry a few bags and boxes into the house, but we're certainly sitting in the driveway.I've done up a small demo with MS Access 2010 (beta) running on the SharePoint 2010 (beta) and shown it to a few folks. It runs on my employer's site, so I can't publish the URL publically, unfortunately. Still waiting for a

And Now For Something (Almost) Completely Different

2009-10-11T19:01:00.000-07:00

I have a guest blogger today. Ben Clothier has agreed to let me post this article he recently wrote. Ben is an MS Access MVP as well as being a some-time colleague and full-time friend.You'll find more of his excellent posts at Utter Access and at Access Word Forums.Ben writes on the use of Parameterized Queries to achieve the results I've recently described with other techniques involving