ODBC import with Excel 2007 with Query Wizard

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rob Nicholson - 22 Apr 2007 13:15 GMT

Really strange problem with Excel 2007 and the Query Wizard. I've done this
hundreds of times with Excel 2000 and 2003. The set-up is Windows XP, Office
2007 connecting via ODBC to a SQL 2000 server via a ODBC system DSN
connection.

After selecting the DSN connection from Other connections, the Query Wizard
asks you to choose the tables and columns. All of the tables are shown but
in the columns, some are missing. Certainly the primary key and text
columns.

If you cancel using the column choosers and switch to entering your query
manually (e.g. select * from companies) it works fine.

BTW - which package does Query Wizard belong to? Is it part of the operating
system (Windows XP) or the Office package? I guess I'm asking which service
pack would update it.

Any ideas?

Thanks, Rob.

Reply to this Message

Nick Hodge - 22 Apr 2007 15:39 GMT

Rob

MSQuery is part of Office. There is no service pack for 2007 yet though.
Try using an OLEDB driver rather than ODBC and see if that works out

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog: www.nickhodge.co.uk/blog/

FREE UK OFFICE USER GROUP MEETING, MS READING, 27th APRIL 2007
www.officeusergroup.co.uk

> Really strange problem with Excel 2007 and the Query Wizard. I've done
> this hundreds of times with Excel 2000 and 2003. The set-up is Windows XP,
[quoted text clipped - 16 lines]
>
> Thanks, Rob.

Reply to this Message

Rob Nicholson - 22 Apr 2007 22:48 GMT

> MSQuery is part of Office. There is no service pack for 2007 yet though.
> Try using an OLEDB driver rather than ODBC and see if that works out

One thought I did have was that I had to install the proofing tools from
Office 2003 first to fix the problem with Outlook Express spell checking
everything in French. This is a well documented "feature" of Office 2007
whereby the proofing tools it supplies can't be used by Outlook Express.

I wonder if I installed a bit more than just the proofing tools and that is
causing this problem.

I'm just building a VM to see if I can get it working with Excel 2003 to
check it's not an environment problem.

Cheers, Rob.

Reply to this Message

Rob Nicholson - 23 Apr 2007 19:30 GMT

> I'm just building a VM to see if I can get it working with Excel 2003 to
> check it's not an environment problem.

It appears to be some problem with Query Wizard talking to SQL 2000 running
on Windows 2003. I've got a development VM running Windows XP and SQL 2000
Developer to which I DTS'd the database and it works perfectly on that.

Hmm, bit worrying if the production SQL Server is unwell :-(

Cheers, Rob.

Reply to this Message

Rate this thread: