We are considering moving to SQL backend because of data corruption problems.
30 users
What SQL is recommended?
Will the front end work seamlessly or is there a lot modifications needed?
We are considering moving to SQL backend because of data corruption problems.
30 users
What SQL is recommended?
Will the front end work seamlessly or is there a lot modifications needed?
What version of SQL Server you mean? You can try Express, which is free.
With linked tables, the app may work without modification, depending on your specifics. You gain a lot of tools you can use to enhance performance, but you can add them as needed. There are some things that work with Access tables but not ODBC tables, so only testing will determine how much modification you'll need to do.
Which version do your recommend?
Our server is Windows Server 2012 R2 standard edition 64-not (virtual) 16gb ram. Intel xenon cup 3.0ghz (4cpus) solid state hard drives.
Like I said I'd start with Express (2017 if you meant that):
https://www.microsoft.com/en-us/sql-...-2017-editions
Non-Express editions aren't cheap.
Just curious...... do you know WHY you are having corruption issues?
Is your dB split?
The BE is located where each user has R/W access?
Each used has a COPY of the FE on their local computer?
It's been running fine for two years on a 2003 server with the split database and the front ends locally .When we switched to the 2012 virtual server is when the corruption started. We switched back to 2003 server. Running good again.
We are currently averaging around 5 concurrent users . That will probably go to 15 when we add new applications .
Our IT guy says we are using the virtual machine as a file server and the server can't be the problem it has to be the program .Since access doesn't have error logs we can't figure out the problem . Since we will have to upgrade eventually we might as well do it now .
I agree with your IT guy, Access on the new VM server should not present a problem. I suspect it's a connectivity issue on the new box. Access is not a stateless database and as such needs to see a constant connection to the file server directory where your BE is stored. Ensure your connection to the new FS stays active. I would ask your guy to add a policy to ensure the network adapter stays active and there are no sleep settings active in the power plan.
This could also be related to the SMB protocol in use. Have your IT guy check to see which version is in use and possibly enable an older version to test.