MS Query cannot access table in Vista and MS Office 2007 RRS feed

  • 問題


    my desktop with Windows XP pro and MS office XP pro using MS Query can access the table in SQL Server database, but another desktop with Vista business and MS Office 2007 pro cannot. The error message information told that the table connecting by other user or without access right, but it is not true. How can I solve it ?
    2008年7月18日 上午 05:59


  • Which version of sql? How did you connect to it? What's completed error message?

    2008年7月18日 下午 02:21
  • I tried it on both SQL2000 and SQL2005 server, but had the same result. I access the table through Excel 2007, i.e. Data -- From Other Sources -- From Microsoft Query -- Choose data source -- Query Wizard (choose column) menu

    I can neither expand the table in the window under 'Available tables and columns' nor add table . The only selection is cancel and quit to MS Query. In MS Query -- Add tables, select table and click add, with the error message of "Can't access table ......".

    2008年7月21日 上午 03:41
  • Does your windows account have permission on those sql servers?

    2008年7月21日 下午 05:22
  • I am the owner of this database in the SQL server. I have the rights to access all database in the sql server. There was no problem for me to access this database when using windows XP with MS office XP. But cannot access tables when using windows Vista with MS office 2007. The login name and access procedures are using the same account.
    2008年7月22日 上午 01:23
  • Would you please post the *exact* error message?

    2008年7月23日 下午 12:43
  • The exact message:


    Can't access table DataBaseName.Domain\User.TableName



    2008年7月24日 上午 04:18
  • Is the table in schema 'Domain\User'? It doesn't look like schema name.
    2008年7月24日 下午 03:33
  • "Domian\User" is the owner of the table.


    2008年7月25日 上午 01:37
  • Hi KL

    Have you try to use SQL Management Studio to access the database?

    May be you can try that to ensure whether Vista cause the problem or just the Office 2007.
    Try the Windows Authentication first, if it does not success then try the SQL Administrator Login

    Not sure if it could help.

    2008年7月25日 上午 08:01
  • Hi ChiYau,


    I have tried difference login name (owner, administrator, sa, ..) on my Vista with MS Office 2007 desktop, but had the table access problem. It must be the Vista issue.

    The combination I have tried without any problem, i.e

    Windows XP with MS Office XP

    Windows XP with MS Office 2007

    SQL Management Studio to access the database


    2008年7月25日 上午 09:50
  • Hi KL

    Have you tried to use the Administrative Right to run MS Office 2007
    By Right clicking on the MS Office 2007 icon on
    Start menu -->All program (I Guess)
    Right Click on the application you want to run and Select "Run a Command as Administrator" something like that and see if it works

    2008年7月25日 上午 10:30
  • According to your post, you have mentioned the following procedure.

    --> Open Excel 2007

       --> Data

          --> From Other Sources

             --> From Microsoft Query

                --> Choose Data Source

                   --> Query Wizard (choose column) menu


    [1] Would you please describe on the data source that you chose during the step ''Choose Data Source''?

    (or, on how do you create your data source?)


    [2] Can you access your SQL Server using the following procedure in Excel 2007?

    --> Open Excel 2007

       --> Data

          --> From Other Sources

             --> From SQL Server

    2008年7月27日 下午 03:06
  • [1] I can see those tables in the database, but cannot add the tables in the Query Wizard (choose column) meun to "Columns in your query". There was no respond for me to highlight the table and press ">" button in the menu.


    [2] Yes, I can import the data from the table onto the Excel worksheet.


    There may be the Vista security blocking, while Windows XP without this function. 

    2008年7月28日 上午 08:00
  • Did you try to follow what ChiYau asked, try to open the Access 2007 by

    Right Click on the application you want to run and Select "Run a Command as Administrator"

    Microsoft MVP, Microsoft Community Star(TW & HK), MCT, MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA
    2009年1月29日 上午 10:19