MS Access Annoyance: Doesn’t Link Synonyms

I use Access when I don’t have dedicated admin tools for editing data, frequently the case for infrequently changed data in smaller projects. Linked tables work reasonably well, just have to create a DSN first.

Except…it doesn’t seem to work (at least obviously) for synonyms. Here’s one I created (viewing as sysadmin):

image

And the view when linking from Access, also with syadmin credentials:

image

In this case I just wanted to peek at a row just inserted. Not that I couldn’t run the select, but was (or would have been) convenient to do so, as often I wanted to update the row so it would re-process without having to run through the entire process again.

I get that it’s not always a table/view, would be strange to link to a proc – but isn’t that what metadata is for?

Why synonyms? I’ve a huge fan of two part syntax. Not one part, not three, not four. Make it all look local, when it’s time to move the db life is good, just update all the synonyms and move on, no code changes.

The Breakfast Plan Goes Awry

Here’s the editorial for SSC today and while it’s a humorous story about asking for donuts and getting a free breakfast for everyone, it’s also a cautionary tale for those trying to do good as managers, and even for employees submitting suggestions.

Looking back, I still think it was a fair suggestion in response to a request for ideas, I think putting a couple dozen whatevers on the CIO’s desk every Fri morning is a great way to encourage cross team chat, and a chance for the CIO to get some face time with people they don’t get to see as much as they’d like.

At the time money was plentiful and so throwing cash at the idea to satisfy those who wanted yogurt, but in leaner times, could the goal still be accomplished? I think so, and one easy way might be to:

  • Rotate through the team, having each person take a turn at picking up breakfast. Maybe even let them come up new breakfast sources to use the $30 or so budget. Nothing like having to get up a little early to stop and get the food to make you appreciate someone else doing it (and someone else paying for it!)
  • Try to accommodate the ones who don’t want a chocolate covered donut, but not to the point of craziness. Not the end of the world if everyone knows this Fri is donut day and if you’re on a diet, you bring your yogurt or banana or whatever. As long as they aren’t excluded too often, it works.

Beyond breakfast though, this can happen with any idea. Start with a basic idea and before you send it in, stop and think about it. What is the real cost? Who will do the work to make it happen? Is it inclusive, or just something that benefits a small subset? Can you find a way to derive more than just good will from it?

Of course, Steve Jones should have posted this on Friday with a coupon for all the SSC readers to get a free donut!

Conference Calls Are Hard

Conference calls are a fact of life for most of us. Remote offices, remote workers, clients – lots of reasons to do a call. Hard to argue with the cost, definitely a money saver. For some things they work well, for situations requiring a lot of collaboration though, they take a lot of extra effort and even then rarely as effective as putting everyone in the same room.

I’ve been doing more of them than average lately, all collaborative, and while they’ve been useful, they’ve been tiring. Especially when leading the call, I find it much harder to keep track of the current conversation, items that were mentioned that I want to loop back to, and the participation of everyone involved. Conference calls require deep listening, because minus the facial cues you’re trying to catch the audio cues that tell you when someone has more to say but reluctant, or unhappy with the direction and mostly tuning out. That kind of listening is hard work if the call is more than 15 minutes or so.

Most of the usual meeting prep stuff applies here; agenda, moderator, time boxed, docs distributed in advance, plus what is hopefully a quiet time for all involved. Last week I ended up participating in one call while driving, call dropped three times – certainly didn’t make the call go any smoother. Headset helps.

I’m surprised by how bad most of the conference calling solutions are. You announce yourself, but the software doesn’t play it for other attendees (“who just joined the call?”). You can mute your line, or mute all if you’re the moderator, but no way to mute the person who doesn’t realize they are the ones adding all the background noise to the call. Maybe just using the wrong solution!

I think that if you’re going to have people on a call, it’s probably better to have them all dial in separately rather than put a group in a room and have a few people calling in. There’s a strange dynamic there, the people in the room are having a hidden conversation with facial cues and body language that the ones calling in just don’t see – makes it more awkward and less productive in some ways.

