INSERT INTO tablename using sql [dynamic table?]

Hello all!

Im trying to insert my record set (using SQL 2005 server [server 2008r2 upgrade coming soon] and office 2013) into a named table "test". (Activeworksheet - for testing purposes.)

The purpose is so I can manipulate the data (add columns- remove columns -write formulas in between the data - then hide columns) and allow the data to remain inside the same named table and upon a refresh have the data enter the same named columns even if the columns are moved from their original position.Basically a table that is dynamic and data always returns to its appropriatenamed column.

This code is cobbled together from the internet ( I new to VBA) and I currently use the connection manager and query designer to manage my workbook. However because my server names and database names change per user group I need a more "mobile" environment. [Tables do remain the same per user group]

My work book has 8 connections total and are all manged via excel's connection manager to SQL server. [workbook is about 10 tabs large]
Just trying to get this to work so I can have all connections managed via VBA.

Sorry for the rambling! Just trying to get this to work.

Option Explicit
Private CN As ADODB.Connection
Function Connect(Server As String, _
                  Database As String) As Boolean
                  
        Set CN = New ADODB.Connection
        On Error Resume Next
                  
         With CN
         ' Create connecting string
                  
            .ConnectionString = "Provider=SQLOLEDB.1;" & _
                        "Integrated Security=SSPI;" & _
                        "Server=" & Server & ";" & _
                        "Database=" & Database & ";"
            ' Open connection
                    .Open
        End With
' Check connection state
        If CN.State = 0 Then
        Connect = False
        Else
        Connect = True
        End If
        
    End Function
    
Function Query(SQL As String)
    Dim RS As ADODB.Recordset
    Dim Field As ADODB.Field
    Dim Col As Long
' Open up a recordset / run query
    Set RS = New ADODB.Recordset
    RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText
If RS.State Then
    Col = 1
    ' Output the column headings in the first row
For Each Field In RS.Fields
    Cells(1, Col) = Field.Name
    Col = Col + 1
    Next Field
    ' Output the results in the rest of the worksheet
    Cells(2, 1).CopyFromRecordset RS
    Set RS = Nothing
    End If
    End Function
    
    Function Disconnect()
        ' Close connection
        CN.Close
    End Function
    
    Public Sub Run()
    Dim SQL As String
    Dim Connected As Boolean
    
    ' Our query
    SQL = "SELECT EIACFT.EI_SN AS 'TAIL #', ENDITEM.STATUS, ENDITEM.EI_BEG_AGE AS 'HOURS'," _
    & " EIACFT.PHASE_DUE AS 'PHASE DUE', EIACFT.PHASE_NO AS 'PMI Sequence #', MIG_LOG.DATE_TIME_STAMP AS 'LAST MIGRATED'" _
    & " FROM dbo.EIACFT EIACFT, dbo.ENDITEM ENDITEM, dbo.MIG_LOG MIG_LOG" _
    & " WHERE EIACFT.EI_ID = ENDITEM.EI_ID AND MIG_LOG.TAG_ID = ENDITEM.EI_ID AND ((ENDITEM.UIC_OWN='" + Range("H3") + "') AND (ENDITEM.DEL_FLAG=0))" _
    & " ORDER BY EIACFT.EI_SN"
    
    
    
    '*************************************************************************************************************
    
    '*********************************************************************************************
  
    ' Connect to the database
    Connected = Connect(Range("H1"), Range("H2"))
    
    If Connected Then
         ' If connected run query and disconnect
         Call Query(SQL)
         Call Disconnect
    Else
        ' Couldn't connect
         MsgBox "Could Not Connect!"
    End If
    End Sub

anyone? Ideas? I have tried the Insert Into "mytablename" however it always states, invalid object "tablename". I have been searching the internet for what I need to no avail. Any help much thanks.

Here is where I have tried to use the insert statement as follows:

However it gives me an error invalid object (I do have a table named "test" in the active worksheet), works without the string and placed data into my active worksheet. Im sure im missing something simple! But its kicking my butt!

I will read up on the update query and try that as well.

Thanks for the response.

Public Sub Run()

    Dim SQL As String
    Dim Connected As Boolean
    
    ' Our query
    SQL = "INSERT INTO test" _
    & " SELECT EIACFT.EI_SN AS 'TAIL #', ENDITEM.STATUS, ENDITEM.EI_BEG_AGE AS 'HOURS'," _
    & " EIACFT.PHASE_DUE AS 'PHASE DUE', EIACFT.PHASE_NO AS 'PMI Sequence #', MIG_LOG.DATE_TIME_STAMP AS 'LAST MIGRATED'" _
    & " FROM dbo.EIACFT EIACFT, dbo.ENDITEM ENDITEM, dbo.MIG_LOG MIG_LOG" _
    & " WHERE EIACFT.EI_ID = ENDITEM.EI_ID AND MIG_LOG.TAG_ID = ENDITEM.EI_ID AND ((ENDITEM.UIC_OWN='" + Range("H3") + "') AND (ENDITEM.DEL_FLAG=0))" _
    & " ORDER BY EIACFT.EI_SN"

