Consulting

Results 1 to 3 of 3

Thread: SQL Server Database Integration with EXCEL

  1. #1
    VBAX Newbie
    Joined
    Oct 2011
    Posts
    2
    Location

    SQL Server Database Integration with EXCEL

    Hi,

    I wanted help with regards to writing a VBA code that would include an SQL query to pull data directly from SQL server on to my spreadsheet. However due to my limited knowledge of VBA I am having difficulty designing the connection string as well as the overall structure.

    SQL Server = SNLDB2
    Windows authenticated settings ( no login required)

    Any help regarding this would be greatly appreciated. Please do let me know if any other piece of info. is required from my side. Thanks

  2. #2
    VBAX Tutor mohanvijay's Avatar
    Joined
    Aug 2010
    Location
    MADURAI
    Posts
    268
    Location
    try this
    you have to reference the "Microsoft ActiveX Data Objects 2.X Library" in tools --> reference

    Dim qry_my as String,conn_str as String
    Dim db_my As ADODB.Connection
    Dim rs_my As ADODB.Recordset
    conn_str="Provider=SQLNCLI;Server=Your Server name;Database=Your databasename;Uid=yourusername;Pwd=yourpassword;"
    qry_my = "Your query here"
    Set db_my = New ADODB.Connection
    Set rs_my = New ADODB.Recordset
    db_my.open conn_str
    rs_my.Open qry_my, db_my, adOpenStatic, adLockReadOnly
    range("a1").CopyFromRecordset rs_my
    rs_my.close
    set rs_my = Nothing
    db_my.close
    set db_my = Nothing
    for more connection strings see here http://www.connectionstrings.com/

  3. #3
    VBAX Newbie
    Joined
    Oct 2011
    Posts
    2
    Location
    Thanks Mohan , I will try this and report back

Posting Permissions

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