This have worked for me before and I remembered that I had to install something extra to make it work.
Then I guess you are asking yourselves, why did it stop to work. Well I’m getting to that :/.
What is the reason that this is not just working?
By some reason Excel 2010 doesn’t support synchronizing with SharePoint 2007. What you can do is connect to a Access database or some other smart solutions instead. But who doesn’t want the real solution?
What you actually need is a component coming from the 2007 version of Office to get this to work. I remembered that this was the fact, but not which add-in I actually removed the other day. Well I had to remove this 2007 add-in since I wanted to install swedish proofing-tools in my 2010 installation and it wouldn’t let me do this since I had old versions. Well yes… I know… what can I say.
Now I’m sitting here not being able to synchronize my time reporting lists to my budget sheets in Excel, which is kind of frustrating. So I just have to find that add-in again!
Guess what I have!
Solution to your problems is the 2007 Office System Driver: Data Connectivity Components.
The file or add-in you should look for is the: AccessDatabaseEngine.exe
Errors that I experienced until I reached the solution
First was when I tried to open SharePoint List as an Excel data sheet. This is done by selecting “Export to DataSheet”.
(Sorry for the Swedish images)
This works perfectly until the file is opened in Excel. A big windows alert window shows up with the message:
Initalization of the data source failed.
Check the database server or contact your database administrator!
The second error that actually led me to the answer was this message that I got after trying to connect to the SharePoint list via a Access database.
Failed to connect to the server. Reason: The 'Microsoft.ACE.OLEDB.12.0'
provider is not registered on the local machine.