Access over remote network - SQL server or Sharepoint Lists? (1 Viewer)

[BiigJiim]

Hi,

We have a split MS Access database, with the FE distributed to users local C drives and the BE on a shared folder accessed over a VPN. I know not recommended, but it worked well enough when the number of users was low. The problem is now there are 30+ concurrent users and we are getting corruption issues (database has a number of Memo fields) and speed/performance issues.

We are evaluating whether to move the BE to either a SQL server or Sharepoint Lists. I know SQL is the best solution, but this is only a short term database solution, so wondering if Sharepoint is more viable. The tables are large with 100+ fields, but relatively few records (2-3000), so the 5000 limit shouldn’t be a problem.

Can anyone give me any pointers as to the pros and cons of Sharepoint Lists? Is this a realistic solution?

Thanks for any help!

Jim

 

[theDBguy]

Hi Jim. Like you said, if this is only temporary and the list of items won't get close to 5000, then SP would be quicker/easier to set up than SQL Server.

However, SP would probably run slower than SQL Server, so I would suggest you do a small test version to see if the speed with using SP would be acceptable.

 

[NauticalGent]

I've done both and SQL Server is hands down superior. Microsoft has pretty much given up on the Access and SharePoint affair so I would stay away if at all possible.

 

[BiigJiim]

Thanks for your responses guys. It is only a temporary database and the Sharepoint option looks like a quick win - not as good as SQL but way better than MS Access BE and maybe good enough for what we need. Also means we only have to make minimal changes to the Front End. We are setting up a test environment using Sharepoint and going to hit it with 30 users to see how it copes.

 

[theDBguy]

Thanks for your responses guys. It is only a temporary database and the Sharepoint option looks like a quick win - not as good as SQL but way better than MS Access BE and maybe good enough for what we need. Also means we only have to make minimal changes to the Front End. We are setting up a test environment using Sharepoint and going to hit it with 30 users to see how it copes.

Good luck, and I'd like to know the results.

 

[Pat Hartman]

If you use Sharepoint Lists, you would NOT run the Access app through a VPN. You would run the app from the local directory and connect directly to Sharepoint. VPN is unnecessary.

 

[BiigJiim]

If you use Sharepoint Lists, you would NOT run the Access app through a VPN. You would run the app from the local directory and connect directly to Sharepoint. VPN is unnecessary.

HI Pat, the VPN is only used at the moment because we are connecting to an Access BE. We are looking at Sharepoint Lists as an alternative to the VPN.

 

[BiigJiim]

Good luck, and I'd like to know the results.

Will keep you posted - will be testing over the new few days

 

[BiigJiim]

Hi DBGuy,

Update as promised. We are now Live using the BE stored as a number of Sharepoint Lists, with 30+ concurrent users.

The good points are that it gives a MASSIVE improvement in performance, and we have had no issues of corruption.

The downsides are that it hasn't gone smoothly and that there are a number of issues which we have had to resolve one by one. For example, Sharepoint ignores any primary keys in your original Access tables and creates it's own at migration, so we had to do a fair amount of messing around with ID fields before migrating. Sharepoint also does not seem to support proper relationships.

We also have an issue where user's changes have been lost because continuous bound forms don't seem to refresh when another user makes a change to a record, as they would do if linked to an Access BE (I have posted that as a separate thread here https://www.access-programmers.co.u...nuous-form-bound-to-a-sharepoint-list.314713/) This doesn't seem to be documented anywhere, but users need to be aware of all these limitations in order to avoid problems.

Conclusion is that it DOES work and is far better than an Access BE over a VPN, but migrating is not nearly as simple and pain free as it could be!

 

[NauticalGent]

This Link has great information on how to take advantage of SharePoint capabilities such as server-side filtering when uses with Access.

Best of luck to you...

 

[theDBguy]

Hi DBGuy,

Update as promised. We are now Live using the BE stored as a number of Sharepoint Lists, with 30+ concurrent users.

The good points are that it gives a MASSIVE improvement in performance, and we have had no issues of corruption.

The downsides are that it hasn't gone smoothly and that there are a number of issues which we have had to resolve one by one. For example, Sharepoint ignores any primary keys in your original Access tables and creates it's own at migration, so we had to do a fair amount of messing around with ID fields before migrating. Sharepoint also does not seem to support proper relationships.

We also have an issue where user's changes have been lost because continuous bound forms don't seem to refresh when another user makes a change to a record, as they would do if linked to an Access BE (I have posted that as a separate thread here https://www.access-programmers.co.u...nuous-form-bound-to-a-sharepoint-list.314713/) This doesn't seem to be documented anywhere, but users need to be aware of all these limitations in order to avoid problems.

Conclusion is that it DOES work and is far better than an Access BE over a VPN, but migrating is not nearly as simple and pain free as it could be!

Hi. Thanks for the update. SharePoint supports List relationships through lookup columns.

Glad to hear you're having some success with it. Good luck!