Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2011
    Posts
    10

    Unanswered: excel accessing sql data

    hello all,

    looking for advice for the best way to view information from an sql database.
    i need the info to be refreshed everytime its open. the excel sheet will be stored on a sharepoint so that anytime anyone opens it the table refreshes with the data.

    its the best way to do this... VBA SQL code?..

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can open Sharepoint lists directly in Excel. Any reason not to do that?

    There are ways to do it "by hand". You don't want to try them in VBA unless you have to.


    What does this have to do with SQL btw? Is there another part of this solution not yet described?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2011
    Posts
    10
    thanks for the reply. ill try give more detailed information as what i want to do.

    Data is entered in a website and stored in a MS SQL Server.
    I want to access this data (i have the connection string and passwords) and show in an excel sheet so that anyone on my network can view it and add comments to the data in the excel file.

    when the excel file is opened, i want the data to refresh so the latest data can be seen

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, I get it.

    You should be able to setup an "import" over OLEDB or however you want to connect. There's nothing special about it being in Sharepoint other than it might complain about rights to modify the excel sheet depending on how you setup permissions.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Feb 2011
    Posts
    10
    the reason i mentioned sharepoint is that this is where i will be storing it on the network so everyone has access to it.

    just in regards to "importing" over OLEDB....

    will this allow anyone to open the excel file in the sharepoint location, and refresh the data? does oledb not tie it to your own pc?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Nope, OLEDB is fine.

    DSN-based ODBC would tie it to a specific machine due to requiring a DSN entry.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2011
    Posts
    10
    ok thanks but when i try to import using 'from other sources.. from data connection wizard (import data for an unlisted format by using the data connection wizard and OLEDB).. then click Microsoft SQL Server.. then i enter the server name (and username and password).. then select the database and table.. then hit finish.....

    the connection file is stored on my C drive??

    if i want other people to be able to open this and refresh the data, it wont work because it will reference my C drive?

    am i doing something wrong?

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I see now. 2007 puts your balls in a vice and tries to force you to never embed any data connection information.

    Here's a way around it: Create the connection file and save it like you normally would. Then, click "connections", highlight the connection you created and click "properties.

    Switch over to the "definition" tab. Save the connection string in notepad or something, then type some random text into the connection string box and click "ok".

    It will yell at you that the connection string is no longer identical to the file and will be stored in the spreadsheet. Obviously this is acceptable. This will clear the connection file association and you are then free to paste in the correct connection string again.


    what a POS.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2011
    Posts
    10
    mmmmmm.. thats annoyin alright.

    could you help me code using VBA where i could store the connection string, username, password and SQL code and put the data in A1?

  10. #10
    Join Date
    Feb 2011
    Posts
    10
    here is what i have done.. but its not working.. (keep in mind im only starting VBA!!)


    Sub DataExtract()


    Dim cnData As ADODB.Connection
    Set cnData = New ADODB.Connection
    Dim strConn As String

    'Use the SQL Server OLE DB Provider.
    strConn = "OLEDB;Provider=SQLOLEDB.1;" & _
    "Data Source=***.com\SQL01,1433;" & _
    "Password=****;Persist Security Info=True;" & _
    "User ID=****;"


    cnData.Open strConn

    Dim rsData As ADODB.Recordset
    Set rsData = New ADODB.Recordset

    With rsData
    ' Assign the Connection object.
    .ActiveConnection = cnData
    ' Extract the required records.
    .Open "SELECT * FROM *

    ' Copy the records into cell A1 on Sheet1.
    Sheet1.Range("A1").CopyFromRecordset rsData

    ' Tidy up
    .Close
    End With

    cnData.Close
    Set rsData = Nothing
    Set cnData = Nothing

Posting Permissions

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