SharePoint Saturday Louisville – MS Access Session Recap

Standard

Yesterday was #SPSLouisville. It was a lot of fun. Had a little over 100 people come spend their Saturday at the new East Marriott Louisville to learn a little more about SharePoint!

It was my first time speaking at an event, and being a bigger part of the organizing committee putting the event together. It really gives me a new appreciation for all the work it takes to get it together, so I’m super happy with how it all turned out.

Speakers/committee members got legitimate baseball jerseys and all attendees got a SharePoint Saturday Louisville Louisville Slugger Bat (Seen above).

So a few shout-outs… Thanks to the committee who put it all together, Stacy Deere, Fred/Barbara Soward, Steven Carr (thanks sweety), Brittany Strole, Tony Maddin, Michael Heils, and everyone else that made this event possible… ESPECIALLY all of the Sponsors that helped make it possible!

Now to re-cap a few things, I talked about MS Access and SharePoint and how you can use them together at the most basic level with your SharePoint site to manage data. I think there are a lot of great things you can do with SharePoint so that you don’t necessarily need Access for (I think that becomes a bit easier with Enterprise Services), but if you are using SharePoint mostly OOTB (Out of the Box), then Access can really help take your site to the next level.

So here is a quick summary of some of the things I talked about.. First, we talked about Linking a SharePoint list to Access. Visit that link for a little more info on that… it’s the basic building block of it all. I also showed how you can export an Access Object to SharePoint. I’ll write a follow up post about that, since there are good and bad reasons to doing it.

Next, I shared some of what I’ve developed and demoed a pretty intense Access ¬†Database that basically does a ton of formatting, joining, etc etc to get data into a specific order. There was question as to why there were so many queries, and I build it that way for easy troubleshooting. Several important things I’ve learned and want to remind people about is:

  1. Send data from tables, not queries. I found appending a table (instead of a query) seemed to be a bit more efficient from a performance standpoint. You can easily create a ‘Make Table’ query and then append the data from that (which is one thing I normally do).
  2. Refresh List after column change. If you make a column change, you MUST refresh your list (by either unlinking/relinking… or the more efficient way, right click on your linked list (in Access), going down to ‘More Options’ > Refresh Lists will do it.
  3. Be Patient! Things are not going to run as quick as you might expect it to. Access usually will say ‘Not Responding’ to me. I just let it go. It’s working! I promise! One way I usually make sure is by going out to the list it’s updating and refresh to see if it’s adding or deleting records as I expect it too.

One thing I need to correct, one of the other Speakers (Scott Shearer) showed me something I was missing which I think people will like to know. During the presentation we had a little discussion about the system columns (Modified, Created, Modified by, etc) and how the do not show up if you simply open the table. If you were going to query the data and had added the list to the query (in design view) you can see in the picture on the right that at the bottom of the selectable columns from the My Tasks linked list are the System Columns. That should be a lot easier to do than manipulating the Calculated Column to show it.

I had also done some fun things in manipulating the UserInfo and People Picker fields via Access and I’ll plan on posting that information sometime. I think it’s good information and I’d like to share it as well.

I think that about covers everything. I plan on submitting a few session ideas for SharePoint Saturday Cincinnati (and that’ll be in October). I’ll be volunteering there regardless. If you have any questions remember to reach out to me and I’ll be glad to hopefully set you in the right direction!