+ Reply to Thread
Results 1 to 5 of 5

INSERT INTO tablename using sql [dynamic table?]

Hybrid View

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    nope
    MS-Off Ver
    Excel 2007
    Posts
    8

    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

  2. #2
    Registered User
    Join Date
    09-04-2012
    Location
    nope
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: INSERT INTO tablename using sql [dynamic table?]

    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.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2506 Win 11
    Posts
    24,903

    Re: INSERT INTO tablename using sql [dynamic table?]

    Have you defined your "mytablename" as any particular type of object in your code? maybe a recordset? I'm grasping at straws here.


    I don't see an Insert statement in your VBA. I only see a Select Statement.

    Here is a link to the syntax for an Insert Statement.

    http://www.w3schools.com/sql/sql_insert.asp

    Have you considered using an Update Query?

    http://www.w3schools.com/sql/sql_update.asp



    I have no experience with linking MS Excel with a SQL database. Most of my experience in this area has been with Access.
    Last edited by alansidman; 10-17-2013 at 10:26 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    09-04-2012
    Location
    nope
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: INSERT INTO tablename using sql [dynamic table?]

    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"

  5. #5
    Registered User
    Join Date
    09-04-2012
    Location
    nope
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: INSERT INTO tablename using sql [dynamic table?]

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error -2147217900: [Microsoft][ODBC Excel Driver] <tablename$> is NOT a valid name
    By basubdd in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-06-2013, 11:34 AM
  2. [SOLVED] Dynamic Table Range, Dynamic Columns
    By bimo in forum Excel General
    Replies: 6
    Last Post: 06-24-2013, 08:16 AM
  3. Dynamic table formula for sorting data to another table dynamic
    By 650dozer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 07:22 PM
  4. [SOLVED] Excel 2007 - SQL srv connection failed with spaces in tablename
    By MarMo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2012, 08:55 AM
  5. Insert / Delete Rows depending upon Dynamic Built Table
    By clemsoncooz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2012, 11:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1