none
Create a named Connection to SQLServer DB and then add Table using the Connection RRS feed

  • Question

  • My final goal is to be able to create an SQL Connection, *give it an appropriate name* and then use it to create a table to retrieve the data.

    I already use code that is creating the connection *while* creating the table, but that produces a no-name Connection in Excel. And when there are more than one Connections in the workbook it gets tricky controlling which Connection is which.

    That is why I want to create and name the Connection prior to creating the table.

    -----------------------

    I found the following code which works without errors - so far so good - but I cannot figure out :

    * how to name the Connection and make it appear in the Workbook Connections

    * what to do next to create the table using the Named Connection object as source

    Dim oCnn As Object
    Dim oCmd As Object
    Dim ConStr As String

    Dim vsQueryStatement as String

    vsQueryStatement ="SELECT * FROM MyTable"

    ConStr = "Provider=SQLOLEDB.11;User ID=sa;Password=saPassword;Persist Security Info=True;Initial Catalog=MyCatalog;Data Source=10.10.10.10;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MyPC;Use Encryption for Data=False;Tag with column collation when possible=False"

    Set oCnn = CreateObject("ADODB.Connection")
    Set oCmd = CreateObject("ADODB.Command")
    oCnn.Open ConStr

    oCmd.ActiveConnection = oCnn
    oCmd.CommandText = vsQueryStatement

    Call oCmd.Execute


    George Thalassinos

    Tuesday, May 9, 2017 6:44 AM

Answers

  • Hi George Thalassinos,

    if you are creating multiple connection then you can just try to set the different object name for every connection object.

    then whenever you want to execute query to particular database then you just need to use that connection object.

    by this way you can maintain multiple connections easily.

    further you had mentioned that,"what to do next to create the table using the Named Connection object as source"

    I have a little confusion here. you want to create table in Excel or in database.

    if you want to feel the data from database to Excel  then you can try to refer code below.

    Sub Demo()
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL, strInput As String
    
    strFile = "S:\Location.Database.accdb"
    
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strCon
    
    strInput = InputBox("Input Desired Name")
    
    strSQL = "SELECT NAME, Location WHERE NAME =""'strInput'"";"
    
    'Added the following four lines
    Set rs = CreateObject("ADODB.RECORDSET")    
    rs.activeconnection = cn    
    rs.open strSQL    
    Sheet1.Range("A1").CopyFromRecordSet rs
    
    'removed
    'cn.Execute strSQL
    
    
    rs.close
    cn.Close
    Set cn = Nothing
    
    End Sub

    if I misunderstand something in your above description then please correct me. I will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 10, 2017 5:38 AM
    Moderator

All replies

  • Hi George Thalassinos,

    if you are creating multiple connection then you can just try to set the different object name for every connection object.

    then whenever you want to execute query to particular database then you just need to use that connection object.

    by this way you can maintain multiple connections easily.

    further you had mentioned that,"what to do next to create the table using the Named Connection object as source"

    I have a little confusion here. you want to create table in Excel or in database.

    if you want to feel the data from database to Excel  then you can try to refer code below.

    Sub Demo()
    Dim cn As Object
    Dim rs As Object
    Dim strFile As String
    Dim strCon As String
    Dim strSQL, strInput As String
    
    strFile = "S:\Location.Database.accdb"
    
    strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile
    
    Set cn = CreateObject("ADODB.Connection")
    cn.Open strCon
    
    strInput = InputBox("Input Desired Name")
    
    strSQL = "SELECT NAME, Location WHERE NAME =""'strInput'"";"
    
    'Added the following four lines
    Set rs = CreateObject("ADODB.RECORDSET")    
    rs.activeconnection = cn    
    rs.open strSQL    
    Sheet1.Range("A1").CopyFromRecordSet rs
    
    'removed
    'cn.Execute strSQL
    
    
    rs.close
    cn.Close
    Set cn = Nothing
    
    End Sub

    if I misunderstand something in your above description then please correct me. I will try to provide you further suggestions.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 10, 2017 5:38 AM
    Moderator
  • Thank you Deepak for your reply.

    You are right, my goal is to create a table for this Connection in Excel and bypass the default name given to the connection when it is automatically created if you start with creating the table.

    I will try your suggestion and let you know how it goes or just mark your reply as Answer if everything goes well!

    I will try this tomorrow at the office.


    George Thalassinos

    Wednesday, May 10, 2017 3:11 PM
  • Actually I cannot see where I create the table from the above connection, say in cell A3. The headers of the table would be in row 3 and the data below that. Please provide the next step. I need the next command that relates the table to the connection and creates the table. After that I just have to refresh the table.

    George Thalassinos

    Wednesday, May 10, 2017 3:15 PM
  • The code that I use so far, and leaves the connection named as "Connection", is

    *********

    With ActiveSheet.ListObjects.Add( _
                                         SourceType:=0, _
                                         Source:=Array("OLEDB;Provider=SQLOLEDB.11;Password=MyPassword;Persist Security Info=True;User ID=sa;Initial Catalog=MyCatalog;Data Source=10.10.10.10", _
                                                       ";Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MyPC;Use Encryption for Data=False;Tag with column collation when possible=False"), _
                                         Destination:=Range("$A$3")).QueryTable
            .CommandText = vsQueryStatement
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = False
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "QueryTable1"
       End With

    *************

    I would like to name the Connection as "QueryConnection1" in order to see easily that it is related to "QueryTable1".

    Thanks in advance.


    George Thalassinos

    Wednesday, May 10, 2017 3:22 PM
  • Hi George Thalassinos,

    I can see that you are using ListObjects.Add Method.

    in which there is no any parameter for naming the connection.

    so you cannot give any name to connection.

    Reference:

    ListObjects.Add Method (Excel)

    you don't need to create table here.

    this method have parameter called "Destination".

    you just need to specify your whole range.

    then after whenever you run this code the data will be overwrite.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 11, 2017 9:21 AM
    Moderator
  • I just marked your reply as Answer. I will tweak it a little bit in order to suit my exact needs, still you have provided a method to do what I need in the first place.

    Good job! Thanks!


    George Thalassinos

    Friday, May 12, 2017 3:44 PM