Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Query Issue (2003)

    I have a view on a SQL Server 2000 database that returns a list of companies to be worked on. All of the people in the department have read/write access to the database.

    I have an Excel spreadsheet that uses Microsoft Query to pull the data from this view. The spreadsheet is stored in a folder that is read/write for everyone in the department.

    The spreadsheet has the following macro:
    Private Sub Workbook_Open()

    Sheets("Untouched").QueryTables(1).Refresh

    ThisWorkbook.Save

    End Sub

    Issue
    I always get the current list whenever I open the spreadsheet. However, a few people in the department appear to get 'old' data. When they call to complain, I open the spreadsheet and get the current data. If they try again, after I have opened/closed the spreadsheet, they then get the current data. I know that they are opening the correct spreadsheet because I watched them open it, and saw the incorrect results.

    I've never seen this situation with Data Query and am at a loss as to why the data is not current for everyone.

    Any suggestions?



    T

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Issue (2003)

    Stupid question: do they open the workbook with macros enabled? If macro security is set to High, or if it is set to Medium and they click No in the macro security prompt, the data will not be refreshed.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Issue (2003)

    I'm not sure, I'll have to find out.

    I have my macro security set to Low.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Query Issue (2003)

    In addition to Hans' comments, see the thread starting at <post:=603,335>post 603,335</post:>, particularly Jan Karel's Star Post in that thread.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Query Issue (2003)

    If it is set to low, macros will always be executed, without asking you for permission. For this reason, it is recommended to set it to Medium.
    If you tick the check box "Trust all installed add-ins and templates" in the Trusted Sources tab of Tools | Macro | Security, you won't be nagged to allow macros in Personal.xls etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •