Access 2013 Template for Sharepoint (1 Viewer)

Dear Forum,

I want to create a small relational database in Access 2013 and then publish it on to sharepoint. I want to be able to set up table relationships and design my forms etc

I found an article that explained how to build and publish to sharepoint using blank web database template in Access 2010 but I am unclear how to do this with 2013 since the template options seem to be either Custom Web App or Blank Desktop Desktop

Which of these should I use for a 'traditional' Access design database that can then be published to sharepoint?

Many thanks

Alex

the template options seem to be either Custom Web App or Blank Desktop Desktop

Click to expand...

I don't have A2013 where I am now, but I recall seeing several available templates for both web and desktop.

Which of these should I use for a 'traditional' Access design database that can then be published to sharepoint?

Click to expand...

Well, what might be considered a "traditional" Access database would not be web compatible. The new web objects have design restrictions that traditional client objects do not.

For example, you can't really establish relationships (at least not in the "traditional" sense) or enforce referential integrity on web tables. This is because web tables actually become Sharepoint Lists when published. The only way to create a "relationship" is by using a lookup field, which, in a traditional Access database would be poor design. Also, you cannot use VBA in web forms so everything must be handled with macros (either table level data macros or embedded macros in your forms).

You can have a hybrid application, with both client and web objects (except the tables - only web tables are allowed in a web app), and it will publish, but only the web objects will function in a browser.

Hi Alexander,

Good question.
Some background first needs to be explained.

Access 2010 introduced the concept of creating a web database that could be published to a SharePoint Server running Access Services. When you publish the web database, the data goes into SharePoint lists on the server and you could use web objects (web queries, web forms, web macros, and web reports) within your browser. You could also create client objects that would only be able to be used if the web database was opened within Access.

To create a new web database in Access 2010, you clicked new Blank Web Database. In order to publish, you need a server running Access Services. Do you have a server running Access Services? Is it local or are you using a service like Office 365?

Now with Access 2013, things are different. Access 2013 web apps while at a high level are trying to do the same thing (create a database that can be used in a browser) the implementation, infrastructure, and design tools are much different than Access 2010.

When you create an Access web app with Access 2013, you are also creating a web database, but all of your data goes directly into a SQL Server database. You're not using SharePoint lists with an Access 2013 web app so you have the potential for much larger databases and we can take advantage of a lot of other features built right into the platform.

So:
Access 2010 + Access Services 2010 = Web Database published and data in SharePoint lists.
Access 2013 + Access Services 2013 = Web app created in SharePoint but data is in SQL Azure if using Office 365 or SQL Server 2012 if using on-prem.

Back to your original question now.
There is no entry point from the Getting Started screen in Access 2013 to create a new blank *2010 style* web database. As you've observed, you can only create new Access 2013 style web apps or local desktop databases in Access 2013. You can, however, open existing 2010 style web databases within Access 2013 and modify them, publish them, etc. You just can't create new 2010 style web databases in Access 2013.

If you'd like I can upload a blank 2010 style web database here which you can use to start creating a 2010 style web database within Access 2013. Let me know if you'd like that.

The first thing you need to figure out though before you go any further is: Do you want to create a 2010 style web database or a 2013 web app? They are two very different beasts. It depends on what you need and the life of the application. Also, a lot depends on what server or service you are using.

Let me know if you have additional questions.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

Thanks for the insight Jeff. Inside/Out was a good resource when I was first investigating the new web apps in A2010. I have a 2010 web app running on Sharepoint via Office 365. Looking forward to your new book because I am interested in finding out what additional design and/or relational tools there are in A2013. Having the data in SQL server or Azure certainly sounds like an improvement over Sharepoint Lists.

Thank you very much for the replies

I only have Access 2013 installed. Sorry not sure of the details of the Sharepoint that we use.

I just know that my IT collegues want me to use Sharepoint instead of writing it as a desktop database and won't support a desktop Access database if it is used by mulltiple users (which it will be in this case)

I have attached a general scheme of what I want to achieve as tables and forms. Do you think this can be done as an Access Web App in 2013?, if yes, I will get going learning how to put it together. Can you recommend any good references or will the design process be intuitive for someone that is used to desiging simple databases. I use macros and wizards, but don't yet write VBA.

Many Thanks,

Alex.

Hi Sean,

Thanks for the nice comments on the 2010 book.

For Access 2013 web apps, things are very, very different.
- 2010 web databases used SharePoint lists
- 2013 web apps, however, use SQL Server for their data source
- 2010 web database data macros were converted to SharePoint workflows and quickflows.
- 2013 web app data macros, however, are converted into SQL Server triggers and Stored Procedures.
- 2013 web app data inserts/edits/deletes are transactional based now because we are using SQL Server. It all gets added/edited/deleted with success or everything gets rolled back.
- 2013 web apps support aggregate queries now.
- 2013 web app views (forms) are completely different and their design surface is completely new. You are essentially designing the HTML view (form) within Access using an HTML designer right inside the Access client shell.
- 2013 web app views (forms) have a couple of new very useful control types with drill-through already built in.

That's just a small list of changes.

I'm curious about your 2010 web database on Office 365. What is your scenario here? What are you tracking? What features are you using in the web database? Has your site been upgraded to the Office/SharePoint 2013 bits? Just curious to see how you're using the product.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

Hi Alex,

I only have Access 2013 installed. Sorry not sure of the details of the Sharepoint that we use.

