Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Access 2003  vs Access 2013 incompatibilities

Posted on 2014-01-06
Medium Priority
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.
Question by:bcreen
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
LVL 10

Assisted Solution

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.
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.
LVL 58

Accepted Solution

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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.

LVL 39

Assisted Solution

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.
LVL 12

Assisted Solution

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
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.


Author Closing Comment

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!
LVL 26

Expert Comment

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!

Author Comment

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 . . .
LVL 26

Expert Comment

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'
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.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

610 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