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
    Beep
    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
         
            .AddNew
            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
            rs.Update
        End With
        Z = Z + 1
    Next
      
      rs.Close
      Set rs = Nothing
      Set db = Nothing
      Set JSONS = Nothing

Open in new window



Regards

Chris
Comment
Watch Question

Software & Systems Engineer
Distinguished Expert 2019
Commented:
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
Commented:
I don't see you using "UPDATE" in the code you posted.
Top Expert 2014

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

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.

Regards

Chris