I just know that my IT collegues want me to use Sharepoint instead of writing it as a desktop database and won't support a desktop Access database if it is used by mulltiple users (which it will be in this case)

Click to expand...

Before we go any further, it's critical to find out from your IT collegues what exactly is your server setup.

You'll need to ask them the following questions:
1. Are you using SharePoint 2010 or SharePoint 2013?
2. Do you have the appropriate licenses and is Access Services installed and configured correctly on the server?

The answer to the first question will determine if you can create an Access 2013 web app or if you'll have to create an Access 2010 style web database.

The answer to the second question will determine if you can even use an Access 2013 web app or an Acces 2010 style web database.

So it's difficult to even begin to offer suggestions, advice, or information until you find out the answers to those questions. The answers to those questions will determine the options available to you.

Can you find out that information and then report back?

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

Dear Jeff,

We are using Sharepoint 2013

Yes, the appropriate licenses and Access services is installed and configured correctly on the server

Appreciate your help

Many thanks,

Alex

Hi,

Ok, thanks for the information.

My vote then would be to create an Access 2013 web app instead of a 2010 style web database because all of the data would be in SQL Server and you'll have extra features available at the data layer as opposed to using a 2010 style web database.

Your IT people will be happy that everything is managed under SharePoint and you'll benefit with the data in SQL Server. You've previously mentioned that you are accustomed to using macros so you should be somewhat comfortable creating all of the UI and data logic for your web app.

There is a learning curve for sure with Access 2013 web apps because the design surface is completely brand new. We also have some new UI controls specific to Access web app views.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

@Jeff

Thank you for the additional info. I will definitely need to do more research on A2013 now. I am curious about what level of control the developer has over the relationships and RI with the back end now being in a true rdbms. Do you manage your own instance of SQL Server through your Sharepoint site? Manage it all from within the client?

I'm curious about your 2010 web database on Office 365. What is your scenario here? What are you tracking? What features are you using in the web database? Has your site been upgraded to the Office/SharePoint 2013 bits? Just curious to see how you're using the product.

Click to expand...

I've been working with A2010 web apps for a while now but mostly for learning purposes, learning the design options for web forms, understanding the new macro system etc. As a side to this I have tried to help others when I see questions about web apps. Other than you, Albert Kallal and maybe a couple others, there doesn't seem to be a whole lot of developers out there responding to web app questions in the forums. As I've learned things along the way I have tried to help those posters when I can.

Anyway, back to your question, I work for an automotive dealership group. I've written several Access apps for various purposes over the years, one of which tracks customer contacts for our outside wholesale reps. This is the app that I recently decided to convert to a web app. Honestly, the majority of the functionality is still client side but there are a few web forms that can be accessed through a browser for when the wholesale reps are out of the office and need access to some customer information. I'm not sure if my Sharepoint site is upgraded to 2013, I'll have to look into it (using Sharpoint Online - Plan 2).

Thanks again for the info. I've you've got any tips on good research sites, I'm all ears.

@Alexander Willey

Sorry to kind of hijack your thread, it's not often you get to talk directly to someone who knows as much about Access web apps as Jeff does.

Hi Sean,

Thanks for the information about your 2010 web database on Office 365. It sounds very interesting. Your approach of using a hybrid type of application using both client and web object within the database is very typical. I think that's one of the big strengths of utilizing Access 2010 web databases and 2013 web apps - using both desktop client features and web database/web app features.

Too often I hear people not happy thinking they need to convert an entire application to be a web database/web app. Those people are then unhappy because there is less functionality in the web features than the client features. I don't see it as an all or nothing; I think the real gains are using both feature sets. Your scenario is spot on in this aspect - you surface a few select elements and data for a select group of users in the browser. The bulk of the application is still using the full feature rich set of the desktop client.

I am curious about what level of control the developer has over the relationships and RI with the back end now being in a true rdbms.

Click to expand...

- You define relationships through the lookup wizard in 2013 web apps. I know some people despise lookup fields but that is the design surface. I've heard people say they don't like the display value aspect of a lookup field but I think those people forget you can just set the ID field as both the bound relationship value and the display value. So you are always storing the ID field and only ever see the ID field. If you use a nice display value though, the UI and controls we autogenerate are taken care of for you.
- In Access 2013 web apps you can create compound indexes. (You could not do this with 2010 style web databases.)
- You can define default values and properties at the data layer.
- You can define field and table level validation rules.
- You can also create data macros at the data level and attach them to the On Insert, On Update, and On Delete table events. These are translated into direct SQL Triggers at the data layer.

Do you manage your own instance of SQL Server through your Sharepoint site? Manage it all from within the client?

Click to expand...

Everything is managed through Access client - everything.
The main goal was to remove all of the complexity from the less-then-professional developer. If you are Using Office 365, you are dealing with a SharePoint site working together with a SQL Azure database. There is a tremendous amount of complexity and moving parts here and Access 2013 just removes most of that complexity from the developer.

Does that help answer your question?

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

AccessJunkie said:

I think those people forget you can just set the ID field as both the bound relationship value and the display value. So you are always storing the ID field and only ever see the ID field

Click to expand...

Right. When I converted my app I just used single column lookups that return only the ID field from the related table. I see no reason to return any other values at the table level.

AccessJunkie said:

Does that help answer your question?

Click to expand...

Yes. Thank you again for your time.

Hi Sean,

No trouble at all, glad I could help answer some questions about Access 2013 web apps. If you have additional questions, feel free to start a new thread and I'll try to answer them.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out (coming soon)
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------