Solved

Access 2003  vs Access 2013 incompatibilities

Posted on 2014-01-06
12
931 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
  • 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 47

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 57

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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 34

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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 57
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now