Con calls aren’t going away, so I’m interested in finding ways to make them better, or at least less annoying!

PASS Update #35

It’s been a busy month for me for PASS activities. I spent almost the entire week last week on PASS related items and probably 60+ hours for the month:

  • Reviewing the annual budget
  • A call on SQLSaturday to work on plans for 2011
  • Tons of time on the Spring Event

I won’t share much in the way of details on the budget, we’re in the middle of the final vote to approve it now and we’ll make that public within about 30 days after that. Members get to see all the totals, we just remove some of the details that relate to privacy – salaries for HQ staff for example. I reviewed the budget about 2.5 times. It comes an Excel workbook with far too many tabs, I own the SQL Server Standard budget, plus a portion of the Community Connection budget – both are fairly easy to review. It’s the rest of the budget that is hard, looking at our spend in other areas, especially with regards to ‘overhead’. Sent Bill a lot of questions, got most of them answered. We did a couple calls to review final changes, and even then Wayne Synder caught something I missed – we had not included a line for adding to our long term reserves. Important item, just didn’t catch it because it wasn’t there.

Long term reserves are a big item for me. In my opinion we’ve got enough to survive a really bad year, but we’d suffer badly in doing it. You hope to never have that kind of year, yet especially for an organization like ours that has relatively few funding sources, it’s important to plan for it. This year we’ve budgeted about $50k to add to our reserves. I’d like to do more, but it would mean sacrificing growth. I think – my opinion – we’re at about half of the long term reserves we need to have, call it another $500k needed. We can accrue that if we budget for it over the next 5-6 years, and we can supplement that with a line of credit just in case.

On the SQLSaturday stuff we’re working on process and goals, no real changes. Still, it takes time even to build process. For example, last Friday I did a call to review with HQ the reconciliation process for accounting for the funds we hold in trust for SQLSaturday. We’ve identified a way to reduce the time it takes, but to do that took an hour call to work through it, 30 minutes to write the query and report over the weekend, and no doubt some extra time to tweak that as we run on live data.

The “spring event” is moving along, but it’s been at alternate times fun and frustrating. We’ve got a lot of people to coordinate, and it’s not as smooth as repeating an event that has been done before. Reviewing that budget has and continues to take a lot of time. One of our big goals is to maintain a $299 price point, and that necessitates a no-frills approach. But what is a frill? Is it wireless access, which will cost us $12,000 for the event? Coffee breaks, at $8 per person per break? Cheap badge holders or stick on name badges? Fewer or less fancy signs? T-shirts for speakers instead of polos? We will stick to the $299 price point, not up for discussion!

It’s also taking some time and effort to build the mental model across the team of what it looks like. We don’t want it to look or feel like the Summit, and we won’t want it to look or feel like a SQLSaturday either. For those of you who have been to both I bet that makes sense, but quantifying it – that’s tough. For me, it’s less formality, more speaker contact, a sense of a slower pace, yet an event with energy, a sense of fun. It’s more than just delivering great content.

We’re also struggling to figure out how to leverage our partnership with the local chapter (in this case oPASS) which is a key way of both reducing costs and making sure we reach those within reasonable driving distance. Jack Corbett is participating in the planning of the speaker selection process as well as how sponsors are handled, Kendal Van Dyke is teaming marketing with a focus on the Florida market. Obviously we’ll have volunteers from chapters staffing the event, but shouldn’t we do more? Right now I’m worried that we haven’t done enough there, which means going back and trying to figure out if that’s a real concern, or just being caught up in the details.

And besides that, I’m working on details of SQLSaturday #49 coming up on Oct 16, sending out invitations to speakers from last year that haven’t signed up again and nailing down other items we need to purchase besides the road signs.

Finally, with the call for nominations now open, I’ve got to decide soon about whether to seek re-election. That’s going to be a tough call. I like participating and giving back, and I think we’ve still got work to do on transparency and culture within the Board that I’d like to participate in. But, I’m also putting in more time than I can sustain for another 2 years. It’s definitely taken time away from both work and family that I can need to restore. I’ve got a couple weeks before the cut off, time to talk to some trusted friends to figure out which is the right path.

Growing the SQL Community – Carefully

If you’re a blogger or a twitterer or a SQL speaker or PASS volunteer, you do it because you have found it to be fun, worthwhile, even challenging perhaps. I bet you remember doubting the value before you started and hesitating to take the leap, and maybe you tried it because you had someone inspire you, or – maybe someone twisted your arm a little!

Because we find it fun, we like to share our passion, and it’s easy to forget that not everyone has the same interests, the same outlook, or is in that place in life where they are ready to try something new. There’s a tendency to think and say “I did it, so can you”. The possibility exists, but it’s just not that simple! It’s less about ability, more about desire.

I was reminded of this recently by a friend who knows several other people that are passionate about community. He enjoys learning, sees the value of professional development, and sees that getting that development is possible by carefully choosing how and when he learns, but doesn’t want to go further, for now at least. Doesn’t diminish him in any way. He has goals and priorities, and they are different than mine. Not worse, just different, and perhaps more balanced!

For those of us who are really passionate about the SQL community, what do we do to encourage others to participate? Sometimes we can just point to the door, saying “here is the path I took”. Sometimes we can open the door by coaching them through a few blog posts or helping them build the first presentation. More often we lead by example, doing the things we enjoy and trying to do them well, encouraging others – subtly – to try it as well.

But what we shouldn’t do is arm twisting. It’s all too easy to start to pressure people, especially friends or co-workers, to walk the same path you do. Rarely works, and even when it does, it rarely sticks. We end up alienating the people we’re trying to help.

What can do is find low key ways to share our enthusiasm:

  • Treat hem to dinner or a drink if they’ll attend a chapter meeting (make sure the topic is one they will find interesting)
  • Offer to car pool to a SQLSaturday and be willing to leave whenever they are
  • Share a blog post via email and talk about not just the post, but the author of the post
  • Ask them to write a guest blog post about a success they have had at work, or co-author an article with them
  • Be there when they are ready to take the next step

It’s easy to push too hard. I don’t do it often, but I know I have at times. Well meaning, but no less annoying to the person on the other side. If nothing else, maybe writing this will reinforce that for me.

Building a PASS Voting Solution – Part 6

Today we’ll try to finish up by looking at a couple of the semi-interesting parts of the code in this one page application. We’ll start with the page load event. On the first run of the page (not postback) I’m grabbing the two keys I need if they exist and calling the MyBallot.Validate method (show you that next). From there I show some various bits of text and load the choices if appropriate, and then move on to deciding what to show them based on what we know.

‘only do on first page load
If Not Page.IsPostBack Then

    Try
        ‘try to load from query string
        MyBallot.BallotID = New Guid(Request.QueryString(“BallotID”))
        MyBallot.VoterID = New Guid(Request.QueryString(“VoterID”))
    Catch ex As Exception
    End Try

    MyBallot.Validate()

    ‘load the list if all is good
    If MyBallot.EligibleToVote And MyBallot.HasVoted = False Then
        MyBallot.LoadListBox(lstAvailable)
    End If

    ‘set this regardless
    lblBallotTitle.Text = Me.MyBallot.Title
    lblBallotDescription.Text = Me.MyBallot.Description
    lblBallotItem.Text = MyBallot.Title
    lblMin.Text = MyBallot.MinSelection.ToString
    lblMax.Text = MyBallot.MaxSelection.ToString
    lblClosing.Text = MyBallot.ClosingDate.tostring
End If

‘various cases, all mutually exclusive
pnlVote.Visible = False
If MyBallot.EligibleToVote And MyBallot.HasVoted = False Then
    pnlVote.Visible = True
