Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access man needs help (2002 SP2)

    Hi Excelers,

    I have been asked to fix an Excel spreadsheet / template that produces a SQL Server login error shortly after opening:

    ---------------------------
    Microsoft SQL Server Login
    ---------------------------
    Connection failed:
    SQLState: '01S00'
    SQL Server Error: 0
    [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
    Connection failed:
    SQLState: '01000'
    SQL Server Error: 53
    [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 6
    [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found.

    The company has recently upgraded to a new SQL server and I have spent the last couple of days re-pointing all their Access reports to the tables in this new server. I suspect that the problem with the Excel spreadsheet is that when it starts it is trying to import/link to data in either the old SQL server or to an obscure Access database that itslef links to the old server.

    When I open a new workbook from the template on my machine I get an option to enable or disable query refresh (the user dosn't get this on her machine)

    The trouble is I am a bit of a novice with Excel and I cannot work out (a) where to set what the spreadsheet does when it first opens and ([img]/forums/images/smilies/cool.gif[/img] where to find the data sources that it is using. I've looked at the code and in Data > Get External Date but can't find anything obvious. Date > Get External Date only seems to offer options to set up new data sources, not to look at existing ones.

    Thanks in advance'

    Ian

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

    Re: Access man needs help (2002 SP2)

    See if the Query Manager add-in by Jan Karel Pieterse helps. You can download it from <post#=352583>post 352583</post#>.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access man needs help (2002 SP2)

    I can't find the query manager on this web site.

    Also, in my problem worksheet, there are no queries listed from Data > Get Exeternal Data. In fact the Edit option is disabled.

    Ian

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access man needs help (2002 SP2)

    I have now found the worksheet with the query in it. It was in a hidden sheet and I've been shown how to unhide sheets from the properties window in the VBA interface. What I need to do now is to change the connection string that the query uses. Can anybody suggest a meyhod?

    Ian

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

    Re: Access man needs help (2002 SP2)

    The Query Manager is attached to the post I referred to.

  6. #6
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access man needs help (2002 SP2)

    Thanks for that. I followed the Web link, didn't spot the attachment.

    Now that I have installed it, do you know how to use it? I can't see any new toolbar or menu items in either Excel or MS Query.

    Ian

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Access man needs help (2002 SP2)

    Access it under:
    Tools - Query manager

    after you have installed it as an addin (Tools - Addins - browse)

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access man needs help (2002 SP2)

    Thanks. Works like a dream.

Posting Permissions

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