Access 2013 hybrid app linking to office 365 sharepoint (1 Viewer)

Techworks

Registered User.
Local time
Tomorrow, 04:31
Joined
Feb 2, 2014
Messages
12
Hmm...where do I start!

I have a number existing applications built in MS Access (V97-2007) running on Access and/or SQL Server back ends that have lots of VBA code behind forms.

I'd like to put the data in these up on the web and its just not viable to rebuild these in Access 2013 as a "custom web app" as this format does not support VBA and I doubt this format would provide the richness of UI that my desktop apps have.

So the obvious option is the hybrid model retaining my front end app with its forms and vba, and have linked tables reading data from an Office 365 instance of a sharepoint list (which is a SQL Server Azure database)

I would expect this is the model that probably 75% of the established Access developers must be looking at, yet I can find one a few scant examples of importing 2003/2007 data into a sharepoint list, or creating a new web app, and linking a desktop front end to it using odbc.

Here are the three methods I have tried, which have raise a number of questions about how to manage these sharepoint lists that are created:

1) create a new Web App name: NZDABOP_WEBDATA
use the "Get exernal data -access" menu and import tables from my desktop backend database.
ok great, now have data, can see it through this access web app, and in browser when I publish it.
Now try setting up an odbc connection to this sharepoint db.
Use "User" type as "file DSN" and "Machine" ones dont work.
Install Sql Server Native Client 11.0
go back and open NZDABOP_WEBDATA and get db name,
login password etc and paste these into odbc
check the "Use string encryption for data" (cause some other post said to!)
Test connection and get CONNECTION FAILED error:
"client with ip address xxxx not allowed to access server. To enable access, use the Windows Azure management Portal or run sp_set_firewall_rule on master database to create firewall rule for this ip address"

I note there is also the option under external data-more-sharepoint to link directly to the sharepoint list, but when I try this I only get the UserInfo system table showing up to select from and none of my imported tables.

..yes I do have enable read-write connections turned on!

Ok so I gave up on this method and tried the following

2) create new web app name NZDABOP_WEBDATA_WIZ. now have empty web database.
Then open my existing local db back end database and use the sharepoint wizard to upload the data to NZDABOP_WEBDATA_WIZ
Wizard says all tables imported with no errors.
My linked table icons in my desktop app have changed to yellow so it looks like they are now reading from sharepoint, but when I re-open NZDABOP_WEBDATA_WIZ none of the uploaded tables show up!

3) go back to the NZDABOP_WEBDATA web app and pick the "File-info-Report on my data" open to create a desktop app with read only linked tables to the data.

Yep works, but is read only so no use!

Ok so now look at the properties of these read only linked tables and try re-creating my odbc connection using these setting but with the read/write login.

finally after several tries I finally get an odbc connection to work with read/write...god knows why it wouldnt work first, second or third time, but at least I now its working and I have linked tables to my NZDABOP_WEBDATA web app in method 1 above and my desktop app seems to be working now with the data "in the cloud"

So now for my questions:

a) if you want to link your access 2013 desktop app to a office 365 sharepoint Azure db, should you use odbc or the sharepoint option?

b) I would have thought option 2 was the better option, as it created the linked tables to the uploaded sharepoint lists (tables). When I tried this option my desktop app has the linked tables and shows the data, but how do I get to the sharepoint lists to edit/change the actual tables in sharepoint? If I go to office 365 admin-site it show my web app as a recent site. If I click on this link if gives following

"Well done! You've successfully created an Access app. Now it's time to start designing. Start by adding some tables in Access.
Open this app in Access to start adding tables."

If I click on the link, it presents me with a save dialog box for an .accdbw file type! Ok try saving it, then open it in access and I just get a blank web app with options to start adding tables. Where are my tables?? I can go back and open my original web app form the access 2013 file open dialog box drilling down to my team site and then can see my data, so what the accdbw file????

c) how can I manage these sharepoint lists once they have been created. e.g I want to delete my first two failed attempts at importing data into the web app.

I can delete the tables and auto generated forms that were created by the import, but how do I actually delete the azure database sitting behind the sharepoint list alias? There doesnt seem to be an option to delete if from within Access 2013. Am I supposed to be able to manage it from my Office 365 admin console using the "Site" menu? Or do I need to install SQL Server Managment Studio (express) to be able to see and delete these databases? or Azure management portal??

d) so how can I now distribute my app so others can use it?

I've signed up to office 365 small business premium. and have one license.

So I'm guessing I can just buy more licenses for each user, give them copy of my front end desktop app, and and they just sign into office 365 and will be able to access the sharpoint linked tables? Is that how it can work??.

Or I could set up one dedicated read/write user for the web app and get each user to connect to Office with this one login?

Or assume I could rent some third party azure db space?

I'm sure I'll have more questions but that probably enough for now.

Thanks in advance

Grant
 

AccessJunkie

Senior Managing Editor
Local time
Today, 08:31
Joined
May 11, 2006
Messages
278
Hi Grant,

I think there is some confusion here.

When you create an Access 2013 web app, the data is all stored within a SQL database - *not* in SharePoint lists. If you are using Office 365, the data is stored within a SQL Azure database. If you are using an on-premise server, the data is stored in a local SQL Server 2012+ database. SharePoint serves as the host for the app (URL, permissions, etc.) but the data is stored outside SharePoint.

Access 2010 style web databases in contrast stored their data in SharePoint lists but that severely limited the performance when you had a lot of data because SharePoint lists weren't really designed for the amount of data that typical Access desktop databases contain.

So are you trying to create a 2010 style web database here or a 2013 web app?

In general the concept of a hybrid approach using an Access 2013 web app should work nicely because you can keep your existing rich desktop front end except it will now just be linked up to the SQL Azure tables driving the web app. You then get the benefit of being able to utilize the web app to surface some of the data in a web browser. You're certainly not required to do that, but there are a lot of scenarios where that comes in very handy. it just opens up more possibilities for you.

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

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 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
----------
 

Techworks

Registered User.
Local time
Tomorrow, 04:31
Joined
Feb 2, 2014
Messages
12
I'm only dealing with Access 2013 here. I will upgrade my existing apps front end db to Access 2013 (.accdb) and put the back end data into office 365 sharepoint (which I understand is actually an SQL Server Azure db).

OK so my confusion about sharepoint lists and Azure db's is related to Access 2010 web apps that did actually use sharepoint lists.

Thanks
 

AccessJunkie

Senior Managing Editor
Local time
Today, 08:31
Joined
May 11, 2006
Messages
278
Hi Grant,

Ok, thanks for the clarification. One last note - Access 2013 can be used to create/edit both 2010 style web databases and 2013 style web apps. That's why I wanted to be sure what you were trying to do.

I believe you are right on the correct path with a hybrid approach. When creating an Access 2013 web app I believe a lot of people think you have to completely abandon everything about the desktop client and that is simply not the case. When I give presentations and talk to customers about our Access 2013 web apps, I point out that you just need to think of the web app as a different back end for your data. In much the same way of upsizing an Access desktop database with a JET or ACE back end to a SQL Server back end, moving your existing application into an Access 2013 web app is essentially the same thing. You can still leverage the full power of your rich front end Access desktop database(s) linked up to the data which is now in SQL Server or SQL Azure when you use a web app. On top of all that you get a web browser interface for your data as well essentially for free which you can utilize as much as you want or as little as you want.

Now, back to your situation. It sounds like your existing web app (or more than one) are either not setup correctly or are incomplete.
I would do the following things (in general terms here):
- Delete the existing web apps you've created if you haven't already made changes to the data because it sounds like you've missed some steps here.
- Create a new empty web app to start again on Office 365 within your site.
- Import your existing data into the web app from your other data source(s).
- Note that you might need to redo some relationships since the web app has specific requirements on utilizing lookup fields for the relationships. I would work through these issues before going any further. Depending upon what you need to change here, you might need to make some changes to your existing ACCDB front ends later on.
- Once you have the tables and data just the way you want, then open the firewall and read/write and read/only connections for the web app tables (SQL Azure tables actually) using the entry points in the Backstage in the Access web app client UI.
- Link up your existing front end Access desktop databases to the web app tables using the connection information from the previous step.

At this point you should have existing desktop front ends now using the SQL Azure table data. You can then choose to customize the web app browser interface for this web app if you'd like or choose not to use it at all. Note that Access already gave you a huge jump start here by automatically generating two views for each table you imported earlier.

Hopefully this information should get you started. Let me know if you need additional clarification on anything.

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

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 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
----------
 

Techworks

Registered User.
Local time
Tomorrow, 04:31
Joined
Feb 2, 2014
Messages
12
Thanks Jeff

All makes sense except:

