INSERT INTO tablename using sql [dynamic table?]

[swill008]

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!

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

 

[swill008]

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.