Doctorjones_md
12/20/2006 10:11:00 PM
Bernie,
There are a plethora of methods to Insert an entire Table, but your For/Next
code does exactly what I needed it to do.
Several other methods (OPENROWSET -- BULK INSERT) weren't working for me --
based on SQL Server Security Settings and/or other issues.
Thanks again for your help! :)
"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:eHaaO0DJHHA.4848@TK2MSFTNGP04.phx.gbl...
> Doctor Jones,
>
> I have to believe that there is a way to move an entire table into a
> database without looping. But I have no experience with SQL, and have
> never used code like yours - still, I'm glad to hear that my (probably
> sub-optimal) code worked out for you.
>
> Bernie
> MS Excel MVP
>
>
> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message
> news:uhXwiI6IHHA.816@TK2MSFTNGP06.phx.gbl...
>> Bernie -- Thanks a Bunch -- that did the trick Brillantly!!
>>
>> I was getting "Wrapped Around the Axle" trying to deal with Stored
>> Procedures and Bulk Inserts (and whatnot) -- what I originally had worked
>> (somewhat), but just needed some tweaking -- thanks again for you help
>> and quick response. :)
>>
>>
>> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
>> news:OJ9VN15IHHA.4848@TK2MSFTNGP04.phx.gbl...
>>> Try changing
>>>
>>> sSQL = "INSERT INTO Upload_Specific " & _
>>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>>> [Features]) " & _
>>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>>> '" &
>>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>>> Range("E2").Value
>>> & "', '" & _
>>> Range("F2").Value & "')"
>>> oConn.Execute sSQL
>>>
>>> to
>>>
>>> For i = 2 To Range("A65536").End(xlUp).Row
>>> sSQL = "INSERT INTO Upload_Specific " & _
>>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>>> [Features]) " & _
>>> " VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
>>> '" & _
>>> Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
>>> Range("E"&i).Value & "', '" & _
>>> Range("F"&i).Value & "')"
>>> oConn.Execute sSQL
>>> Next i
>>>
>>> HTH,
>>> Bernie
>>> MS Excel MVP
>>>
>>>
>>> "Doctorjones_md" <xxxDoctorjones_mdxxx@xxxyahoo.com> wrote in message
>>> news:uQqUzX5IHHA.1816@TK2MSFTNGP06.phx.gbl...
>>>>I reposted this because I was unable to achieve desired results from
>>>>previous recommendations.
>>>>
>>>> Previous Post:
>>>> ===================
>>>> I have the following code which does the following:
>>>>
>>>> 1. Deletes all rows having a value of "0" in column C
>>>> 2. Uploads the data in Row 2 to my SQL Server
>>>>
>>>> What I need for the code to do is to upload all rows on the worksheet.
>>>> My thought is that I might need a For/Next Loop, but I'm not sure where
>>>> in the code to place it. Any ideas on how/where would I modify the
>>>> code to enable it to (loop through) upload all rows, or iterate on each
>>>> row having
>>>> data (those not deleted by the DeleteBlankRows procedure)?
>>>>
>>>>
>>>>
>>>> Here's my code:
>>>> ========================
>>>> Private Sub DeleteBlankRows()
>>>>
>>>> Dim lastrow As Long
>>>> Dim r As Long
>>>> lastrow = Range("C" & Rows.Count).End(xlUp).Row
>>>> For r = lastrow To 2 Step -1
>>>> If Application.CountIf(Cells(r, "C").Resize(1, 1), 0) = 1 Then
>>>> ActiveSheet.Rows(r).Delete
>>>> End If
>>>> Next
>>>>
>>>> End Sub
>>>>
>>>> Sub InsertData()
>>>> Dim oConn As Object
>>>> Dim sSQL As String
>>>> Application.ScreenUpdating = False
>>>> Set wsSheet = ActiveWorkbook.Sheets("Product Tracking")
>>>> Set oConn = CreateObject("ADODB.Connection")
>>>> oConn.Open = "Provider=sqloledb;" & _
>>>> "Data Source=xx.x.xx.xx;" & _
>>>> "Initial Catalog=xxx_xxx;" & _
>>>> "User Id=xxxx;" & _
>>>> "Password=xxxx"
>>>> sSQL = "INSERT INTO Upload_Specific " & _
>>>> "([Location], [Product Type], [Quantity], [Product Name], [Style],
>>>> [Features]) " & _
>>>> " VALUES ('" & Range("A2").Value & "', '" & Range("B2").Value & "',
>>>> '" &
>>>> Range("C2").Value & "', '" & Range("D2").Value & "', '" &
>>>> Range("E2").Value
>>>> & "', '" & _
>>>> Range("F2").Value & "')"
>>>> oConn.Execute sSQL
>>>> oConn.Close
>>>> Set oConn = Nothing
>>>> End Sub
>>>>
>>>> Thanks in advance.
>>>>
>>>>
>>>
>>>
>>
>>
>
>