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]

Does it stop right away (on the first time you try to run that line), or does it stop at some point later (with some successful iterations? -- i.e., what's the value of row counter and column counter when it errors).

Offhand things look okay, assuming that valid values are used on all your variables.
I would probably prefer:

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

I don't see that you are using batch mode so I don't think UpdateBatch makes sense here. Using an adOpenDynamic cursor type option might also be worth a try (a little less tricky).

 

[espenskeie]

Hello xenu

It stops right away. The rowcounter = 2 (Thats the first row), and the colcounter is 1(thats also the first column)..

I wonder about the path to my files, when I rightclick the files and select properties this is the path I get:

C:\PairTradeFinder

But the servername is localhost/SQL2008E which is located somewhere else(C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008E), the program that uses the databases has defined its own place for storage. I'm not sure if that has any affection to this case?



Kind regards
Espen

 

[espenskeie]

I'm not that familiar with SQL, does this image tell you anything? I justed notice that there are some keys, could it be "locked" for editing from Excel?

 

[xenou]

Okay, here's a try:

Edit this line:

rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic

Change To:

[COLOR="#FF0000"]rs.CursorLocation = adUseClient[/COLOR] 
rs.Open TableName, Cn, [COLOR="#FF0000"]adOpenDynamic[/COLOR], adLockOptimistic

I would also continue to suggest the edit I suggested before, and I think with the client cursor you do want to use updatebatch to send the changes back to the server - I'm not experienced with ado and SQL Server but it makes sense to me.

    For RowCounter = StartRow To EndRow
        rs.AddNew
        For ColCounter = 1 To NoOfFields
            rs(ColCounter - 1) = shtSheetToWork.Cells(RowCounter, ColCounter)
        Next ColCounter
        [COLOR="#FF0000"]rs.Update[/COLOR]
    Next RowCounter
    [COLOR="#FF0000"]rs.UpdateBatch[/COLOR]

Don't worry about the "keys" or the location of the database. The keys are about indexing the database, not locking tables. And your database is a server so it "provides" it's services to you through the operating system. So that's why you give the database server name, not the physical location, in your connection.

 

[espenskeie]

Hi

I still make an error, this one is slightly a bit different than the last error:

Could this have something to do with my values being wrong?

 

[xenou]

How many values are in a row in your sheet? A lot? Can you give sample data?

 

[espenskeie]

I have 205 rows... 19 columns...

 

[xenou]

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

A different connection string might help. Try this instead:

   Dim connection_string As String
   connection_string = "Provider=SQLOLEDB;Data Source=" & ServerName _
       & ";Initial Catalog=" & DatabaseName _
       & ";UserId=" & UserID & ";Password=" & Pwd & ";"
  Cn.Open connection_string

 

[espenskeie]

I think I have to change the variable for Cn.Open, it errors saying that Object variable or Withblock variable not set.

But I'm not sure what to change it to... As for now it is defined:

Dim Cn As ADODB.Connection

Kind regards
Espen