... GPG On Access

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

The Cheese Stands Alone

2009-08-06T21:33:00.000-07:00

In a previous blog I talked about the usefulness of dynamically parameterized queries in generating small recordsets for forms and reports, a technique that is particularly good for client-server applications with potentially large recordsets in the back end. One of the additional benefits I found in working with this technique is that I can construct forms that work equally well as subforms and

Data Macros. How Hard Could It Be?

2009-07-26T19:46:00.000-07:00

The last week has been a lot of fun. Like many of the people who have access to the Technical Preview (TP) release for Access 2010, I lost a few hours of sleep the first two or three days of the week.However, over the last couple of days, it's begun to feel more like "business as usual". And I think that's a good thing. A very good thing. The new "look and feel" are natural and intuitive and the

Blue Skies Smiling at Me

2009-07-12T20:26:00.000-07:00

...Nothing but blue skies from now on. Would that it were true.Another blue weekend and not much Access work got done over here at GPG Manor.It's funny how languages work (I'm thinking of English at the moment, of course, but every language has it's own quirks.) For example, I was listening to Willie Nelson a bit ago and I got to thinking how ironic it is that we call it a blue day when there is

"Inappropriate Language" in MS Access 2007 Templates

2009-07-05T22:47:00.000-07:00

Okay, so I've been trying to figure out how to submit additional Access 2007 templates to the MS Access Template Site. For some time now, the MS Access team has been encouraging MVP's to participate in the template program.Well, I tried, I really tried. A little over a year ago, I submitted a dead simple template that consists of the table schema and two forms. That one was accepted.However, MS

Where in the World is Common Parameter?

2009-06-27T10:59:00.000-07:00

One thing I really like about the dynamic SQL approach to creating saved queries is that it allows me to re-use some base queries without having to parameterize them.With a forms reference, the query is tightly linked to the form itself. That means one query for each form where you want to use that same syntax--with variable input for the form.With a parameter query, the user has to supply the

Dude, Where's My Clause?

2009-06-21T22:51:00.000-07:00

Lately I've been experimenting a lot with writing dynamic SQL for queries. Even though I'm starting to see some limitations, I'm pretty sure I like this approach a good deal . Today I'll just sketch out some basics and come back in future blogs to talk about the details, including the places where I think it makes a lot of sense--and where it looks like it might not ever really work.You Want HOW

SQL Saturday

2009-06-07T13:35:00.001-07:00

Oh Come All Ye Faithful.I spent the day yesterday (June 6th) at the Portland SQLSaturday event. http://www.sqlsaturday.com/eventhome.aspx?eventid=16Way cool, but, of course, I ran into the predictible "Access? I'm sorry" bias. (More on that later.)I sat through sessions on:Table and Index Partitioning (If you manage many millions of rows of data you need to know about partitioning, otherwise, not

I Am Still Around

2009-05-08T20:45:00.000-07:00

I need a better calendar. This one is telling me it's been 3 months since I blogged. There is only one good explanation for that: the darn Outlook calendar has a bug in it. Gotta get my Outlook guy to look at it.Truthfully, though, the extended gap between outpourings of wit and wisdom is, I guess, a pretty good sign I've not yet become addicted to the "Blogger's Buzz". That's an affliction