Crashes and Errors in Access application after upgrading to SQL Server 2012 from SQL Server 2008

I am having a terrible time with a Microsoft Access 2010 system running in 2003 mode. We upgraded to SQL Server 2012 from SQL Server 2008 and have had serious problems. There are two main forms for an order entry system and both are experiencing Write Conflict Errors on a form with some bound controls and some unbound. These forms have a "Save" Command button that saves data on the unbound controls.

The interesting thing is that this application runs perfectly with no errors on my old Windows 7 system with SQL Server 2008.

In addition to the Write errors, Access crashes frequently. As a result, Control Source values set via VBA for various text boxes are being hardwired in the Control Source properties. When this happens, the correct values are not refreshing in these text boxes. I thought my new code were causing refresh problems and started building in a series of "Requery" and "Save" actions, trying to force the refresh, with unsuccessful results.

i have tried numerous ODBC connections using Native Client 10, but I haven't found a permanent solution.

My client has numerous systems running different versions of Windows, Office, etc. One new system they have running Windows 8.1 with Office 365 started crashing when loading the order entry header form for orders. I worked on this problem using "stops" and "break points" in code trying to find the offending line of code. Finally I found a subroutine that initiated the crash. There appeared to be nothing in this code that was incorrect. I swapped a DIM line with an executable line (why, I don't remember), but walaa, no more crashing. Just swapping two lines of code should have done nothing as one was the only line executing.

Well, I received a call the next day telling me the system was crashing again.

Please help, this system has been running for my client for over 15 years. Every time Microsoft upgrades their versions of Office, Operating Systems, and SQL Server, the system becomes less stable.

Would it help to try eliminating the 2003 mode? I made a quick effort to save the database in 2010 mode but it would not save properly.

Thanks for any suggestions!!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It's a little hard to tell what's going on here....

First "2003 mode", which I'm assuming you mean the DB is in MDB format.   No, there is no advantage in regards to what your experiencing.  A2007/2010 support MDB's fine.   You won't have access to ACE specific features, but outside of that, there is no issue.

But what's troubling is that you cannot save in it .accdb format.  The question is why, because it should.

As a long time Access developer, you I'm sure are well aware of DB corruption and you probably have some or some type of problem with the DB.

To start sorting out the issues, I would:

1. Create a new MDB file.
2. Import everything into it
3. Make sure it compiles without errors.
4. Make sure it can convert to an .accdb

 That brings you to a clean starting point.    The next issue is SQL 2012 and data types.  Not sure what if anything changed for your BE DB between 2008 and 2012, but I would check the data types carefully unless you simply moved the DB's to a new server.

 Then you come to all the "normal" stuff that can cause problems; un-patched  Office installs, network problems, etc.

 Beyond that and in regards to the write conflicts, you can often have timing issues/logic bugs where when you move to a faster/slower machine, things don't work like they should.   Basically it boils down to you doing something in a way that you should not have, but got away with it till now.

 Moving to a faster machine is a problem though, because now the events happen in a different order (say a time going off in the background doing some things while a user is trying to save or trying to control/cancel the order of events with checks).  These are a lot harder to pin down sometimes.   What I'd offer here is that if you have stuck to simply using bound forms "as is", then look else where.

 But if you've made Access jump through hoops to do what you want (like controlling things with your save button), then there is more a chance of something like this being a problem.

  It's hard to offer anything specific though because you have some much going on.   Your just going to need to keep chasing it down.

 One thing that is critical; the DB stays in the format and is modified only in the oldest version you are supporting and a DB touched by a newer version is never used by an older version.

 For example, you have 2003 still in use, and a FE is being shared with someone that has 2010.  Make sure every user is getting their own copy of the app and you would only develop in 2003.

You never said why you elected to not convert the app from A2003 to A2010 in the past although you mentioned that attempting it now raised an error.  If you are not trying to support users who still have A2003, then I would start by converting to A2010.  The error is disturbing and as Jim said, there may be corruption at play here but rather than rebuild as A2003, I would rebuild as A2010.  Before you attempt the conversion, make sure you have no compile errors.  An app with compile errors will not convert.  Then compact and repair and even give the /Decompile switch a try.  If you still can't get A2010 to do the conversion, then create a new, empty A2010 database and import the objects a group at a time.  Start with the tables, then the queries, then the macros and modules.  Set the necessary references and compile.  Next do the reports.  The final step will be forms which is where the corruption is most likely to be.  Save the database at each step, compact and repair.  Compile frequently.  You may need to do the forms one at a time, if you can't identify the corrupted one.
pabrannPresidentAuthor Commented:
Thanks Jim and Pat. I really appreciate the guidance. My client does have two remaining systems requiring A2003 but they told me that they will upgrade if that continues to be an issue.  

I have created a version that seems to work for my client on all systems tested except their new system running Windows 8.1 and Access 365. They just called and told me that Access crashes on my command button unloading a quote management form. Previously that system crashed when loading an order entry form.

What I had to do to get this latest version is to copy the MDB to my old Dell 8300 running Windows 7 / SQL Server 2008.  The system runs flawlessly on that system. I opened, compiled, did compact and repair, and copied back to my windows 8.1 system and it worked with no Write errors.

Unrelated Rant:
Incidentally, my Old Dell 8300 saved my new Dell 8700 Windows 8.1 system shortly after purchasing. When I bought my Dell 8700, I installed a new extra disk drive for backups. Well, the new drive was bad and My 8700 with Windows 8.1 didn't know how to handle it. The system would not boot at all and I received messages about the system being locked. I had to remove both hard drives leaving only the solid state drive. After removing the drives, the system would boot. So I had to connect both drives to my old 8300 Windows 7 system which was able to repair the one disk and tell me the second was bad. If I hadn't had my old system available, my new 8700 would have been hosed. It seems to me that Microsoft has really failed us in a big way, in operating systems and Office. Sorry for the rambling.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pabrannPresidentAuthor Commented:
Thank you very much for your guidance. I will follow your instructions and hopefully things will continue to improve...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.