• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2760
  • Last Modified:

Problems with Access DB on Windows Server 2012 R2

Hello,
At our customers, we have the following problem with Windows Server 2012 R2:
On the Windows 7/8 clients there is installed a VB6 application. This application accesses an Access database, which is located on a file share on the server.
Sporadically, maybe 2-3 times in a month, the VB6 application crashes on the clients, the database will get corrupted and needs to be repaired (duplicate entries in tables, primary keys have been removed.) In the event log, there are no error messages from the Access database. only customers with Windows Server 2012 R2 have this problem, customers with 2008 R2 for example, have no problems.
At Google, i have found that it's possible to downgrade SMB 3.0 to SMB 1.0, but i think it's not the solution because SMB 1.0 was used by Server 2003 and XP clients.
Does anyone have the same issues with Server 2012 R2 and Access, or know how to resolve this problem?
Thanks in advance.
0
SirAskALot
Asked:
SirAskALot
1 Solution
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Haven't seen this problem specific to 2012.  I've OFTEN seen Access databases crash.  It's why I don't use them.  I use a SQL Database with an Access front end.  I would suggest, depending on if you still have the source code to the VB6 app, convert it to a SQL database instead.

Also, make sure VSS is disabled.  If memory serves (and it's been a while), snapshots can cause the access databases to crash, so we stopped using them with the drive that Access was storing the data on.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
OPLOCKs has been problematic in the past, but now with 2012, there is no way to turn them off.  A work a round for that is to have two instances of an application running somewhere that connects to the DB.

 With the DB held open, OPLOCKs will no longer come into play.

 I just answered another question where they are trying that to see if it is OPLOCKs causing the problem or not for them.   It may be something else and un-related.

 All things being equal, Access databases should not corrupt and unlike Lee, I've seen the opposite; corruption for me has been infrequent.

But as said, a good solution is to move to SQL Server.  The issue with JET/ACE DB's is that all the DB processing is carried out client side (no server side process), so there are a multitude of factors that can contribute to a DB corrupting.

The free version of SQL server can now handle up to 10GB and with the SQL Server Migration Assistant, it's fairly straight forward.   Your app may need adjustment, but the changes are usually few.

 Then on top of that, you get the benefits of online backups, recovery with roll-forward, etc.

 If you want to stick to JET/ACE, then look for patterns.  Same users, same approx time each month, etc.   It can be anything from the stations, to the networking, to the server OS.

 For example, years ago, many NIC cards had a test protocol installed where the card would disconnect itself from the network, test, and then re-connect.   Only apps that ever would complain were the Access ones.  Reason being is that the cache is maintained on the client at the milli-second level, so data traffic is almost constant.

Jim.
0
 
Dale FyeCommented:
If you decide to upsize to SQL Server, use the SQL Server Migration Assistant for AccessSQL Server Migration Assistant for Access, not the Access upsizing wizard.  But before you do that, make sure that all of your tables contain a primary key.  Without that, you will not be able to edit data in your SQL Server tables or delete records from those tables.

I also add a field of datatype "timestamp" to all of the tables on the SQL Server.
0
 
SirAskALotAuthor Commented:
Now we decided to move the Access database to a virtual Windows 7, running on the Windows 2012 Server.
Thank you for your help.
Regards
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now