ElseIf MyBallot.HasVoted = True Then
    ShowMessage(“You have already completed your vote in this ballot.”, displaymessage.IconToDisplay.InformationIcon)
ElseIf MyBallot.BallotID = Guid.Empty Then
    ShowMessage(“To vote on a ballot you must provide the ballotid in the URL – contact PASS HQ for assistance”, displaymessage.IconToDisplay.InformationIcon)
Else
    pnlLookup.Visible = True
End If

Validate is where we call the proc GetBallotDetails, which you may recall returns both output parameters and a result set.

‘remove any old items
Me.BallotDetails.Clear()
Me.EligibleToVote = False
Me.Title = “Unknown”
Me.Description = “”
Me.MinSelection = 1
Me.MaxSelection = 1
Me.HasVoted = False

Dim dr As SqlDataReader
Using cmd As New SqlCommand()
    With cmd
        .Connection = OpenDBConnection()
        .CommandType = CommandType.StoredProcedure
        .CommandText = “Voting.GetBallotDetails”
        .Parameters.Add(New SqlParameter(“@BallotID”, DbType.Guid))
        .Parameters(“@BallotID”).Value = ballotID
        .Parameters.Add(New SqlParameter(“@VoterID”, DbType.Guid))
        .Parameters(“@VoterID”).Value = VoterID
        .Parameters.Add(New SqlParameter(“@IsEligibleToVote”, DbType:=SqlDbType.Bit))
        .Parameters(“@IsEligibleToVote”).Direction = ParameterDirection.Output
        .Parameters.Add(New SqlParameter(“@HasVoted”, DbType:=SqlDbType.Bit))
        .Parameters(“@HasVoted”).Direction = ParameterDirection.Output

        dr = .ExecuteReader
        Do While dr.Read

            ‘just assign these each time, shouldn’t change
            Me.Title = dr!ItemTitle
            Me.MinSelection = dr!MinSelection
            Me.MaxSelection = dr!maxSelection
            Me.Description = dr!Description
            Me.ClosingDate = dr!ClosingDate

            ‘next we add the details
            Dim oDetail As New BallotDetail() With {.BallotDetailID = dr!BallotDetailID, .Title = dr!DetailTitle}
            Me.BallotDetails.Add(oDetail)

        Loop
        dr.Close()

        HasVoted = cmd.Parameters(“@HasVoted”).Value
        EligibleToVote = cmd.Parameters(“@IsEligibleToVote”).Value

        dr = Nothing

    End With
End Using

Here is the code called when they finally vote. We check our local state to see if they have voted, and then we’ll check again within the proc to make sure! ExecuteProc is a helper method that handles calling a proc when it just takes parameters.

If Me.EligibleToVote = False Then
          Throw New Exception(“We have not identified you as an eligible voter for this ballot.”)
      ElseIf Me.HasVoted = True Then
          Throw New Exception(“You have already cast your vote for this ballot.”)
      End If

      ‘trim leading comma
      If SelectedDetails.Substring(0, 1) = “,” Then
          SelectedDetails = SelectedDetails.Substring(1, SelectedDetails.Length – 1)
      End If

      Dim Params(0 To 2) As SqlParameter

      Params(0) = VarcharParam(“@Details”, 8000, SelectedDetails, False)
      Params(1) = New SqlParameter(“@BallotID”, SqlDbType.UniqueIdentifier)
      Params(1).Value = BallotID
      Params(2) = New SqlParameter(“@VoterID”, SqlDbType.UniqueIdentifier)
      Params(2).Value = VoterID
      ExecuteProc(“Voting.CastVote”, Params)

      Me.HasVoted = True

There’s not much else that is interesting. It’s set up with a master page containing the logo, and the main page is a content page. I’m storing a Ballot object in session and it’s also set up to be accessible as a property of the page, making it easy to reference as a strongly typed object.

