Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Sending Data From Excel to SQL Server

    I'm trying to find out how I can send data from Excel to SQL Server, I have a Stored Procedure I'm looking to run that would insert data into my SQL Table.

    I am used to using DAO from Excel to Access but understand that it would be better to use ADO.

    This is my code for DAO and have tried adapting to ADO without success, how could this be done?

    Code:
    Sub AddPOToMDB()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim r As Long
    
    Sheets("ToSend").Select
    'Set db = DAO.DBEngine.OpenDatabase("D:\Work\eaga\DataWarehouse\JDHeatingOrdersDatabase.mdb")
    Set db = DAO.DBEngine.OpenDatabase("\\birm-astonmulti\Departments\PurchaseOrderDatabase\JDHeatingOrdersDatabase.mdb")
    Set rs = db.OpenRecordset("tblPOInput", dbOpenDynaset)
    r = 2
    Do While Len(Range("A" & r).Formula) > 0
    
        rs.AddNew
        rs!InputUser = Range("A" & r).Value
        rs!OrderID = Range("B" & r).Value
        rs!OrderDate = Range("C" & r).Value
        rs!Supplier = Range("E" & r).Value
        rs!OrderedBy = Range("F" & r).Value
        rs!ApprovedBy = Range("G" & r).Value
        rs!HRNumber = Range("H" & r).Value
        rs!EngineerID = Range("I" & r).Value
        rs!Engineer = Range("J" & r).Value
        rs!Address = Range("K" & r).Value
        rs!Area = Range("L" & r).Value
        rs!PostCode = Range("M" & r).Value
        rs!TotalValue = Range("N" & r).Value
        rs!Item1 = Range("O" & r).Value
        rs!Description1 = Range("P" & r).Value
        rs!Part1 = Range("Q" & r).Value
        rs!Price1 = Range("R" & r).Value
        rs!Qty1 = Range("S" & r).Value
        rs!InputFlag = Range("EK" & r).Value
        rs.Update
    
        r = r + 1
    Loop
    Set db = Nothing
    Set rs = Nothing
    Sheets("Cover").Select
    End Sub
    I have raised a thread in another forum, but nobody has been able to help so far
    http://www.excelforum.com/excel-prog...ql-server.html

    How can I go about this?

  2. #2
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have been googling sending data via ADO from Excel into SQL Server, this below is the code I have written, but I keep getting an error
    [Microsoft][SQL Native Client][SQL Server] Incorrect syntax near the keyword 'FROM'.
    on line "cnn.Execute sQRY, IngRecsAff, adExecuteNoRecords"

    I dont understand what it could be as the code here was taken from a microsoft help website
    Code:
    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & vbCrLf & _
                    "'Excel 8.0;Database= ThisWorkbook', " & vbCrLf & _
                    "[ToSend$])"
    How can I get my records on Sheet(ToSend) to import into the SQL Server table?
    Code:
    Sub SendData()
    Dim cnn As ADODB.Connection
    Dim sQRY As String
    Dim strFilePath As String
    Dim IngRecsAff As Long
    
        Set cnn = New ADODB.Connection
    
        strFilePath = "Driver={SQL Native Client};" & _
                "Server=CISSQL1;" & _
                "Database=CORPINFO;" & _
                "Trusted_Connection=Yes"
        cnn.Open strFilePath
        
        sQRY = _
                    "INSERT HospAtHomeActivityReport (HospAtHome, DateofReferral1, SourceofReferral1, ReasonforReferral1, NewFollowUp1, " & vbCrLf & _
                    "MinutesSpentPatient1, PredictedLengthStay1, NumDayswithHAH1, Outcome1, DateofReferral2, SourceofReferral2, " & vbCrLf & _
                    "ReasonforReferral2, NewFollowUp2, MinutesSpentPatient2, PredictedLengthStay2, NumDayswithHAH2, Outcome2, DateofReferral3, " & vbCrLf & _
                    "SourceofReferral3, ReasonforReferral3, NewFollowUp3, MinutesSpentPatient3, PredictedLengthStay3, NumDayswithHAH3, Outcome3, " & vbCrLf & _
                    "DateofReferral4, SourceofReferral4, ReasonforReferral4, NewFollowUp4, MinutesSpentPatient4, PredictedLengthStay4, NumDayswithHAH4, " & vbCrLf & _
                    "Outcome4, DateofReferral5, SourceofReferral5, NewFollowUp5, ReasonforReferral5, MinutesSpentPatient5, PredictedLengthStay5, " & vbCrLf & _
                    "NumDayswithHAH5, Outcome5, DateofReferral6, SourceofReferral6, ReasonforReferral6, NewFollowUp6, MinutesSpentPatient6, " & vbCrLf & _
                    "PredictedLengthStay6, NumDayswithHAH6, Outcome6, DateofReferral7, SourceofReferral7, ReasonforReferral7, NewFollowUp7, " & vbCrLf & _
                    "MinutesSpentPatient7, PredictedLengthStay7, NumDayswithHAH7, Outcome7, DateofReferral8, SourceofReferral8, ReasonforReferral8, " & vbCrLf & _
                    "NewFollowUp8, MinutesSpentPatient8, PredictedLengthStay8, NumDayswithHAH8, Outcome8, DateofReferral9, SourceofReferral9, " & vbCrLf & _
                    "ReasonforReferral9, NewFollowUp9, MinutesSpentPatient9, PredictedLengthStay9, NumDayswithHAH9, Outcome9, DateofReferral10, " & vbCrLf & _
                    "SourceofReferral10, ReasonforReferral10, NewFollowUp10, MinutesSpentPatient10, PredictedLengthStay10, NumDayswithHAH10, " & vbCrLf & _
                    "Outcome10, InputUser, InputDateTime, InputFlag, ImportDate) " & vbCrLf & _
                    "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & vbCrLf & _
                    "'Excel 8.0;Database= ThisWorkbook', " & vbCrLf & _
                    "[ToSend$])"
        MsgBox sQRY
        Debug.Print sQRY
        cnn.Execute sQRY, IngRecsAff, adExecuteNoRecords
        Debug.Print "Records affected: " & IngRecsAff
        cnn.Close
        Set cnn = Nothing
        Exit Sub
    End Sub

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To use OPENROWSET you must be in the pubs database.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Oh ok, so how would I be best doing this?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a sproc on the server, call the sproc from Excel, passing the necessary parameters.
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    So am I right in thinking then that I should have the INSERT statement and the SELECT statement as the SPROC but the FROM part should bring in the EXCEL part?

    is there any sites I can get an example of this as I'm not 100% sure what you mean?

Posting Permissions

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