ODBC Problem with Office 2010

I have an Access Database with several queries created specifically for use within Excel.  I created ODBC connections within Excel 2007 to grab a specific query and populate the spreadsheet.  The import is configured to just put in the values and allow Excel to format everything.  The data changes often, so the refresh happens manually.

I started the database/spreadsheet/odbc in Windows XP and Office 2003.  It was working fine.  I upgraded to Windows 7 (skipped Vista) and Office 2007 and it continued to work fine.  I updated both the database and the spreadsheets to the newer formats without issue.  It continued to work properly until I upgraded to Office 2010.

Now, When I go into any of the spreadsheets and run REFRESH ALL from the Data ribbon, the spreadsheet will update the DATE and COMMENT fields but not the TIME fields.  Those fields in Excel have no values in them at all.  I tried recreating the ODBC link and the preview shows the data but the spreadsheet will not import it.  The fields with Excel are configured properly, because if I manually type in a time it will show properly.  There are no errors generated within Access or Excel when I run the refresh, so it's not FAILING to run just not putting the data in any TIME field.

Has anyone else run into this?  Any fix?  I hope I've described it enough.  Thanks.

Hi,

 

I suggest checking the following settings in Excel 2010:

================

1.       Click File > Options > Trust Center > File block settings.

2.       Make sure the following settings are not checked:

Microsoft Office Query files

Office Data Connections files

Other Data Source files

 

Please take your time to try the suggestions and let me know the results at your earliest convenience. If anything is unclear or if there is anything I can do for you, please feel free to let me know.

 

Best Regards,

 

Sally Tang

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

Sally,

    Thank you for the suggestion.  I checked and all three items currently do not have checkmarks.  I would think that these options would block ALL data from coming in and not just the time fields.  I never get a message letting me know that there is a security risk or anything.  No message at all.  Just no data in those fields.   :(

   BTW, one of the fields that DOES come in properly is a DATE field, so it's not a problem with the DATA (date and time come from the same 'TYPE' of data) but merely when the field is formatted to parse the TIME.  I currently have all of the failing fields set like "23:00" (24-hour no AM/PM).

   Also, if I run the same data refresh with the same files on an XP system with Office 2007 (the only other system I have for testing this issue), the entire process continues to work properly, with the TIME fields updating as expected.  Thanks for the help.

Michael

I have exactly this same problem in Excel 2010, but not in 2007. Did you ever get this resolved?  It's driving me nuts since i use the TIME field every day.

I have the same problem as well.  Using an ODBC link from one spreadsheet to another.  Works fine in 2003 and 2007 but in 2010 the time data comes through as 0.00.  All other data, including dates, comes through OK.  Given that (in Excel, at least) time is just a number, formatted to look like a time, I tried changing the display format for the relevant column of my source file so the times looked like ordinary numbers.   The ODBC now works!  It's looking very much like a bug.  Interesting the moderator (Sally Tang) who responded above hasn't come back to you, despite the recommendations not solving the problem.  My method is not very statisfactory, but may be a workable solution.

Did you ever get an answer for your question? I have Access 2010 but when I try to make an ODBC link the only choices I have are SQL Server? How do I get to be able to have Access mdb as a choice?

Hi All

I have the same issue here and I'm going though it with MS Support of another thread, but with ODBC Drivers are you using as I've tried MSSQL 2008 Driver and MySQL 5.1 Driver and these pull in the TIME field correctly, but the IBM Universe ODBC Driver pulls the TIME's in a 00:00:00.

Thanks

Nigel

Hello all,

I'm experiencing the same issue when extracting TIME data with the IBM Universe ODBC drivers from Excel 2010.

I don't believe this is an issue with the actual ODBC driver itself as I have performed the same query with the Microsoft Query tool and all of the TIME values appear correctly.

Performing the same query from Excel 2003 on the same machine also works perfectly, so it is isolated to Excel 2010.

Has anyone had any luck with this?

Thanks,

Nick.

Had same issues. I found that the OS was 64bit windows 7 and access was installed as 64 bit. Had many issues with 64 bit access. So uninstalled 64 bit version and installed 32 bit version.  You may want to check your bit version of MySLQ connector.

 

I am now having issues with the ODBC connection being used for multiple users. The ODBC connection fails even if the setting are correct in the ODBC.