I came in right at 10 hours. Lost a little time thinking through how to handle multiple items on each ballot, couldn’t see a way to do that without adding a lot more complexity to the UI. It still needs some testing by someone other than me, and it’s a project where I think unit tests would be appropriate, lots of different cases that could be verified (eligible, not eligible, has/not voted, etc), but I think we can do most of that with UI testing that will be needed anyway.

By the time you read this I’ll have sent the bits off to HQ for install and testing. We’ll try it on a few board votes, and we may send out a test vote in the Connector too.

PASS Board Call For Nominations Open

Nominations opened today and will run through July 21st. Whether you’re planning to be a candidate or not, I hope you’ll visit the election page and review the documents posted. Those documents reflect a lot of time and effort from Hannes and a lot of others to try to improve the election process this year and to bring a transparency to the process too. One big change – we’re going to be posting applications and scores, everyone will get to see how candidates fared leading up to selecting the final slate.

See my post from May 2010 about serving on the Board for more of my own views.

Guest Editorial on SSC – Selling Used Cars/Experience Requires Repetition

I’ve been experimenting with stories in the editorial, experiences of my own that are amusing and have some kind of interesting lesson embedded. Stories can be interesting, uplifting, even tiresome, not sure at all where the boundary is yet, or if it can even be defined that way. The good part about stories (and life experiences in general) is that they resonate with people – as social creatures we like to know that we’re not so different than the other guy.

Moving back to the used car editorial, it was often a surreal experience. I still remember driving a ‘74 or so Cadillac that had a feature that would auto dim the headlights – pretty advanced stuff for 1974, but I guess it it didn’t work that well then, or now, I don’t see many cars with it. Once I saw another driver pull the car up for viewing and forget to put the car in park, when the auctioneer waved to rev the engine, he nearly got run over! Fired the guy on the spot, which seemed harsh. Always wondered if the auctioneer learned not to stand in front of the car during that phase!

A follow up lesson learned – think I blew the title on this one. The ‘lesson’ here was about repetition and trying a lot of different techniques until you see that they aren’t really all that different. I tend to write by pulling a topic from my list, creating a title from that, and then writing the rest. Mostly that works ok for me, but I need to go back and do the same thing I recommend for presentations – make sure the content matches the title. I think a better title would have generated more reading/discussion.

SQLSaturday Orlando Signs

Life in the event leader fast lane, finally got the signs for our upcoming #49 here in Orlando. In past years we’ve gone with the low budget version, but this year since we’ve got ace fund raiser Jack Corbett working on sponsors, we’re investing a little. Ordered 25 of the 18×24 signs on plastic, printed both sides, $150 with shipping plus the metal stands. Now I just need to find a Rubbermaid or similar container to store them in, or, maybe we’ll give them away as keep sakes.

And the chair? It’s one of two at the office, comfortable, but that pattern was all they had in stock that day, matches the pants that Steve Jones likes to wear!

 

IMG00078-20100623-1310

Building a PASS Voting Solution – Part 5

Today we move over to the web side of things. I put this together with VS 2010, and it’s a single aspx page with a single class and a user control. I’m using DevExpress controls (they provide me with a free subscription, good stuff).

Here’s the normal view that a voter will see if they’ve clicked a link received in email. That link would be passing in the BallotID and the VoterID. We do a check to see if the election is open, and that they haven’t already voted and if all is well, they can vote. As you can see I’m not a UI wizard! The nice part is this stuff is easy to change, it’s the plumbing that takes time to figure out.

 

image

Here is the post vote display (same page):

 

image

 

If they try to refresh to vote again they get this:

 

image

If all they have is the ballot ID (from a link we share on Twitter for example), they get a different display (same page). Because we’re going to send a email to the address they enter, I added the captcha to reduce the chances of a bot hitting it and getting us blacklisted. If the address is valid for that election we send them a full URL, otherwise they get a message that it didn’t match. Not perfect, but matches the rules we built on for this time around.

 

image

Tomorrow we’ll look at some code!