Geeks With Blogs
My lessons learned while developing!
So let's say that you were asked by your organization to make an intranet "database" using access, and that because of organizational policies, etc. there is no option to have a real webserver like a LAMP/NGINX/IIS stack.

I recommend that you push strongly for real web technologies like PHP/C#/Java and try really really hard to not have to use Access. I tried that for this project and failed. I learned that Access is complete piece of garbage. However, as I struggled through the painful interfaces, limited documentation and poor community support, I realized that I should share what I did so that some downtrodden developer would be able to learn from what I did. 

Also, if you are an access developer...what are you thinking? Learn a modern high level languages or two and use that with a good framework instead. That is much better in my experience in every way.

I assume that you are reading this because you either are a masochist and like Access or someone is making you use Access. I am sorry. I feel your pain and hope that this post will help somewhat.

First, since we are stuck with Access and have to have multiple users using should we use it?
  1. We could have it be a normal access database, opened with microsoft office and put on a shared drive. This is problematic, since because Access is just a file sitting out there and since folks have to have write access to it in order to interact with it, you are just one stupid/malicious user away from having everything corrupted or deleted. Also, if you go this route, you will be using VBA, which I think is an evil language. (see If programming languages were religions for an entertaining read that is pretty spot on) Also, this method is hardly scalable at all.
  2. or, we could use a web database, which uses annoying "macros" but can be published to sharepoint, which avoids the problem of being exposed to any user out there who wants to mess with things. Still not very good, but I feel this is the lesser of two evils. (It is still better to get a proper web server out there and make a proper web application)

So here is what I did step by step with my commentary. Hopefully it helps some downtrodden developer out there. Sorry about having no screenshots. I'm not allowed to take them from the intranet and post them on the web because of my organization's policies and all.

  1. Make a web database in access.
  2. Set up your tables using the "create"->"table" menus. Please note that to make foreign keys (sort of) that you have to go to "Table Tools"->"Fields"->"More Fields"->"Lookup and Relationship". You can also connect to the userinfo table on sharepoint this way if you first connect to it by going to "External Data"->"more"->"sharepoint list"
  3. Make a main form to operate out of. I used "create"->"navigation" and picked one that made sense for my project. I named this one "Home" when I saved it, and I renamed the subform to be "MainSubForm" (will be important later)
  4. Make a form to put into the navigation's subform. I used "create"->"datasheet" to make a datasheet form.
    1. In "Form Tools"->"Datasheet"->"property sheet" (available when in layout mode), you can enter a table into the "data"->"record source" field. This also supports limited SQL, or you could click on the "..." to build a query using the syntax that access is used to.
    2. After connecting to the table, click on "Form Tools"->"Datasheet"->"add existing fields" and pick all of fields. It is better in my experience to pick them all and then to right click on the columns that appear and "hide" them. This way, you can actually use the data in those columns later on.
  5. Make more forms as necessary. You can connect these forms to the navigation form's sub form by going into layout mode, making a tab and then picking the form you just made from the drop down in the "data"->"navigation target name" field.
  6. Now is about a good time to actually connect to your share point server. Go to "file"->"save and publish"->"publish to access services" and enter your relevant information.
    1. Make backups every time you get a feature working like you want on the sharepoint page. After you get anything to work on the desktop version of access, immediately sync it with sharepoint and test it there, since sometimes stuff will work great on the desktop version, but won't work on sharepoint.
    2. Don't touch the compact and repair button, It corrupted my database and made me start over. If you absolutely have to use it, make a good backup first. You have been warned.
    3. As far as backups are concerned, it will probably be best if you use "file"->"save and publish"->"save database as"->"save as local database". It will get weird if you don't do that because sharepoint will try to overwrite what you have with what you uploaded to it last if you try to use an old file that was simply copy-pasted using windows explorer.
  7. Ok, so now you probably need to have one form interact with another. For my project, I added another subform to the main form with the navigation subform and the tabs. To do this, I put it in layout mode and clicked on "form layout tools"->"design" then clicked on the box thing to the right of the paperclip.
    1. You can show those same datasheet forms inside of this subform, by going to the "property sheet"->"data"->"source object" field and picking the form you want to display.
    2. For purposes of this example, I named this subform "SecondarySubForm"
  8. I wanted to display some data in the second subform based off of what I clicked on in the first. I first tried to use the link master fields/link child field, but it ended up being a pain and erratic because of poor documentation and a lot of magic going on in the background. I ended up seizing more manual control via the event options. This part is the part I wished I had run across most of all during my research. It would have saved me a good day or two of research and much annoyance. Here is how I made it work:
    1. I found it easier to to the actual datasheet form instead of attempting to interact with it by clicking on the subform window in the main form.
    2. In the property sheet, click on "Event"->"on current" and select the "...". This will bring up the embedded macro editor. (this technique will work with any event. It all depends on what you are trying to do)
    3. In my example, I was selecting the data in the second form based off the id in the first (The second table had a foreign key to the first one). I found it to be pretty impossible to send data back and form between subforms until I realized that if committed a coding sin and made some global variables (called "temp vars"), I could make it work. If you can find a more proper way of doing it, go for it, but I'm not convinced it is possible.
    4. Pick "SetTempVar" from the little drop down thing and give it a name (probably something like selected_<name of your variable>) and then for the expression, you can put the name of your id that you care about. Intellisense should pick up up and auto complete it for you. Access automatically assumes that you are talking about the selected row's value. For this example, let's assume I picked the name "selected_main_id"
    5. Then, use BrowseTo. BrowseTo allows you to arbitrarily shove stuff into any other spot in the page, to include other subforms. 
      1. This was the only way I could find for one sub form to interact with another sub form in access. 
      2. I left the object type to "Form", set the object type to the name of one of the datasheet forms I made earlier and then set the where condition to filter it down. The syntax to these where clauses would be  [main_id]=[TempVars]![selected_main_id], assuming that you wanted to filter on a field called "main_id"
      3. The Path to subform control systax is not well documented, and gave me trouble for a bit. Here are some examples of the correct syntax. I hope it helps:
        1. For what I named stuff, it would turn out like this: "Home.MainSubForm" would dump the contents in the main sub form we put in first and "Home.SecondarySubForm" would dump the contents in the second sub form we added to the Home form. 
        2. For what I said earlier, I would need to pick "Home.SecondarySubForm" to get the functionality that I talked about.
    6. Save and close it and test it out. If it works like it should, you will be able to click on the first subform, and it will filter results by "main_id" and shove the results into the second subform.
    7. Please note that sometimes you will get errors from the desktop version of the Access database as you navigate to and from forms that have these "BrowseTo" commands built into the "On Load". Just ignore them. It's Access being a piece of junk, and it should work fine on Sharepoint.
  9. Go ahead and connect stuff together like you need to. Remember BrowseTo and TempVars are your friend when it comes to this stuff. 
  10. If you want to make a subform go blank, the best thing I found to do is to make a blank form, give it an original name like "blank_form" :) and then use the BrowseTo command to fill that subform with the blank form when you need it to go blank.
  11. I wanted to make it so that if a user double clicked on the values in a column that it would allow the user to edit that particular record. Here is how I did that part:
    1. Make a blank form, then set the "Data"->"record source" to a table or query that you care about.
    2. Use add existing fields to add the fields, then add a button control on there. Set visible = "no" for the stuff you don't want to user to see after you have debugged it and are certain it is working as expected.
    3. Save the form as something easy to remember, like "Modify_Employee" or whatever.
    4. Using the property sheet on the datasheet form, navigate to the event tab for the column you care about and open up the macro editor for the "On Dbl Click" event.
    5. Set the Temp Var of the foreign key as shown above in step #8, then do a BrowseTo, referencing the Modify_Employee form you just made, and have it target a subform you would like to put it in, again using the techniques explained above. On the "On Load" event of the Modify_Employee form, you can also do a few tricks:
      1. If you want it to make a new record on that form instead up updating the selected one, be sure to include the line "GoToRecord" command with "Record->New" in your macro. This will insert a new record into the db and set that to the new record instead.
      2. If you want to programmatically set the value of any of the fields, use the "SetProperty" command, with "Property->Value" and set the Value with whatever you would like. If you want it to refer to a variable like a temp var, include a "=" in front, like this: "=[TempVars]![selected_main_id]". You can also set it to functions like "=Date()" for today's date or "=CurrentWebUser(0)" for the Sharepoint User's userinfo id. More on currentwebuser here.
      3. You can also programmatically hide or disable stuff using SetProperty. Just use 0 or 1 for the values to set those fields to off and on, etc.
      4. Please note that if you GoToRecord, it will overwrite whatever was set before that command in the macro, so put any SetProperty stuff after GoToRecord.
    6. Edit the "On Click" event of that button you made, 
      1. SaveRecord will save the changes you just made to the existing record (or brand new one, if you used the GoToRecord technique mentioned above)
      2. Use BrowseTo to change up what displays in what subform.
  12. Another random thing: If you want to use dropdowns or listboxes on your forms to select an id, make them user friendly by changing the number of columns to more than 1 and then set the semicolon delimited list of column widths in the property sheet. Just make sure that whatever you want saved to the db is the first column (which you can set to have a width of 0).
  13. I haven't succeeded in getting data macros to work for me yet. I suspect that the reason is the same as this guy. More to follow as I figure it out.
Posted on Wednesday, July 13, 2016 5:18 PM | Back to top

Comments on this post: A tutorial on using Access 2010 web databases with Sharepoint

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © robertphyatt | Powered by: