EXCEL VBA to SQL Index and Seek

Question

I am exporting data in Excel table to SQL Server Database, If exists UPDATE else INSERT.

The following VBA code works well for exporting to ACCESS Database, BUT NOT TO SQL SERVER DATABASE TABLE.

Error Message appear :Invalid Use of Property for .Index and .Seek.

Please Help !!! Toh

Sub ExcelDataToSql ()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim lastrow As Long, o As Long

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open "Provider=SQLNCLI11;Server=***;Database=****;Trusted_Connection=yes;"
rs.CursorLocation = adUseServer
rs.Open "InventorySQL", cn, 1, 3, adCmdTableDirect

' Get Lastrow
Worksheets("InventoryEXCEL").Select
lastrow = Worksheets("InventoryEXCEL").Cells(rows.Count, 1).End(xlUp).Row
r = 2 ' the start row in the worksheet
For o = 2 To lastrow

    'Check For Duplicate In Database SQL
    With rs
        .Index = "PrimaryKey"
        .Seek Range("A" & r).Value

        If .EOF Then
            .AddNew            
            'If No Duplicate insert New Record
            rs.Fields("oPartno") = Range("A" & r).Value
            rs.Fields("oDesc") = Range("B" & r).Value
            rs.Fields("oCost") = Range("C" & r).Value
        .update
        Else          
            ' If Duplicate Found Update Existing Record
            rs.Fields("oDesc") = Range("B" & r).Value
            rs.Fields("oCost") = Range("C & r).Value
            .Update
        End If
    End With

Next o
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    MsgBox "Posting Completed"
End Sub

. Index = "PrimaryKey" --- Sysntax Error : Invalid Use of Property .Seek Range ("A" & r).Value Sysntax Error :

Why are r and mysal tagged here? SQL Server would seem a more appropriate tag if I have not misunderstood? — Preston
– Preston, Commented Nov 24, 2016 at 15:48
Thank you for your feedback ... this is my first post in stackoverflow ...will be more careful in future... My apology. — Toh
– Toh, Commented Nov 25, 2016 at 11:42

score 1 · Accepted Answer · 2016-11-25 11:56:31Z

1

Reference:Seek Method and Index Property Example (VB)

The MSDN example passes an Array as the first parameter.

rstEmployees.Seek Array(strID), adSeekFirstEQ

The first parameter's name os KeyValues which also implies an array

I would try this first

.Seek Array(Range("A" & r).Value)

It might also be beneficial to use one of the SeekEnum value

Update: TOH the OP found that this was the relavent code snippet

MSDN also suggest checking if the Provider supports .Index and .Seek

If rstEmployees.Supports(adIndex) And rstEmployees.Supports(adSeek) Then

edited Nov 25, 2016 at 11:56

answered Nov 24, 2016 at 16:39

user6432984

Sign up to request clarification or add additional context in comments.

17 Comments

Toh Over a year ago

Thank you for your help. It seems that the Provider does not support .index and .seek property and method.

user6432984 Over a year ago

Thanks for the Update. I amended my answer.

Toh Over a year ago

Try your method .seek array(range("A" & r).valuye, seekEnum... Runtime Error 3251 - Current provider does not support the necessary interface for index functionality.

user6432984 Over a year ago

@Toh Do youknow about ConnectionStrins.Com? They have a complete list of providers.

Toh Over a year ago

Yes... I use the provider from ConnectionStrins.Com. How to know which provider provide the functionality for the .index and .seek. The connection currently used with no connection problem.

|

Toh · Accepted Answer · 2016-11-30 11:01:26Z

1

My Problem is resolved by work around.

Many resources indicated that Sql Providers do not support the index and seek function. So I avoid Index and Seek

I work around by importing the excel worksheet into Sql server as source table... thereafter Merge the target table with source table... if match UPDATE, if Not Match INSERT.

select * from InventoryTableSQL
select * from InventoryTableFromExcel

Merge InventoryTableSQL as T
using InventoryTableFromExcel as S
on t.oPartno = s.oPartno
when matched then
    update set t.oPartno = s.oPartno,
    t.oDesc = s.oDesc,
    t.oCost = s.oCost
when not matched by target then
    insert(oPartno, oDesc, oCost) values(s.oPartno, s.oDesc, s.oCost));

answered Nov 30, 2016 at 11:01

Toh

991 gold badge2 silver badges11 bronze badges

Collectives™ on Stack Overflow

EXCEL VBA to SQL Index and Seek

2 Answers 2

17 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related