Solved

Access 2003  vs Access 2013 incompatibilities

Posted on 2014-01-06
12
914 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

705 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

15 Experts available now in Live!

Get 1:1 Help Now