Sharepoint help!

[GingGangGoo]

I have an existing database in 2013, and I just got Office 365 with a Sharepoint site up and running. Now I'm trying to wade through all this, esp the differences between using Sharepoint and creating a web app. As I understand it, if I use a web app, I will have to rebuild quite a bit of my db, as I've used a lot of vba and junction tables, etc. I just don't have a lot of time to go back I experimented instead with exporting my tables to the SP site and that worked, in that each table is there, looks like a neat little list and can be edited and my desktop db updates. Nice but now what. How do I make the SP site UI look more like my db UI, with forms? My goal is to have all the users at our two separate locations able to open ? What? their copy of my db? the sharepoint site? and view, enter and edit data, and run reports. I am totally lost on this. I've never yet split a database, so not even sure if I need to do that, how to do that. Any help will be appreciated.

 

[Rx_]

In the web version, none of the vba will work. There are basically no events. They have come up with a set of macros 1995 style.
There is no wizard I have found that can help.
The Web version is probably good for the users who want to look up information. It is OK for entering basic information if it doesn't require validation efforts.

The best place for the web version as presented a few weeks ago, is to keep the client version for the power users, and the web version for the casual consumers.

The other web option is to use Citrix or Remote Terminal to distribute your application "as is". However, Microsoft is big on the "web" version regardless of if it can't perform anywere close to the desktop version.
Depending on your application, don't expect it to perform like the VBA version.

For splitting a database:
Will you use SQL Server (or Azure the web version).
http://www.access-programmers.co.uk/forums/showthread.php?t=223173&highlight=migration+assistant
This is a free tool to migrate data to SQL Server.
It is possible the UI on the Web is designed to meet some web standard.

If you are just splitting to an Access engine, post this on the Tables forum.
You might peform a search first, lots of articles at this form's site.

B.T.W. how many actual users do you envision using your Database?

 

[GingGangGoo]

In the web version, none of the vba will work. There are basically no events. They have come up with a set of macros 1995 style.
There is no wizard I have found that can help.
The Web version is probably good for the users who want to look up information. It is OK for entering basic information if it doesn't require validation efforts.

The best place for the web version as presented a few weeks ago, is to keep the client version for the power users, and the web version for the casual consumers.

The other web option is to use Citrix or Remote Terminal to distribute your application "as is". However, Microsoft is big on the "web" version regardless of if it can't perform anywere close to the desktop version.
Depending on your application, don't expect it to perform like the VBA version.

For splitting a database:
Will you use SQL Server (or Azure the web version).
http://www.access-programmers.co.uk/forums/showthread.php?t=223173&highlight=migration+assistant
This is a free tool to migrate data to SQL Server.
It is possible the UI on the Web is designed to meet some web standard.

If you are just splitting to an Access engine, post this on the Tables forum.
You might peform a search first, lots of articles at this form's site.

B.T.W. how many actual users do you envision using your Database?

The whole no VBA thing is one reason I am working hard to make a hybrid approach work. At this point I am trying to see if moving the tables to my site as a backend, and then pushing the whole thing to a web ap (which I may or may not ever use) as a front end that can be shared, is the right solution.
I have experimented with the first part, and was able to add a record from both sides, but for some reason, on my side, the junction tables got cleared out. This is not workable, because I need those junction tables for my forms and reports.

 

[technician]

I use a sharepoint access web based database and have been doing so for three years to great success

if you design web forms then they are published as a web page and can only use macros

however in sharepoint you can have access forms queries , vba, macros all published to sharepoint

the company database use currently has users in the uk in the
north
midlands south

beauty of this type of hybrid database isit can run both on and offline and can resync the data at your will

if you want any assistance please feel free to pm me

paul

 

[21Aberdeen]

HI Paul, I need your assistance/learn to put my database in Sharepoint to achieve some success. I was using shared drive/local server and now have to use SharePoint for others in different areas to access it. Problem is users could not open the file placed in sharepoint directory/folder.

Thanks

 

[Albert D. Kallal]

You can send the back end tables up to SharePoint/365, and then continue to distribute the front end to your users.

The “trick” with SharePoint (SP) tables is there are several considerations:
During the up-size process, the PK (auto numbers) are changed. This means that ALL of your relationships MUST be CAREFULLY setup before an up-size. This includes often (and overlooked) simple tables that drive say simple choices in a combo box, and the ID is stored in a column.

The other two issues of SP tables are:
Only standard PK (auto number, long type) are supported. This means your relationships cannot be based on a text column, and compound primary keys are not allows. So all relationships can work just fine, but ONLY if your design is basic PK and FK (foreign keys) relationships).

Performance:
I don’t recommend tables larger then 10,000 records. And for the initial up-size table REALLY need to be smaller then 5000 records (there is a reason/bug for this – indexing does NOT take if tables up-sized are greater then 5000 records). I pushed tables up to 80,000 records, but the up-size process should have tables less then 5000 records unless you have SharePoint on site.

Note that use of this SharePoint/365 tables option DOES NOT require any kind of web publishing. You thus have to distribute your VBA client application to users, and they will edit data via linked tables to the tables on SharePoint.

Another possible alternative is to simply up-size the back end to Access 2013 web, since Access 2013 web does NOT use SharePoint tables, but now uses SQL server tables. As a result, then row counts can be much higher. In this setup, then again your plain Jane Access front end will used linked tables to SQL running in the cloud.

The advantage of the SP tables is you don’t need sql server skills – but there are performance and table size limitations. And as noted the SP option allows your application to run in “off line” mode until such time internet connection is available and then a “sync” of data occurs.
With SQL tables, no such off line mode is available.

The above choices thus allow you to keep + use your existing desktop database.

If you go the web route, then existing VBA and forms cannot be used, but both the web application part and existing desktop applications can connect and use the data in the cloud.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada