?
Solved

Access 2003  vs Access 2013 incompatibilities

Posted on 2014-01-06
12
Medium Priority
?
995 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 216 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
Dale Fye earned 216 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 216 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 426 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 38

Assisted Solution

by:PatHartman
PatHartman earned 213 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 213 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 426 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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