Access Database on Sharepoint

[calvinle]

Hi,

I currently have a database that is used by multiple user.
There is front-end and back-end.
The front-end is stored on user local, and back-end on the LAN.
However, it seems that our server is really slow that when multiple goes on it, it will slow down the database.
It also happen that the table is corrupted and record is deleted from a table.
There is a solution to move to SQL, but that will required the renaming convention to all table, etc.
So I am looking for sharepoint possibility?
Is it possible to put that database on sharepoint and have everyone work on it? Read and write to table at the same time?

Thanks

 

[isladogs]

I've no personal experience of SharePoint but from what I've read, the most common complaint is that it is desperately slow ... probably much worse than you have now.

SQL Server has many advantages in terms of scalability and stability. The Express version is also free and is more than enough for many users. However, if your LAN is slow, don't expect SQL Server to perform miracles either.
You will need to identify what the network bottlenecks are and fix them.

As for converting Access to SQL Server, the migration assistant makes the task relatively simple in many cases. If you use multivalue or attachment fields then you will have some work to do.

 

[CJ_London]

renaming should not be an issue if you use the upsizing wizard in Access for sql server - it will rename for you.

If you have a slow network it doesn't matter what you use as a backend, it will still be slow.

You also need to look at how your db works. take a look at this link and see if any of the points in post #1 apply to you.

https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=slow

not mentioned, but it is a given that the db should be split with the backend on the server and the front end on the users local drive. If it is not split (or is but each user uses the same front end on the server) then that may be why you have corruption. Putting the front end on a local machine should also help performance since there will be less network traffic relating to forms/reports etc.

If you tick all the boxes in the link a better option to defeat a slow network would be to use remote desktop (previously known as terminal server) or citrix. All operations take place on the server, the only network traffic is to do with screen refreshes and it can be run from any type of machine including apple.

 

[isladogs]

renaming should not be an issue if you use the upsizing wizard in Access for sql server - it will rename for you.

The upsizing wizard was removed in access 2013.
However the SQL Server migration assistant is still available and is more powerful than the old Access version

 

[CJ_London]

The upsizing wizard was removed in access 2013

good to know - I'm still on 2010

 

[calvinle]

renaming should not be an issue if you use the upsizing wizard in Access for sql server - it will rename for you.

If you have a slow network it doesn't matter what you use as a backend, it will still be slow.

You also need to look at how your db works. take a look at this link and see if any of the points in post #1 apply to you.

https://www.access-programmers.co.uk/forums/showthread.php?t=291269&highlight=slow

not mentioned, but it is a given that the db should be split with the backend on the server and the front end on the users local drive. If it is not split (or is but each user uses the same front end on the server) then that may be why you have corruption. Putting the front end on a local machine should also help performance since there will be less network traffic relating to forms/reports etc.

If you tick all the boxes in the link a better option to defeat a slow network would be to use remote desktop (previously known as terminal server) or citrix. All operations take place on the server, the only network traffic is to do with screen refreshes and it can be run from any type of machine including apple.

Each user front end is store on their local pc.
The backend only store tables for data.
Even just query a single table takes time when all user are in it.
For testing, I went to the LAN and copied pasted a file of 30MB to the LAN itself and it went over a minute, sometime really quick. So I am pretty sure it’s a LAN issue.

 

[calvinle]

I've no personal experience of SharePoint but from what I've read, the most common complaint is that it is desperately slow ... probably much worse than you have now.

SQL Server has many advantages in terms of scalability and stability. The Express version is also free and is more than enough for many users. However, if your LAN is slow, don't expect SQL Server to perform miracles either.
You will need to identify what the network bottlenecks are and fix them.

As for converting Access to SQL Server, the migration assistant makes the task relatively simple in many cases. If you use multivalue or attachment fields then you will have some work to do.

What is that free version you are referring to? Is it secure?
My database contain many private information and wudn’t be good if the info
Gets leaked out.

 

[Pat Hartman]

If your shop already supports SQL Server, you have no need to use the free express version.

Make sure that your Jet/ACE BE is compacted regularly. With a network as slow as yours, I would not compact it over the network. If you can't log into the server and compact it there, copy the BE to a local computer, compact it, and put it back (after renaming the original)

 

[isladogs]

What is that free version you are referring to? Is it secure?
My database contain many private information and wudn’t be good if the info
Gets leaked out.

SQL Server Express is free and supplied by Microsoft as the entry level version to the SS suite. It is very secure, more so than Access.

As Pat has indicated, if a version of SS is already in use at your workplace, use that. Even better, it should mean there is someone who knows how to administer that.

However, fixing the LAN seems to be the key.