"Once you have the tables and data just the way you want, then open the firewall and read/write and read/only connections for the web app tables (SQL Azure tables actually) using the entry points in the Backstage in the Access web app client UI."

So how do I "open the firewall"?

I've now got my odbc connection working, but only for a "User" type connection, but if I try to create a file DSN connection it bombs out. Does Access 2013/Azure not support file DSN ODBC connections?

Thanks

Grant
 

AccessJunkie

Senior Managing Editor
Local time
Today, 08:31
Joined
May 11, 2006
Messages
278
Hi Grant,

There have been good posts on this topic in the UtterAccess forum so rather than repeat everything here in this post, I'll provide some links to the topic which should get you going.

The first link I would look at though is this recent blog post by one of our Access MVPs. It has a really good walk through on the subject.
This is the blog post:
http://www.devhut.net/2014/01/13/ho...access-database-in-office-365-azure-database/

In addition, here are a couple of forum posts which should help:
http://www.utteraccess.com/forum/Connect-Web-App-Database-t2013865.html
http://www.utteraccess.com/forum/Update-Query-Access-2013-t2004324.html&st=20

Hope that information helps unblock you.

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

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 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
----------
 

DukeOfDew

New member
Local time
Today, 15:31
Joined
Apr 25, 2014
Messages
1
Hi,

I know this post is a bit old but I was wondering if I could just bother AccessJunkie for a little bit more detail on one of your points.

You said "Link up your existing front end Access desktop databases to the web app tables using the connection information from the previous step."

Does this mean that I can take a Custom Web App that i have on office 365/onedrive and connect it to a fully functioning front end stored on the users machine?

If this is that case, how would I go about doing this as the only options I can find are about splitting a database not combining them.

If anyone else can answer this question feel free and thank you for taking the time to help.
 

AccessJunkie

Senior Managing Editor
Local time
Today, 08:31
Joined
May 11, 2006
Messages
278
Hi,

The following link I posted previously gives a real nice walk-through on how to take an Access desktop application and link it up to the tables in an Access 2013 web app (the tables are actually SQL tables behind the scenes).

Here it is again:
http://www.devhut.net/2014/01/13/ho...access-database-in-office-365-azure-database/

Hope that helps.

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

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 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
----------
 

s0upy

New member
Local time
Today, 15:31
Joined
Sep 14, 2017
Messages
1
d) so how can I now distribute my app so others can use it?

I've signed up to office 365 small business premium. and have one license.

So I'm guessing I can just buy more licenses for each user, give them copy of my front end desktop app, and and they just sign into office 365 and will be able to access the sharpoint linked tables? Is that how it can work??.

Or I could set up one dedicated read/write user for the web app and get each user to connect to Office with this one login?

Or assume I could rent some third party azure db space?

I'm sure I'll have more questions but that probably enough for now.

Thanks in advance

Grant

Hi. Thanks for the info in this post, and the helpful comments. The thoroughness of the OP's questions made me metaphorically leap for joy, realising I'd finally found someone who was asking all the questions I've been asking myself as I look to make several of my split Access databases available online, thus opening them out to a wider audience. You've got me to the point of knowing what must be done; all I have to do now is to do it.

(I looked into this a couple of years ago and did a lot of testing with Sharepoint lists and Web apps and ended up concluding that it could not be done, so I gave up. I'm returning with a more time-critical need now, so I'm determined to get this working this time round.)

However, the OP's question d) doesn't seem to have been addressed. Not can I find anyone else talking about that particular point elsewhere. But for me (after assimilating the info in this post, and associated links) this is the only remaining burning question now.

How can I distribute an app (i.e. a database back-end) so others can use it?

The FE is easy: each user within an organisation will use my FE desktop apps, and I've written my own updater code that makes deployment of the FE quite seamless. But what about the BE?

Buy a new license for each user? (In my case, each user would be an organisation.) If so, can each BE web app be restricted to a single user (or organisation)? This would mean all data for all different BEs would be hosted on my Office 365 domain rather than the client's, yes? Or should I be trying to get the BE hosted on their 365 domains?

Or do I need to start looking at Azure to accomplish this?

Thanks for your help. Once I know the framework for deployment, I can get going on with all this. I feel I'm exposing my naivety by the seemingly basic questions I ask, but also MS's lack of transparency on guiding existing Access developers to make the inevitable transition to the cloud.

Thanks for your time.
 

Users who are viewing this thread

Top Bottom