Sending Data From Excel to SQL Server

JezLisle · #1 (**permalink**) 10-29-08, 11:20

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?

JezLisle · #2 (**permalink**) 10-31-08, 04:41

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

gvee · #3 (**permalink**) 10-31-08, 05:02

To use OPENROWSET you must be in the pubs database.

JezLisle · #4 (**permalink**) 10-31-08, 05:13

Oh ok, so how would I be best doing this?

gvee · #5 (**permalink**) 10-31-08, 05:24

Create a sproc on the server, call the sproc from Excel, passing the necessary parameters.

JezLisle · #6 (**permalink**) 10-31-08, 05:33

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech