Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: SQL Server Query and Office

    Is there a way to create a Secure Connection between Excel 2000 and SQL Server 2005 so that Excel can run a stored SQL procedure?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A lot depends on your definition of secure. You can create an ADO connection using VBA, which can use Windows Authentication. What kind of security do you want to acheive?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    Whats going on is my query will not run in excel. I am assuming it is due to the fact thay when i try to run the same query in access it asks for uname and password and i have to click okay beforw the reaults will display. Maybe i should have asked how to pass a sql username and password via vba?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The easy answer is to use Windows Authentication instead of passing a username and a password. The hard answer is to embed the username and the password in the ADO connection string within the VBA code.

    Your actual problem is that your query requires parameters. When the query runs from VBA, there isn't a logical "screen" or "keyboard" like there is when a parameterized query is run from within MS-Access. For help with this part of your problem, see ACC2000: How to Use Parameters with ActiveX Data Objects (ADO) and Jet

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Thank you for the helpful link. I actually just hardcoded it into my VBA Procedure, looked something to this:
    Code:
    Dim DataInfo AS String
    
    DataInfo = DataInfo & "ODBC;"
    DataInfo = DataInfo & "DSN=DSN Name"
    DataInfo = DataInfo & "UID= Enter the User ID"
    DataInfo = DataInfo & "APP=Enter your Excel Version"
    DataInfo = DataInfo & "Database= Enter your Database Name"
    And that got me connected and pulling in the data.

Posting Permissions

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