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
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