Opening Record set with Access & Sql server linked tables

[nector]

I have got a Suprise this morning concerning linked tables from Ms Access and SQL, if I use the standard open record set as below, I'm able to insert the data in the specified access table below. But if I transfer the same table to SQL server, the VBA code fail to insert the data, I'm currently using the new 2022 Version Sql server vs Access 2016.

I know I can work it around by creating a temporal table in access just to receive these internet data and then transfer the data to SQL server by the standard insert query, but that route needs more coding to avoiding blotting the Front End.

Any fix here is welcome!

'Processing data from the string above
    Set db = CurrentDb
Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset)
    Set json = JsonConverter.ParseJson(strDataAudit)
   
    'Process data.
    Z = 1
    For Each Details In json
        rs.AddNew
        rs![ESDTime] = CDate(Format$(Details("ESDTime"), "00/00/00 00:00:00"))
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs![INVID] = CInt(Me.txtEsDFinInvoice)
        rs.Update
        Z = Z + 1
    Next
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

 

[isladogs]

For recordsets based on SQL Server tables you should use dbOpenDynaset+dbSeeChanges

 

[nector]

I do totally agree with you on this one, I have noticed also that some of the code that work perfectly with Ms Access are no longer working, and so I had to rewrite them:

Example an update query based on formulas or calculated controls, the SQL Server cannot accept, and so in this case I had to make sure that the calculations are done via the unbound form with VBA and then send them to an update query as normal values. This way I was able to make the SQL server work!

The same issues emanate if you do not migrate the Ms Access tables with SSMA + Access runtime 2013 conversion issues again pops up. This alone is a serious issue.

Set rs = db.OpenRecordset("tblEfdReceipts", dbOpenDynaset,dbSeeChanges)

Many thanks to you MVP/VIP

 

[Pat Hartman]

Access can only update linked SQL Server tables if the table has a unique Index.

Access does not support temporal tables. If you create "temporary" tables in Access whether the FE or the BE, and you then delete either the rows or the table, Access does not clean up automatically. This waste space just accumulates as bloat as the db just gets larger and larger. It takes a C&R to remove the bloat.