migrating excel information into a sql server table

[lilal111]

I was asked if it is possible to use Microsoft Excel to input information into a table in SQL Server 2008. We have a Project Manager that is trying to use Excel as an interface to populate Assigned Hours and Estimated Hours.

Any help would be so appreciated!!Thanks!!

 

[SkipVought]

hi,

You can use ActiveX Data Objects. I have never written SQL Server queries, but here's an example of an UPDATE query to an Oracle table, a function that adds a row to the table...

Function AddComment( _
    TRAV As String, _
    COMMENT As String, _
    Optional COST_CENTER As String = "", _
    Optional MAKE_PROMISE_DATE As Date = 0)
'======================================================================
'SkipVought
'2011 Mar 24
'UPDATE to FPRPTSAR.MFG_ORDER_COMMENTS
'
'AddComment ADDs the comment for that MFG_ORD, USER_ID of login
'----------------------------------------------------------------------
'REV 1
'2011 Mar 25
'SkipVought
'AddComment first checks to see if the COMMENT is NOT the LATEST COMMENT
'  If so, then it ADDs the comment for that MFG_ORD/USER_ID
'======================================================================
    Dim sConn As String, sSQL As String, sServer As String, UID As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection, cmd As ADODB.Command
    
    Set cnn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rst = New ADODB.Recordset
    
    sServer = "A010PROD"
    cnn.Open "Driver={Microsoft ODBC for Oracle};" & _
               "Server=" & sServer & ";" & _
               "Uid=/;" & _
               "Pwd="
    
    UID = Environ("username")
    
    sSQL = "SELECT CREATE_DATE, COMMENTS"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FPRPTSAR.MFG_ORDER_COMMENTS"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE USER_ID=?"
    sSQL = sSQL & "  AND MFG_ORD=?"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "ORDER BY 1 DESC"
    
    On Error Resume Next
    
    With cmd
         .CommandText = sSQL
         .CommandType = adCmdText
         .Prepared = True
         .Parameters.Append .CreateParameter( _
             "USER_ID", _
             adChar, _
             adParamInput, _
             , _
             UID)
         .Parameters.Append .CreateParameter( _
             "MFG_ORD", _
             adChar, _
             adParamInput, _
             , _
             TRAV)
         
         .ActiveConnection = cnn
         
        Set rst = .Execute
    End With
    
    rst.MoveFirst
    If rst("COMMENTS") <> COMMENT Then
        rst.Close
'[b]this part does the update[/b]        
        sSQL = "FPRPTSAR.MFG_ORDER_COMMENTS"
        
        rst.Open sSQL, cnn, adOpenDynamic, adLockPessimistic, adCmdTable
'MFG_ORD, CREATE_DATE, MAKE_PROMISE_DATE, USER_ID, ORGANIZATION, COMMENTS, COST_CENTER
                              
        rst.AddNew
        
        rst("MFG_ORD") = Trim(TRAV)
        rst("CREATE_DATE") = Now
        rst("USER_ID") = UID
        rst("ORGANIZATION") = "DSC_BP"
        rst("COMMENTS") = COMMENT
        If COST_CENTER <> "" Then _
            rst("COST_CENTER") = Trim(COST_CENTER)
        If MAKE_PROMISE_DATE > Date Then _
            rst("MAKE_PROMISE_DATE") = MAKE_PROMISE_DATE
                      
        rst.Update
        
        If Err.Number <> 0 Then
            AddComment = False
        Else
            AddComment = True
        End If
    Else
        AddComment = False
    End If
    
    cmd.Cancel
    rst.Close
    cnn.Close
    
    Set cmd = Nothing
    Set rst = Nothing
    Set cnn = Nothing
    
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]