UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Sharepoint Foundation And User Advise Please, SharePoint 2013    
 
   
bartjuh999
post Feb 16 2019, 02:56 PM
Post#1



Posts: 6
Joined: 16-February 19



Hi, my name is Bart and haven't been around here for a couple of years. Recently i got back on using Access again.
A couple of years ago i made a Access db with a lot of VBA in it for a small group of users (<10) in Access 2007
The database would be running for a year or 2 and then the idea was that i would become End of Life due to all kinds of online software. However the db still runs users are happy with and don't want to migrate.

So a couple of month ago they wanted to upscale there use and users (it a non-profit organisation) and they are working on a windows small business server 2011. The db is about 30 mb's with the largest table having about 15.000 records


Now for the question / advise smile.gif


I didn't follow the Sharepoint / Azure developement at all tried to look into some of the posts here, but what they want to do is open the db for other collegues so that they can enter data directly into the db. However this has to be done online (webbased) and concerns a lot of users 200+

I tried a sharepoint foundation server (no access support but has unlimited users) made a couple of lists and forms and this should work. For this part i don't need VBA.

So this i would like to do it but please correct me if i'm wrong or should do something else

Host the concerning tables on a SharePoint Foundation server
Make Views for external collegues to enter data
Make an Access database with a reference to the Sharepoint Lists and make forms reports and queries in this db

I thought that in this way the on premisses users (<10) can still use Access and the remote users can enter the data online (these or a kind of timesheets which a user has to enter say once a month)
In this way the large amount of users wont cost licences due to the fact this is a non profit company.


Perhaps not the best solution but the costs are a big issue.


Am i in the right direction with this or to do i need to think or do something else ?

Your input is much appreciated !! Thanks for reading this


Bart

(sorry for my english)






Go to the top of the page
 
bartjuh999
post Feb 17 2019, 02:46 AM
Post#2



Posts: 6
Joined: 16-February 19



No one ? Please ?
Go to the top of the page
 
RobKoelmans
post Feb 17 2019, 03:52 AM
Post#3



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Bart,
I strongly advice to test this first in the simplest proof-of-concept that you're able to assemble. My experience is that SharePoint is useless as a platform for database application data storage. You're talking about some 15K records and only Business Connectivity Services (BCS) aka SharePoint External Lists will work for those amount of records. So, always put you're data in a SQL-Server because that's were BCS expects the data. If it needs to be free of charge, make it Express or Web Edition.

From there, use Flow to make it 3-tier available to for instance PowerApps as well. For data to PowerApps ONLY use the Exec Query option from Flow to run a SQL-Query script. Don't use GetRows a.t.l. The only way to get proper (practically optimal) performance is retrieving JSON by ExecQuery on behalf of a Flow PowerApps action initiator. See all of the videos on our site MetaMicro.nl. Most of them are relevant to what you're heading for. Learn to create simple SQL-server T-SQL scripts to minimize the amount of roundtrips from Flow to SQL-Server (to just one round trip per request).
Kind regards,
Rob
This post has been edited by RobKoelmans: Feb 17 2019, 04:04 AM
Go to the top of the page
 
GroverParkGeorge
post Feb 17 2019, 08:38 AM
Post#4


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


Access from 2010 forward can link to SharePoint lists and work with them in the same way it would work with linked accdb tables or SQL Server tables. While the relational capabilities of SharePoint in this environment are rudimentary, you can define relationships using the Lookup Field method. In fact, if you have an on premises SharePoint site, you can test this out as Rob suggests by using the Access Wizard:

Attached File  ExporttoSP.jpg ( 104.63K )Number of downloads: 4


I don't know whether you'll experience performance problems with 15,000 records in a large table. The last time I was exposed to an Access/SharePoint scenario, we began to see serious problems around 25,000 records. That was a SharePoint 2007 site, with Office 2007.

Which version of SharePoint does your organization have installed, btw?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AlbertKallal
post Feb 18 2019, 02:01 AM
Post#5


UtterAccess VIP
Posts: 2,845
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok the big issue and part is the web system.