Alan,

Ok, I almost have it. Thanks for pointing me to the update query!

This will allow me to pass sheet refrences to my sql query and allow me to place tables into my workbook, I can insert columns and place formulas into the table and upon refresh formulas will remain. However I need one more thing: I need to be able to move my table anywhere in my worksheet and update the table by table name. Not just a static cell refrence, but by table name refrence. I know it has to deal with the target line in my vba but I can not figure out how to set the correct property, or if it is even possible. Any guidance will be much help!

Option Explicit

Function GetTestConnectionString() As String
    
    GetTestConnectionString = OleDbConnectionString("6-101dbsrv", "6-101db", "", "")
    
End Function

Function GetTestQuery() As String
    
    GetTestQuery = "SELECT EIACFT.EI_SN AS 'TAIL #', ENDITEM.STATUS, ENDITEM.EI_BEG_AGE AS 'HOURS'," _
    & " EIACFT.PHASE_DUE AS 'PHASE DUE', EIACFT.PHASE_NO AS 'PMI Sequence #', MIG_LOG.DATE_TIME_STAMP AS 'LAST MIGRATED'" _
    & " FROM dbo.EIACFT EIACFT, dbo.ENDITEM ENDITEM, dbo.MIG_LOG MIG_LOG" _
    & " WHERE EIACFT.EI_ID = ENDITEM.EI_ID AND MIG_LOG.TAG_ID = ENDITEM.EI_ID AND ((ENDITEM.UIC_OWN='" + Range("H3") + "') AND (ENDITEM.DEL_FLAG=0))" _
    & " ORDER BY EIACFT.EI_SN"


End Function

' ===== Import Using QueryTable =====

Sub TestImportUsingQueryTable()

    Dim conString As String
    conString = GetTestConnectionString()

    Dim query As String
    query = GetTestQuery()

    Dim target As Range
    Set target = ThisWorkbook.Sheets(1).Cells(5, 2)

    Select Case ImportSQLtoQueryTable(conString, query, target)
        Case Else
    End Select

End Sub


' ===== Connection String Functions =====

Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
    ByVal Username As String, ByVal Password As String) As String

    If Username = "" Then
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";Integrated Security=SSPI;Persist Security Info=False;"
    Else
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";User ID=" & Username & ";Password=" & Password & ";"
    End If

End Function

' Source: http://support.microsoft.com/kb/816562

Function StringToArray(Str As String) As Variant

    Const StrLen = 127
    Dim NumElems As Integer
    Dim Temp() As String
    Dim i As Integer
    
    NumElems = (Len(Str) / StrLen) + 1
    ReDim Temp(1 To NumElems) As String
    
    For i = 1 To NumElems
       Temp(i) = Mid(Str, ((i - 1) * StrLen) + 1, StrLen)
    Next i
    
    StringToArray = Temp

End Function

Function ImportSQLtoQueryTable(ByVal conString As String, ByVal query As String, _
    ByVal target As Range) As Integer
    
    On Error Resume Next
    
    Dim ws As Worksheet
    Set ws = target.Worksheet
    
    Dim address As String
    address = target.Cells(1, 1).address
               
    
    ' Procedure recreates ListObject or QueryTable
    
    If Not target.ListObject Is Nothing Then     ' Created in Excel 2007 or higher
        target.ListObject.Refresh 'DELETE
    ElseIf Not target.QueryTable Is Nothing Then ' Created in Excel 2003
        'target.QueryTable.ResultRange.Clear
        'target.QueryTable.Refresh
        'Delete
    End If
    
    If Application.Version >= "12.0" Then        ' Excel 2007 and higher
        With ws.ListObjects.Add(SourceType:=0, Source:=Array("OLEDB;" & conString), Destination:=Range(address))
            .Name = "test"
            With .QueryTable
                .CommandType = xlCmdSql
                .CommandText = StringToArray(query)
                .BackgroundQuery = True
                .SavePassword = True
                .Refresh BackgroundQuery:=True
            End With
        End With
    Else                                          ' Excel 2003
        With ws.QueryTables.Add(Connection:=Array("OLEDB;" & conString), _
            Destination:=Range(address))
            
            .CommandType = xlCmdSql
            .CommandText = StringToArray(query)
            .BackgroundQuery = True
            .SavePassword = True
            .Refresh BackgroundQuery:=True
        End With
    End If
    
    ImportSQLtoQueryTable = 0

End Function

Thanks