Upgrading from Access 97 to 2010

Question

Hi all,
I am trying to upgrade an application written with Access 97 to Access 2010 for a client. I converted all mdb file to the new accdb format, updated the VBA references, and the table links.
I am able to run all forms and reports. I am having one issue and I would really appreciate the help.
The issue is in the form called invoice that ha a subform called InvoiceItems. InvoiceItems subform has a subtotal field in its footter which is referenced in the subtotal fields in the mainform

On submitting the invoice (button click event), the following statements (summary of them) are executed

Save record
Run recalc on the form
Validate business rules

Rules validation failes because it seems that the recalculated fields (fields with formulas) are blank on both subform and main form. They however get refreshed after I clicked OK on the messagebox that says that the validation had failed

I am using Windows 7 64bit and Office X86. I will try it today with Office 64 and see if that makes a difference.

Thanks in advance for the help :)

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 1

Hi Lajak Tech,

Thank you for posting.

I will involve other engineers to see whether they can give you some suggestions about the problem. There might be some delay about the response. Appreciate your patience.

Best Regards,

---

Bruce Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Thanks Bruce.

I have an update to do, I was took advantage of the XP mode and installed office 2003 on it. I upgraded the database to 2003. I am getting an automation error when opening the form related to one of the subtotals fields in the footer of the sub report (don't see that error in access 2010). That error doesn't show up when I hit F9 after the form is opened. Clicking the button to submit the invoice works on Access 2003/Windows XP.

I copied the converted to Access 2003 database to Windows 7/Access 2010. Surprisingly, Access didn't prompt me to upgrade it to the 2007/2010 format. Same issue as described in the first post still exist.

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 3

Hi Lajak,

Based on what you have described it sounds like this may be a timing issue and Access 2010 on Windows 7 is just running faster than Access 2003 in XP Mode.

My suggestion would be to try simply adding a call to "sleep" after recalculating the fields on the form and before running the validation rules. To do this:
1) Add the following declaration to a VB Module:
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal ms As LongPtr)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal ms As Long)
#End If

2) In the VBA code call "Sleep(x)" where X is the number of milliseconds to wait. I would suggest starting with 10,000 (or 10 seconds). If that "fixes" the issue you can then decrease the number until the problem returns.

As a test you could also try installing Access 2003 directly on Windows 7 and/or installing Access 2010 under XP mode. This may help to understand if this is related to the version of Windows or the version of Access.

If that that does not resolve the issue and you wish to pursue this further you should open a support incident. The following article should provide you with instructions on how to do this.
http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Best Regards,

Donald M.
Microsoft Online Community Support
--------------------------------------------------------------------------------
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually
answer your question. This can be beneficial to other community members reading the thread.

Answer 4

I will give that a shot. I already tried sleeping for 1 second and that didn't help. I will try 10 seconds. I will also try to rewrite the expression for the subtotal since I am, sometimes, getting automation error in XPMode/Access 2003.

Were there any breaking changes for writing expressions for calculated fields between access 97 and 2000/2003??

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 5

One more question and execuse my Access illiteracy. Can I install Access 2003 and 2010 side by side? Do I need to unistall 2010 first?

Thanks

Ahmed

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 6

Ahmed,

While it is possible to install multiple versions of Access on a machine but we do not recommend it. Because you are also intermittently getting the automation error in Access 2003 in XP Mode we know that this is not dependent on the version of Access or the version of Windows so there is no longer a need to install multiple version of Access on your machine.

Information about how to use Office 2010 suites and programs on a computer that is running another version of Office
http://support.microsoft.com/kb/2121447

Best Regards,

Donald M.
Microsoft Online Community Support

--------------------------------------------------------------------------------

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 7

The automation error is not related to the original issue. It only occurs when you load the form for the first time and I got the fixed.

I went ahead and installed the Access 2007 runtime on XP mode. I ran the .mdb databases. I had the same problem as Access 2010.

A friend informed me that Access 2007/2010 have issues when running recalc on hidden/invisible fields. I will check that out tonight.

Cheers

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 8

Hi Ahmed,

Were you able to resolve the issue by making the fields visible?

Best Regards,

Donald M.
Microsoft Online Community Support
--------------------------------------------------------------------------------
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 9

I had to do few things to get it to work. I will soon update this post for the benefit of others and close it.

Thanks for the help

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 10

I had to do the following to fix the issue:

Make hidden fields visible but move them behind other controls

There was code on the main form accessing subtotal control in a a subform. I added a control on the main form that points to the subtotal control on the subform. Code now accessing the control on the main form

In the main form, the validation was looping through the records on the subform using

DoCmd.GoToRecord , , acFirst and

DoCmd.GoToRecord , , acNext

I replaced the above code with

 Dim rsClone As Recordset
    Set rsClone = FormName.RecordsetClone
    rsClone.MoveFirst
    While Not rsClone.EOF

.....

rsClone.MoveNext

Wend
    rsClone.Close

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 11

Would you be up for posting the errant db on a public skydrive folder?  This seems to be a situation that could be handled in a much more natural way.  If you are up for it, I can give you a SkyDrive link, or if you have your only Public skydrive folder, please give us the link.  Basically, I think if some of us had your db, there would be better way.

Please know that in sharing the db, it would be expected that the data contained within would be completely bogus!

---

Brent Spaulding | Access MVP

Answer 12

Hi Brent,

I really appreciate the offer. Unfortuantely, there are many tables with senitive data and it will take a while to clean them up. The database application is called Price Rite Plus. And it seems that it is only available for Access 97. I haven't done VBA coding in almost 7 years. But I can tell, the VBA code behind those forms is not pretty :)

Thanks again.

---

Ahmed is an independent consultant in Ottawa, Canada specialized in .NET, Biztalk, WCF, WPF, TFS and other Microsoft technologies.

Blog: http://lajak.wordpress.com

Twitter: ahmedalasaad

Answer 13

No problem, I completely understand -- Good luck with your what seems to be like a monster of a project!

---

Brent Spaulding | Access MVP

Answer 14

Hi Ahmed,

Just to update you, I have a little knowledge on Access and I have a client requirement to upgrade our Windows XP , Access 97 application to Windows 7, Access 2010.

• This application involves more than 20 forms and sub-forms with VBA
• Reports generation

If you can share/guide me on step by step pre and post upgradation process/procedure from Access 97 to 2010 will be greately appritiated.

Quick Questions:

• What are the thing to be in handy before migration?
• Is there anything, do i have to re-create?
• Performance issues?
• Any expected issues after post migration?

Urgently need your help... waiting for your response

Thanks in advance

---

Mohammed