We been running a car, but now we need air travel. While air travel is common as cups of coffee, it certainly based on a different technology.

If you have SharePoint, then it certainly is a possibility. However, SharePoint DOES tend towards “internal” users, not external ones.

So, when you say “many” external users, are they company users with an issued logon, or are they actual external customers?

The above is a REALLY important issue (and one often not given the appropriate considerations here).

So “before” you consider SharePoint, you have to really sit down and define who those web users are going to be.

I mean, let’s take this SUPER simple question:

We need a new web user. Can the user self-sign up, what happens if they forget their password? Do you have someone answering the phone all day long to fix or re-set a password? This is a stunning simple question – one that I see companies fail to even consider or address.

SharePoint works great for an existing company of employees, all with ALREADY issued logons.

SharePoint “can” become a public customer (not company employees anymore) system, but it not optimized for that task or process.

So, your “logon” technology, how passwords and users are to be created is an issue that MUST be well defined, and well hammered out.

The next issue is the type of application.

Think of an access application. EVERY user and launch a form and see all records. That makes perfect sense. Then a company realizes why not let the customer see their data, enter their data, and why bother having these customers’ phone up the office and THEN some employee simply fires up access and reads the information, and types in information (how silly is that!!).

If we now take that Access application, and say web enabled it, now EVERY customer using the web system will be able to see all other customer information. Access, FoxPro, SQL server or SharePoint does NOT by some act of magic restrict users from grabbing at and looking at that data. In fact, this OFTEN why Access as a web system is a VERY bad idea.

Once you introduce web, then we talking about serving data to each separate customer, and a system like Access highly optimised to bring up ALL information in the database does not make sense anymore. And SharePoint does not change this issue (unless you design you system to do this).

And keep in mind, using SQL server or any other database system does NOT automatic restrict such customer information. It is YOU the developer that must do this. So, picking the wrong tools not optimised towards this goal often means you are picking the wrong platform for your goals.

Asp.net + SQL server and using an authentication technology (say FBA – forms based authentication) is very cheap. About 10$ per month, and you can have unlimited logons, users can self-sign up, and even re-set their passwords (assuming you want to allow and do this).

Or, maybe your authentication provider going to use their Facebook logon, right?

However, just like when you fire up access, you starting from scratch. So, what features do you need for each user? Can they see previous information they entered?

Or, can they just email you a spreadsheet each month and you import it? (No web or SharePoint required – right?).

