We help IT Professionals succeed at work.

How to insert Data into SQL Server

The code below works very well against Ms Access VBA but the same code does not insert anything against Ms SQL Server, any assistance will be highly appreciated. It seams like SQL does not like the command UPDATE I still think it wants a reference or Primary ID number while access accept without problems. The primary ID number cannot be there until the record is created, so in place of UPDATE  is there something that need to be done to insert the processed data into the SQL table????

Dim JSONS As Object

    lngStatus = CommRead(intPortID, strData, 14400)

Set rs = db.OpenRecordset("tblEfdReceipts")
    If lngStatus > 0 Then
    Set JSONS = JsonConverter.ParseJson(strData)
    Z = 2
    ElseIf lngStatus < 0 Then
    MsgBox "Please note that there is no data to read", vbOKOnly, "The Comm Port has no data"
        ' Handle error.
        On Error Resume Next
    End If
        ' Process data.
  For Each Item In JSONS
           With rs
            rs![TPIN] = Item("TPIN")
            rs![TaxpayerName] = Item("TaxpayerName")
            rs![Address] = Item("Address")
            rs![ESDTime] = Item("ESDTime")
            rs![TerminalID] = Item("TerminalID")
            rs![InvoiceCode] = Item("InvoiceCode")
            rs![InvoiceNumber] = Item("InvoiceNumber")
            rs![FiscalCode] = Item("FiscalCode")
            rs![TalkTime] = Item("TalkTime")
            rs![Operator] = Item("Operator")
            rs![Taxlabel] = Item("TaxItems")("TaxLabel")
            rs![CategoryName] = Item("TaxItems")("CategoryName")
            rs![Rate] = Item("TaxItems")("Rate")
            rs![TaxAmount] = Item("TaxItems")("TaxAmount")
            rs![TotalAmount] = Item("TaxItems")("TotalAmount")
            rs![VerificationUrl] = Item("TaxItems")("VerificationUrl")
            rs![INVID] = Me.InvoiceID
        End With
        Z = Z + 1
      Set rs = Nothing
      Set db = Nothing
      Set JSONS = Nothing

Open in new window


Watch Question

Software & Systems Engineer
Distinguished Expert 2019
If tblEfdReceipts is a linked table it would work just fine..as for the PK just create an AutoIncrement Field on the SQL table...
Check if you have datatypes mismatch
Top Expert 2014
I don't see you using "UPDATE" in the code you posted.
Top Expert 2014

The method you are using is analogous to an INSERT operation
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Do you get an error? If so, please post the entire error, or a screenshot of the error.

There must be one or more fields identified as unique for access to update a linked table. Usually this is the primary key of the table. Open your linked table in design view and verify that there is a unique key. If there isn't, try to create the link again, and you should be prompted to identity the field or fields that are unique. 

But as others have mentioned, if you include the error message with your post, it would be easier to troubleshoot :)

Its done now , thank you so much for pointing me to the right direction.