-1

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 :

IRTFM
  • 258,963
  • 21
  • 364
  • 487
Toh
  • 99
  • 2
  • 10
  • 1
    Why are r and mysal tagged here? SQL Server would seem a more appropriate tag if I have not misunderstood? – Preston Nov 24 '16 at 15:48
  • Thank you for your feedback ... this is my first post in stackoverflow ...will be more careful in future... My apology. – Toh Nov 25 '16 at 11:42

2 Answers2

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

enter image description here

I would try this first

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

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

enter image description here

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
  • Thank you for your help. It seems that the Provider does not support .index and .seek property and method. – Toh Nov 25 '16 at 11:43
  • Thanks for the Update. I amended my answer. –  Nov 25 '16 at 11:57
  • Try your method .seek array(range("A" & r).valuye, seekEnum... Runtime Error 3251 - Current provider does not support the necessary interface for index functionality. – Toh Nov 25 '16 at 11:59
  • @Toh Do youknow about [ConnectionStrins.Com](https://www.connectionstrings.com/)? They have a complete list of providers. –  Nov 25 '16 at 12:01
  • 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 Nov 25 '16 at 12:12
  • Which is the right provider to use ?. I am Using SurfacePro tablet, Excel 2010, Microsoft Sql Server Management 2016, Visual Studio 2015 – Toh Nov 25 '16 at 12:21
  • @Toh it looks like Ole DB supports it: [Using the Seek Method with OLE DB](https://technet.microsoft.com/en-us/library/ms173300(v=sql.110).aspx). It lloks like you just need to change `SQLNCLI11` to `SQLNCLI`:[SQL Native Client 9.0 OLE DB Provider](https://www.connectionstrings.com/sql-server-native-client-9-0-oledb-provider/) –  Nov 25 '16 at 12:25
  • Provider=SQLNCLI;Server=***;Database=****;Trusted_Connection=yes; – Toh Nov 25 '16 at 13:44
  • Thank you Thomas for helping... really appreciate your time and effort . change the provider ... run-time error 3706 Provider cannot be found, It may not be properly installed. Any other things needed to be done. – Toh Nov 25 '16 at 13:47
  • @Toh There is another `OLEDB `provider listed: [Microsoft OLE DB Provider for SQL Server](https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/). Here is a sample: `"Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=MyDatabase;User ID=abc;Password=abc;" taken from [Accessing SQL Database in Excel-VBA](http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba) –  Nov 25 '16 at 14:01
  • You might want to use `Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase; Integrated Security=SSPI;` for the Trusted Connection. –  Nov 25 '16 at 14:02
  • Read from many sources that Provider for Sql server generally do not support .index and .seek. I am now trying to export the excel data into a temporary Inventory sql table... thereafter. using stored procedure to insert if exists into the final Inventory sql table. hope the work around work... Thank you so much for your time and labour of love helping many. – Toh Nov 27 '16 at 13:35
  • You should post your results and Accept your answer. Take care –  Nov 27 '16 at 14:03
  • My problem is resolved... I export the excel.Inventory to sql.TempInventory... in Sql table 1: FinalInventory, table 2: TemInventory... – Toh Nov 29 '16 at 01:21
  • By sql Merge ... the data in TempInventory is inserted to FinalInventory if not match... and update if match. – Toh Nov 29 '16 at 01:23
  • Hi Thomas Inzina, this is my first post in Stack Overflow... How should I post my result and Accept my answer... please advise. Thankyou. – Toh Nov 29 '16 at 01:25
  • Thanks... I got it... will post the result and accept answer. – Toh Nov 29 '16 at 01:47
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));
Toh
  • 99
  • 2
  • 10