Solved

Access 2003  vs Access 2013 incompatibilities

Posted on 2014-01-06
12
969 Views
Last Modified: 2014-01-11
A client has a heavily-used AC2003 networked app running on a server for the past 10+ years.  Gradually, some users upgraded to AC2007 and AC2010 without problems.

Recently however, several of the users of this particular app were upgraded to 2013 and since then, we have experienced weekly database corruption.  It's not unusual for 3-6 users to simultaneously be updating the same table; and typically these users are a mixture of the 4 versions of MsAccess.

"Splitting out" the program into a front-end/back-end has not helped (may have made it worse)....seems to be the back-end (....Data.mdb) now that is getting corrupted.

Client has placed an order for all new workstations which will come with 2013, I assume; but these will likely not be installed until later in Q1 2014.

Can anyone think of anything a developer can do under these mixed-version conditions, while waiting for the total 2013 switchover?

Sorry so long-winded -- trying to reduce questions up front....
Thanks in advance.
-Bill-
0
Comment
Question by:bcreen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +5
12 Comments
 
LVL 10

Assisted Solution

by:Korbus
Korbus earned 72 total points
ID: 39759965
I know this doesn't answer your question, but though it might be useful info:

Microsoft access databases are prone to corruption, at least in my experience.
You mentioned you split out the back end- You might want to consider using SQL server, rather than an ACCESS engine to store the database on the backend.  This is a much more robust DB engine, specifically made for use over the network with multiple users accesing it at the same time.
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 72 total points
ID: 39759992
When did you split the database, before or after you started encountering this issue?  This "best practice" generally improves stability, not decreases it.

I don't have any applications that are being accessed by more than two versions of access (2007 and 2010).  But none of them have experienced the problems you are encountering since the installation of 2013.

I disagree with Korbus about access databases being "prone to corruption".  Poorly written databases of all kinds are "prone to corruption", but I've been using Access for over 20 years, most of that with multi-user applications where each user has their own copy of the front-end and the backend is shared on a network server.  The only times I've seen a serious corruption problem, like you are reporting, were when:

1) multiple individuals were using the same front-end from the network
2) when people tried to use the application over a wireless network

Having said that, I do agree that using SQL Server as a back-end might be a partial solution to your problems, but it sounds like this is more about 2013 than anything else.
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 72 total points
ID: 39760000
I don't know that it's the version, but rather the network.  I'm assuming with these new versions of Office came new stations as well.  If your running a mixed environment of Windows XP and Windows 7, then that's probably why your having problems.

As Korbus said, conversion to SQL Server is the best option.  Using the SQL Server Migration Assistant, the job is fairly straight forward.  Generally you will get as good or better performance than you do now and you will certainly get stability.

Jim.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 142 total points
ID: 39760015
"Microsoft access databases are prone to corruption, at least in my experience"
In your experience maybe, but not in mine.

"Splitting out" the program into a front-end/back-end has not helped (may have made it worse)."
That would be unlikely in a normal situation.  In fact, that would always be the first recommended step in a resolution to the issue.

mx
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 71 total points
ID: 39760212
Having multiple versions of Windows is more likely the culprit.

Converting to SQL isn't exactly trivial unless your app was developed with that in mind from the beginning so splitting the database is certainly the first start.  You also need to give each user his own copy of the FE that he runs from his own PC.  It would also help to compact and repair the BE on a regular basis and don't forget frequent backups.  www.fmsinc.com has several useful utilities for managing Access applications.

In the mean time, you might want to give the users with old versions of Access, a copy of the A2013 runtime (or A2010 since I'm not sure if Win XP can run A2013).  Make sure you get the 32-bit version of the runtime engine if offered a choice.  Create a shortcut for each user that references the path to the A2013 runtime so you are sure that version is being used to launch the app.
0
 
LVL 12

Assisted Solution

by:funwithdotnet
funwithdotnet earned 71 total points
ID: 39770172
Access 2013 does not support ADP (Access Data Projects); a very popular Access project type. If your app is ADP it won't even run on Access 2013.

Access 2013 inherently supports SQL databases, so anything on Access 2013 is fully SQL & cloud capable.

See: http://msdn.microsoft.com/en-us/library/office/jj618413.aspx
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 142 total points
ID: 39770313
" a very popular Access project type"
Well ... that might be a stretch.

"Access 2013 inherently supports SQL databases,"
That is only the Web App side of A2013, not the desktop side. However, once you create a Web App (which creates the SQL db back end) - even if you don't actually use the browser based web UI, you can link via VBA code to the full blown SQL Server database (SQL Azure) from the desktop side. So, indirectly you can create a desktop db that does connect to the SQL database.

mx
0
 

Author Closing Comment

by:bcreen
ID: 39770831
All answers -- and even just the comments - were very helpful. Decided to give each user his/her own copy of the front-end, instead of them sharing the front-end on a networked drive.
Couldn't really specify a truly "best" answer; so just picked one.
Thank you all!
-Bill-
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39772265
Another landmine for you to avoid--or maybe it is what you have been stepping on!
DON'T make design changes in anything other than the lowest Access version (A2003) that you are running.  If you make changes in an uplevel version, and then in a downlevel version, whatever object you made the changes in shortly becomes corrupt.

Distributing a front-end to everyone locally is very good best practices.  Tony Toews makes a good product for doing that http://autofeupdater.com/ Me, I scripted my own.  One nice effect of giving everyone a copy of the frontend and moving the backend to SQL Server is that you don't have to kick everyone out of the app when you are ready to do changes.

Another option that I just discovered yesterday is using Group Policy Preferences to push out files and file changes.  Quite slick!
0
 

Author Comment

by:bcreen
ID: 39772305
Ahhhh!  Yes, I was making changes using 2007 to AC2003 objects.... hmmmm?

I'll look at autofeupdater as well !  Thanks for your comment . . .
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39772327
You can make changes uplevel -- but THEN you can never go back.  And given that the file reference break, you HAVE to go back to fix all that for the A2003 clients.

And then things get corrupted.

Who gets to make changes? Just you?  Then create and distribute MDE files for everyone else, and ensure that you religiously adhere to the mantra of 'only develop in A2003, only develop in A2003'
0
 
LVL 58
ID: 39773275
<<You can make changes uplevel -- but THEN you can never go back. >>

 That was especially true with A2010; created havoc every time you went back.   Microsoft worked on it and it got better after SP1, but it's still not perfect.

 Follow Nick's advice on this and always do your mods in the lowest version, then bring them forward if you need to.

Jim.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question