Multiple-step OLE DB operation ERROR

[espenskeie]

Hi

I try to add data to an SQL DB directly from Excel, but I get an run-time error saying that Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Could it be that the SQL is blocked for editing from the "outside"?

Here's my code:

Sub SQLIM()


          ' Send data to SQL Server
     ' This code loads data from an Excel  Worksheet to an SQL Server Table
     ' Data should start in column A and should be in the same order as the server table
     ' Autonumber fields should NOT be included'
     ' FOR THIS CODE TO WORK
     ' In  VBE you need to go Tools  References and check Microsoft Active X Data  Objects 2.x library




    Dim Cn As ADODB.Connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.Recordset
    Dim RowCounter As Long
    Dim ColCounter As Integer
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("Sectors")
    Set rs = New ADODB.Recordset




    ServerName = "localhost\SQL2008E" ' Enter your server name here
    DatabaseName = "pairTradeFinder" ' Enter your  database name here
    TableName = "portfolios" ' Enter your Table name here
    UserID = "" ' Enter your user ID here
     ' (Leave ID and Password blank if using windows Authentification")
    Password = "" ' Enter your password here
    NoOfFields = 19 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading  records
    EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet


     '  CHANGES
   ' Dim shtSheetToWork As Worksheet
   ' Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1")
     '********


    Set Cn = New ADODB.Connection
    Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
    ";Uid=" & UserID & ";Pwd=" & Password & ";"


    rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic


    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 1 To NoOfFields
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
        Next ColCounter
    Next RowCounter
    rs.UpdateBatch


     ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing


End Sub

This is where the VBA stops:

rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)

Kind regards
Espen

 

[xenou]

Oops. LIke this:

   Dim connection_string As String
   connection_string = "Provider=SQLOLEDB;Data Source=" & ServerName _
       & ";Initial Catalog=" & DatabaseName _
       & ";UserId=" & UserID & ";Password=" & Pwd & ";"
  [COLOR="#FF0000"]Set Cn = New ADODB.Connection[/COLOR]
  Cn.Open connection_string

Remove the other Set Cn = New ADODB.Connection if it is still somewhere else in your code.

 

[espenskeie]

Hmm, now it errors on

Cn.Open connection_string

And says "Invalid connection string attribut"

 

[xenou]

Rat. I have to head out. I was following the suggestion from here. There are other links to try. I think the connection string I gave is pretty standard. You could try fiddling with it a little more to get it to work.

http://databases.aspfaq.com/database/what-is-this-multiple-step-ole-db-error.html

 

[xenou]

Ah,

UserID should be User ID

 

[xenou]

So like this:

   Dim connection_string As String
   connection_string = "Provider=SQLOLEDB;Data Source=" & ServerName _
       & ";Initial Catalog=" & DatabaseName _
       & ";[COLOR="#FF0000"]User Id[/COLOR]=" & UserID & ";Password=" & Pwd & ";"
  Set Cn = New ADODB.Connection
  Cn.Open connection_string

Microsoft OLE DB Provider for SQL Server

 

[espenskeie]

Thanks, it still errors, this time it says Invalid Authoriztion specification...

I'll google around and see if I can break the problem down

Thanks for you help!

Have a nice day

 

[espenskeie]

Finally!!!

I did a lot of editing back and forth, so I cannot quite remember exactly what made the differance. But for instance I changed the User ID to UID, I see both versions used, I ended up with UID, but I think I could use both of them..

The main issue was that my code started to bug when it came to

rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)

I did think about this solution earlier, but I somehow forgot to check it out in the heat. My first column is somehow predefined in SQL, the unique ID for each input is not coming from Excel.

So when I removed that column and reduced the number of columns from 19 to 18, it went straight through in less than a second, I actually had to double check if anything happend at all!!

Here is my code, and it is working for me using Excel 2010, and SQL 2008 Express:

Sub SQLIM()


          ' Send data to SQL Server
     ' This code loads data from an Excel  Worksheet to an SQL Server Table
     ' Data should start in column A and should be in the same order as the server table
     ' Autonumber fields should NOT be included'
     ' FOR THIS CODE TO WORK
     ' In  VBE you need to go Tools  References and check Microsoft Active X Data  Objects 2.x library




    Dim Cn As ADODB.Connection
    Dim ServerName As String
    Dim DatabaseName As String
    Dim TableName As String
    Dim UserID As String
    Dim Password As String
    Dim rs As ADODB.Recordset
    Dim RowCounter As Long
    Dim ColCounter As Integer
    Dim NoOfFields As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim shtSheetToWork As Worksheet
    Set shtSheetToWork = ActiveWorkbook.Worksheets("Sectors")
    Set rs = New ADODB.Recordset




    ServerName = "ESPENS-NYE-PC\SQL2008E" ' Enter your server name here
    DatabaseName = "pairTradeFinder" ' Enter your  database name here
    TableName = "portfolios" ' Enter your Table name here
    UserID = "" ' Enter your user ID here
     ' (Leave ID and Password blank if using windows Authentification")
    Password = "" ' Enter your password here
    NoOfFields = 18 ' Enter number of fields to update (eg. columns in your worksheet)
    StartRow = 2 ' Enter row in sheet to start reading  records
    EndRow = shtSheetToWork.Cells(Rows.Count, 1).End(xlUp).Row ' Enter row of last record in sheet


     '  CHANGES
   ' Dim shtSheetToWork As Worksheet
   ' Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1")
     '********


    Dim connection_string As String
    connection_string = "Provider=SQLOLEDB;Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";Trusted_connection=yes;" 'UID='" & UserID & ";Password='" & Password & ";"
           
    Set Cn = New ADODB.Connection
    Cn.Open connection_string


    rs.CursorLocation = adUseClient
    rs.Open TableName, Cn, adOpenDynamic, adLockOptimistic
    
    'Read from SQL and import to Excel
    'With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
     '   .ClearContents
      '  .CopyFromRecordset rs
    'End With


    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 2 To NoOfFields
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
        Next ColCounter
        rs.Update
    Next RowCounter
    rs.UpdateBatch


     ' Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing



End Sub

Thank you xenou for helping me out on this one! No I'm ready to run into another huge moutain of problems and challenges!

Kind regards
Espen

 

[xenou]

Excellent. Glad it's working.