Microsoft Office Migrations

Thinking of upgrading to Microsoft Office 2010?

A quick search in Google Insights shows a very interesting trend:
Searching on the words "microsoft office upgrade":
The top five in order are:

1. United States
2. Australia
3. United Kingdom
4. Canda
5. Germany

In the last 90 days, it's just the United states followed by the United Kingdom.

Does this mean that the US and the UK have put off their upgrades until now in order to reduce costs etc?


Then looking at just Australia, you get QLD followed by NSW and then VIC.
Does this mean the floods put QLD off and that NSW is just trying to cut costs?

There are a few pain points, especially in Microsoft Excel and Microsoft Access or (MsAccess) as it's commonly referred to in web searches.
There's a conversion tool aimed at MsAccess, from a mob called converter technology. They don't seem to have a handle on Excel or even a way to check the network is optimized for the combination of Windows 7 and Office 2010.


We even developed a network speed/locking tester written in excel that can show up issues before Office goes into production. Now to write one in MsAccess that covers issues with DAO/ADO connections taking forever.
One of the most interseting outcomes of the Excel Speed Tester is that a good network environment can read and write your file fastger than alot of PC hard disks can. This means that having your files on an optimized network can be faster in some cases than on your hard disk. We do say quite carefully, "In some cases", because once a network gets busy, most of us can feel if not see the effects.
For more information on the Speed tester email me via http://www.macroview.com.au.

The whole migration story gets murkier when Office 2007 is also thrown in as this is a nightmare version in terms of upgrading/coverting macro driven applications.

Unfortunately for us Excel and MsAccess Developers, Office 2007 will plague us for years, and even now, companies are migrating to Office 2007, because some ignorant adviser said this was less risky than office 2010. They obviously haven't consulted us!

###

Migration to Excel 2010 and Access 2010 the easy way

Migrating to a new version of Office used be quite painless. With the major openxml format changes and major overhaul to both Microsoft Access and Excel, there are many breaking changes that can break many an existing application.
With the right amount of planning, most issues can be dealt with prior or when the migration goes ahead.
What is disturbing is the amount of mis-information about the different versions of Microsoft Office.
This article focuses on what you need to be aware of, and what are the best choices when upgrading the desktops and notebooks of all your users.

Recently a client asked me to assist with migrating to Excel 2007 using a $20K tool.
This appeared to have been written in ancient vb and doesn't seem to be built to cater for the two biggest issues with Excel Migrations.
What I couldn't believe was how developers make tools without knowing the product or add extensions to their products as an afterthought.
The Tool did work okay against Microsoft Access databases, although still haven't put this under pressure to test how it deals with issues we have discovered in simple access databases like fields not being big enough so forms just crash erratically.


Firstly there's the choice between the Microsoft Office 2010 32 bit or the Microsoft Office 2010 64 bit version.
Recommendation from Microsoft is to upgrade to the 32 bit version.
Only a few super power users will need the 64 bit version, and that's most likely due to badly designed cubes or lack of good database guys to roll up information nicely.

Why not 64 bit version of Microsoft Office?
Because that's causes alot of applications with code to break. Also many addons will not work, due to library differences between 32 and 64 bit office. It's not because the 64 bit version is buggy. It's different.


Version of Windows 7 to go to?
Go to the 64 bit version of Windows 7. Any 32 bit office app that doesn't work in Windows 7 64 bit version should be trashed or the developers given a talking to. There are some issues about the Windows 7 Service Pack 1 causing problems for users who don't have the same service pack. Microsoft has a few workarounds.
On first glance it appears to be mainly office 64 bit users who would be effected. The solution being to change the code to use late binding on the adodb (MDAC) libraries. Thes are used mainly to link to sql and other dobc databases. Most Access developerws went back to dao development, unless there were sql links, so alot of code can be fixed quickly and easily.


Do you upgrade your Microsoft Access databases to SQL Server?
No. Not unless you know what you are doing. If the Access databases only have 5 to 10 users, then keep them in Access. If you have more, be ware that sql can cause more locking and other issues, if you do not know what you are doing.

What about Excel files. Should we upgrade them?
Only if you want to as many excel workbooks might be linked to others.
The solution we came up was to add an addon in the Excel ribbon that dealt the linking and the other major issues so the users could do their conversions with less pain, or one click of the button as they say.


What items are missed or done the wrong way?
The new colouring themes are often forgotten about or not done correctly.
Ironically, the word guys try to dictate the styles, but Excel and PowerPoint users are mostly effected by the colouring. Some clients have spent thousands on graphic designer ideas for styles and fonts, but these guys don't always have a grasp on Office 2007 or Office 2010 themes as they live in their adobe software world.

###

Tom Bizannes is a Microsoft Certified Professional with a business background.
He works for a Microsoft Gold Partner in Sydney, Australia
http://www.macroview.com.au

How to add a hyperlink on a intranet to open an access database

Using a unc path with file: in front of the unc path should do it.
e.g. File://Servername/ShareName/Folder/../something.mdb

You can even use the above method to point to a shortcut.
Noting that shortcuts end in .lnk (that's a L not a capital i)
e.g. File://Servername/ShareName/Folder/ShortcutToDatabase.lnk

That way you can specify the link to run the 2003 or 2007 version of access and to open in shared mode – so no locked out users.

The biggest issue is probably that spaces might need to be %20 instead .

Tom Bizannes
Microsoft Access Design and Development
Sydney, Australia
http://www.macroview.com.au

Error 2227 Issue with clipboard in Access 2007

Error 2227: The data on the Clipboard is damaged, so Microsoft Office Access can't paste it.

Had to catch the error and advise the user to close excel first!

This nightmare was caused by an patch to excel 2007.

So put this error message at the bottom of the procedure:
Noting it was in an access 2003 database but the error occurs when running access 2007:

If Err.Number = 2227 And Application.Version = "12.0" Then
MsgBox "There is an issue with an update of excel 2007 and you need to close excel before pasting", vbCritical
Else
MsgBox "Error pasting and updating data" & vbNewLine & Err.Description & vbNewLine & "Error No:" & Err.Number, vbInformation
End If

Startup options for Microsoft Access

You can add command line switches when opening up access via a shortcut or bat file.

To go one step further we had to also use a specific security file.

See below for the bat file script:

@echo off
"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" %~dp0MyDatabase.mdb /wrkgrp %~dp0MySystem.mdw
cls
@exit

This opens using access 2003 a database called MyDatabase and a Security file caled MySystem.

What is nice is that everything is relative to where the script lives...

Apparently this opens in shared mode and the other command line options are:

/excl   exclusive access.
/ro     read-only access.   
/user   Starts Access using the specified user name
/pwd    Starts Access using the specified password
/excl   Opens the specified Access database for exclusive access.
     To open the database database for shared access in a multiuser only environment, omit this option.
/x  macro    Starts Access and runs the specified macro.
          Another way to run a macro when you open a database is to use an Access AutoExec macro.                        
/cmd         Specifies that what follows on the command line is the value that will be returned by the Command function.
          This option must be the last option on the project command line.
          You can use a semicolon (;) as an alternative to /cmd.
/nostartup   Starts Access without displaying the startup dialog box
/wrkgrp      Starts Access using the specified  workgroup information file.               database
/runtime     Starts Access in run-time mode.

And these look good to use if you want to schedule compact and repair etc
Although, we put temp tables in separate tables to avoid database bloat and conflicts, so compact and repair isn't required.

/compact [target database]   Compacts and repairs the Access database
/convert [target database]   Converts an Access database in an earlier version
* Specify the source database before using the /convert option.

For any more detail just Google:
"How to use command-line switches in Microsoft Access"

###
Thinking of Migrating Access to Sql Server?
Talk to the guys who know all the tricks....
http://www.macroview.com.au

Office Developer Conference 2008

This was really good.

Full kudos to Graham Seach for Organising this.

Couldn't go to all the sessions...but learnt a few new tricks.....

I presented Real World Access and Real World Excel...Two rapid fire ones about migrations to Office 2007 where we helped clients to upgrade microsoft office and discovered a few gotchas..

Wow these really stretch you...It's easier to talk for an hour than for 10 minutes!

Got back and now designing an Access 2007 picture gallery/viewer for your pc...
Access can read and show pictures in a directory without having to load them into the database.

Regards,
Tom

Office Dev in two weeks time!

The Office Development Conference is only two weeks away!

If you are thinking of going, you need to let them know via email so there will be enough Pizza!
Oh my gosh!...Will need to get my Cholesterol checked before this weekend!

Same as last year, cannot go to all the sessions I want to go to!

FYI – I am doing two quick rapid fire sessions, as I will be the only Excel Expert speaking apart from Grant at Angry Koala talking about data mining....
plus I couldn’t help myself and just had to do something on Access rather than just sit and correct people all the time!

Will have to hand around a few WISDOM cubes……

Regards,
Tom Bizannes
Sydney, Australia
http://www.macroview.com.au