So, you have to start with defining how such users are to logon, what kind of logons they are to be issued, and who going to issue such logons (or can users self-sign up like we ARE and DID for using this forum?

So, for that SharePoint data table can all users browse and look at records? (Maybe that like Access is what you need and want).

Using SharePoint, access or SQL server does not change how this is going to work.

However, you certainly need to consider what kind of logons and security system you going to adopt and choosing the correct type of logon and security system will be based on the kind of users, and needs. If your employees need a access like system, and need to look at potential customers to call and deal with while on the road, then all users of that system DO need use of all data just like a typical Access application.

So, defining who needs what and what you need each user of the system to see will often dictate your security logon model and technology you going to choose here.

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

Go to the top of the page
 
RobKoelmans
post Feb 18 2019, 09:39 AM
Post#6



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Quote Albert: "So, your “logon” technology, how passwords and users are to be created is an issue that MUST be well defined, and well hammered out."
I totally agree, Albert. That's why we have those kind of things completely self provisioned on our SharePoint Services. Please see our screen animation videos on that on MetaMicro.nl. SharePoint supports form based users from ASP .Net.

Quote Albert: "Access, FoxPro, SQL server or SharePoint does NOT by some act of magic restrict users from grabbing at and looking at that data. In fact, this OFTEN why Access as a web system is a VERY bad idea." Indeed not by some act of magic but by MS-Flow. MS-Flow manages the Where Clause that's being performed by SQL-Server, just like any other webservice does. Again, see our videos i.c. those MS-Access and Excel. There's no need whatsoever to throw away your old investments in time and/or money.

Rob

Go to the top of the page
 
bartjuh999
post Feb 18 2019, 03:14 PM
Post#7



Posts: 6
Joined: 16-February 19



Thank you for your replies, really great to see your answers and really appreciate it that your thinking along with me.
Yes the logon and the mindset of how to take data apart for users is very important. However i agree that the old system worked and works and it shouldn't be thrown away.

The way Albert set up the reply started to get me thinking (which was his point i believe and thanks for that)

I'm interessted in your reply Rob, and will take a look into your site. Your Dutch also a saw that makes it a lot easier when you read my English hahaha.
Your company offers SharePoint Hosting or does it offer Plugin's and such ? Could you mail me about that ?

Thank you very much for your replies again.

Will consider how to handle this and will update this post when i will take further steps

Gr.

Bart

Go to the top of the page
 
AlbertKallal
post Feb 18 2019, 03:48 PM
Post#8


UtterAccess VIP
Posts: 2,845
Joined: 12-April 07
From: Edmonton, Alberta Canada


@Rob wrote:
QUOTE
MS-Flow manages the Where Clause that's being performed by SQL-Server, just like any other webservice does.


No, web services do not manage your where clause for you. That web service can’t know if you pulling records from some project or invoice table and figure out to restrict those rows. You might use customer number, maybe email, or OFTEN some parent record.

If you want a customer to see their project or say their past invoices or whatever information and display it? It is YOUR code that HAS to make the restriction of the data – the web system does not make such restrictions. It has ZERO clue as to what records belong to who.

That invoice table will not even have the users email or logon information attached to those tables.

You have a bunch of quotes, or some project. How is that row of data restricted to the one customer? By email? By some customer ID and that how you attached projects to that customer.

You want to toss up a form for editing of data, such a system will no more automatic restrict rows on a database query then Access will.

Web systems do not by some act of magic restrict rows to a given user. YOU THE DEVELOPER have to choose what columns and how such data is going to be restricted to the one user. The database system, and the web system DOES NOT do this for you.

The only part of the web system that restricts records to the one user is your authentication stack. And that usually means a custom authentication provider (I written more than one – and even in this case the ONLY help you get from the web system was the ONE record that defined their logon.

ANYTHING ELSE you query from that database will ONLY restrict rows based on YOU the developer providing that filter and restrictions. The web system does ZERO in this regards and does ZERO to restrict a query you fire off that pulls records from SQL server.

And yes, I have install + setup SharePoint and implemented FBA authentication for SharePoint.

You migrate your data to the instance of SQL server, and start developing the application as web based, the web system DOES NOT restrict records to each user – you the developer provide that filtering and criteria and design. You in your web code building a SQL query on a table, you going to get everyone’s rows – not by some magic ONLY rows that belong to a given user.

So if you using your system to pull say an invoice balance, how does the SQL query you fire off restrict the rows to that one user?


What does that code and SQL query look like? If you don’t add a filter to that query pull from some database, you going to get everyone’s rows. There is NO magic restricting for these rows when building and writing web software, and the web system and the instance of SQL server running on that system offers no automatic help of any kind to the code you are writing for that web application.

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

Go to the top of the page
 
RobKoelmans
post Feb 18 2019, 06:05 PM
Post#9



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Albert,
I don't understand your point. MS-Flow receives a data request message from the Access Desktop application. The request also contains a user-id and a SHA-1 digital signature that is created from a shared key (a 1024 byte string provided to the user), concatenated with a timestamp that's rounded to the last full 5 minutes (and to the next 5 minutes after 2,5 minutes). De signature is passed with the request and the user-id by Flow to a stored procedure that looks up the shared key of the user and also assembles the signature. If the signatures match, the stored procedure returns the requested data that Flow automatically receives as JSON and returns that to the requesting application. The stored procedure most of the time looks up some id's that it uses in the where clause of the actual select. If necessary, those id's include a user or a group-id that user is member of. Works perfectly and is completely safe if you ask me.
Kind regards,
Rob
This post has been edited by RobKoelmans: Feb 18 2019, 06:07 PM
Go to the top of the page
 
AlbertKallal
post Feb 18 2019, 07:21 PM
Post#10


UtterAccess VIP
Posts: 2,845
Joined: 12-April 07
From: Edmonton, Alberta Canada


@Rob,

You are missing my point.

However, most don't realize (or grasp) how web security works.

My point is all too often people make assumptions about how web systems work.

They don't provide that row level security automatic.
(SharePoint lists can in fact do this, but it not the norm for general web development).

It is the designer of the software that has to write code and make these restrictions.

The web system DOES NOT do this for you.

I mean, if I send a salesforce of 3 reps into a city.

I might have 3 sales reps in that area.

So, anyone of the 3 sales reps can add new contacts but ALSO of course update existing companies and contact information. And while all 3 sales reps might have SOME personal areas and information (such as their own sales volume and commissions due), I MOST certainly want any contact edited by ANY sales rep to show that information (such as which rep called them last – worst possible thing is to have two sales calls to a company from two different sales reps in a day – makes them look like bozo the clowns and you lose sales doing that).

So, contact history and updates is going to be viewable by all sales reps.

So in above, the simple “act” of adding rows to some database DOES NOT mean that the web system now makes the choice for that system.

ALL sales reps will see these updates. All sales reps will see NEW contacts added. All sales reps will see last call time and information.

If the web system were to attach security to each user who adds a row, then what happens when you want a web report of ALL your sales reps in that area? What happens if one sales rep needs to see those other contacts and history of who called that customer?

And as my above example shows, be this a web form (or same access form), you OFTEN want everyone using the system to see that data but sometimes you don’t.

Access forms don't restrict records to ONE user of the Access application.

THE SAME applies to web based software - everyone can see everything UNLESS the developer makes this choice.

You are making promoting the very COMMON issue I am talking about.

Your response to this poster again suggests at the web
system by some magic act will restrict the data for each user - it does not!!!

It not clear if you are missing this basic point, or are trying to make a different point. This is a SIMPLE but critical concept that people using web technology have to grasp.

And your making this same mistake (suggesting that the web system will restrict data based on each user - they don't!!!! - ANY KIND of narrative that suggests otherwise such as your post is a HUGE disservice to the readers here.

The web system does not make this restriction for you. If in access you want to use the workstation name, or some logon to restrict records, then that is UP TO YOU!

And the exact same applies to web based - you can use logon name, or some other identfication. You then use that to restrict the rows of data.

But that information does NOT on its own restrict rows of data - it is YOUR code and YOUR decision.

The web system (or Access desktop) does not and cannot make this decision for you.

You not explained ONE BIT how you decide if a simple row in a database is viable by all users of the system, or only the one particular person/user of that system.

The web system cannot make this kind of decision unless that web system can somehow read a developers mind.

So, I am not talking about such a system being safe I am FLAT OUT making the point that YOU THE SOFTWARE developer has to make the decision and write the code and decide WHO can see what row of data. The web system, the web security and the web software does NOT make that decision for you.

FBA will automatic restrict the loading of a web page based on membership in a security group. So as a devleoper, I don't have to write code to prevent load of a web page. However, loading a web page (or not) based on FBA has ZERO to do with what rows of data they will seen when that web form or web page is loaded.

So this means you the developer do NOT have to write that security code for which web pages the users can see or not.

So yes, the web security system can and will allow you to control who can see what web page, and you don’t have to write code to do this.

However and Again;
That security model DOES NOT control who can see what row of data. It is YOU the developer that decides this issue, not the security system you choose.

How this words is the same in Access if you wanted to hide all records for each user of the system – it is you the developer that has to cook up, and write SQL etc. that restricts the rows of data – the web system, or Access does NOT do this for you.

I mean, you have to explain then how you keep each sales rep from NOT seeing each others sales volume, but ensure that all sales reps can view all customer information, and all updates by all sales reps.

Until you explain the above then readers here will again have the false impression that the web system restricts those rows - it does not!!!

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

Go to the top of the page
 
slavescu
post Feb 18 2019, 08:11 PM
Post#11



Posts: 11
Joined: 17-February 19



Link msaccess front end to a mysql back end , you can host db locally for free or online for peanuts
you will probably need to adapt the access tables, PK, naming and so on to be able to export them over but the process it's not that complicated , that way you will end up spending peanuts and truthfully , it opens possibilities to future php or java with mysql should you need it
This post has been edited by slavescu: Feb 18 2019, 08:13 PM
Go to the top of the page
 
RobKoelmans
post Feb 19 2019, 03:33 AM
Post#12



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


Hi Slavescu, it doesn't really matter what database the data is in. You don't want to open it up (to some 200+ users the question is referring to) on database socket level. MS-Flow has a https action initiator which makes it very easy to deploy a webservice (on monitoring, error recovery especially if you want the management to be done by others than yourself). Although the basic functionality may be, It's certainly not trivial to write something like that in Visual Studio ASP .Net, Ruby etc. We have several WebAPI2.0, REST and oData webservices running at customers and the difference is that we need to support those and with Flow we can simply instruct them to support and maintain that themselves. As described earlier here, it can also be done extremely safe and you cannot beat the performance. The Microsoft people are not fools and they invested a lot in this platform. Flow also opens up very spectacular possibilities to non-professional developers (for instance ms-Access/Excel, but also PowerApps developers). It's extremely powerful.


Albert, how would FBA know what data should be personal and what shared if not the logged on user information was submitted in the dataset selection/filtering somehow. We've been working for many years now with form based authenticated users and never stumbled upon some row based security mechanism the developer is not involved in. In fact, I can easily proof there isn't. There is Windows domain and native security management in SQL-server of course (but not FBA) and is possible to get some row based security on the level. Is that what you're advising or referring to?

Kind regards,
Rob


Go to the top of the page
 
slavescu
post Feb 19 2019, 03:56 PM
Post#13



Posts: 11
Joined: 17-February 19



He was mentioning cost as being a big factor, that is why I've mentioned mysql , otherwise any database will do if you eliminate the dollar amount factor
Go to the top of the page
 
RobKoelmans
post Feb 19 2019, 06:04 PM
Post#14



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


SQL Server also has free editions, they work better with MS-Access and they support Business Connectivity Services.
Rob
Go to the top of the page
 
slavescu
post Feb 19 2019, 07:07 PM
Post#15



Posts: 11
Joined: 17-February 19



you can not really compare mssql free edition to mysql, i could prove you wrong on so many levels , and by the way , msaccess does work flawlessly with mysql , if you know to do it properly, but it doesn't pertain to the question at hand
Go to the top of the page
 
RobKoelmans
post Feb 20 2019, 02:46 AM
Post#16



Posts: 489
Joined: 25-November 14
From: Groningen, Netherlands


It wouldn't surprise me if you're totally right on what you're saying. MySQL is a serious product just like Asterisk for instance. We use Access Web Apps on our platform however. With that, a reseller or end user can manage him or herself entirely with exports/imports, test replicas, backups, new databases on an app level that's been taken out of an organisational store in SharePoint. That makes us enablers more than service providers, so we're married to SQL-server fortunately and unfortunately. We have our licenses on just two cpu's (with a large amount of ram (512 GB) and ram-cached ssd's as disk caches). Up to now, we can even use our Microsoft Partner License for that because that's cpu/core related, not user. I think we have some 150, 175 databases for customers and for ourselves and the resource load is hardly noticable. It's the write spikes that normally cause the first delays but those are going directly into ram in the 1 TByte SSD's. Works much better than AWA ever did on Office365. In there you had spontanious short delays in Access Web Apps but those are gone completely. We have one customer who's writing a couple of hundreds of bytes some 4 to 5 times a minute and a 15 to 20K XML file from a spectral analyses device every half hour and I've never noticed any delay whatsoever in that or other databases. They're constantly doing some heavy queries from PowerBI on that database a well.

Rob
This post has been edited by RobKoelmans: Feb 20 2019, 02:53 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 06:50 PM