Solved

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

Posted on 2014-10-14
4
607 Views
Last Modified: 2014-10-19
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!!!!
0
Comment
Question by:pabrann
  • 2
4 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40379760
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.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40380182
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.
0
 

Accepted Solution

by:
pabrann earned 0 total points
ID: 40380729
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.
0
 

Author Closing Comment

by:pabrann
ID: 40389843
Thank you very much for your guidance. I will follow your instructions and hopefully things will continue to improve...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

13 Experts available now in Live!

Get 1:1 Help Now