Solved How to avoid the domain functions in Ms Access (1 Viewer)

[nector]

Sorry people I'm not a friend of the domain functionality for obvious reason, slow performance especially against MS SQL Server, now the objectives are almost achieved through the help from this forum I'm now able to enter data in the parent table & child table at the same time and link the foreign key by using the DLAST function which I'm not comfortable with . Any idea to replace the Dlast function below, see VBA code :

Option Compare Database
Option Explicit

Private Sub CmdJson_Click()
Dim http As Object, JSON As Object, i As Integer
Dim item As Variant
Dim Z As Integer
Dim Y As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rst As DAO.Recordset
Set db = CurrentDb
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "http://jsonplaceholder.typicode.com/users", False
http.Send
Set rst = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)
  Y = 0
      rst.AddNew
      rst("SalesDate") = Date
      rst("InvoiceNumber") = "154696"
      rst.Update

Y = Y + 0

MsgBox ("completed Invoice Header")

Set rs = db.OpenRecordset("tblInvoiceDeatils", dbOpenDynaset, dbSeeChanges)
Set JSON = ParseJson(http.responseText)
'i = 2
For Each item In JSON
  
'Process data.
    Z = 1
    
        rs.AddNew
        rs("Id") = item("id")
        rs("FirstName") = item("name")
        rs("username") = item("username")
        rs("email") = item("email")
        rs("street") = item("address")("street")
        rs("suite") = item("address")("suite")
        rs("city") = item("address")("city")
        rs("zipcode") = item("address")("zipcode")
        rs("lat") = item("address")("geo")("lat")
        rs("lng") = item("address")("geo")("lng")
        rs("phone") = item("phone")
        rs("WebSite") = item("website")
        rs("catchPhrase") = item("company")("catchPhrase")
        rs("Sname") = item("company")("name")
        rs("bs") = item("company")("bs")
        rs("InvoiceID") = DLast("InvoiceID", "tblCustomerInvoice")
        rs.Update
        Z = Z + 1
    Next
   MsgBox ("completed Invoice Deatils")
 
    rs.Close
    rst.Close
    Set rst = Nothing
    Set rs = Nothing
    Set db = Nothing
    Set JSON = Nothing
    Set item = Nothing
End Sub

 

[arnelgp]

i think you mean DMax()?

 

[ebs17]

rs("InvoiceID") = DLast("InvoiceID", "tblCustomerInvoice")

One step before, you had created this ID in the table. Simply record and remember so that you can then use it straight away.

Josef had already implemented this in one of your previous topics. So you can read that. There are articles that are very worthwhile to be read several times.

 

[ebs17]

I'm not a friend of the domain functionality for obvious reason, slow performance

The real problem is not the domain aggregate function, but rather its application by the developer.

A domain aggregate function is a complete query including establishing the connection to the table, encapsulated in a function. It returns exactly one value.

Of course, to put it mildly, it is very clumsy if you need many, many values and then use this function with its effort in constant fire, instead of using a single real query that does everything at once.

So you shouldn't blame your own clumsiness on an innocent function.

 

[arnelgp]

So you shouldn't blame your own clumsiness on an innocent function.

 

[theDBguy]

In SQL Server, you can also use the @@IDENTITY function to get the latest autonumber assigned to a new record - if that's what you're trying to do.

 

[The_Doc_Man]

Be aware that the LAST function OR the DLAST function as applied to a table is ambiguous in which record it returns. That is because in a relational database, a table is an UNORDERED set of records. Typically, the "last" record is the record most recently (i.e. chronologically recent) updated in the table. In an ordered QUERY, you can use DLAST to find the last record in a sorted list. In a table, not so clear. The problem is that if you are using a shared database with occasional user updates from multiple users, LAST/DLAST potentially changes after each update or delete operation.

 

[ebs17]

In SQL Server, you can also use the @@IDENTITY function

In Jet SQL too.

However, this is not necessary here, as this ID is generated via a recordset, which means that a new auto value is already available and can be read after the rs.AddNew. Why should you leave this and start a new query?

 

[theDBguy]

In Jet SQL too.

However, this is not necessary here, as this ID is generated via a recordset, which means that a new auto value is already available and can be read after the rs.AddNew. Why should you leave this and start a new query?

I won't. Just pointing out another option available, since the OP mentioned using SQL Server as BE.

 

[nector]

However, this is not necessary here, as this ID is generated via a recordset, which means that a new auto value is already available and can be read after the rs.AddNew. Why should you leave this and start a new query?

The issue here is to get the last Primary Key since the invoice header details are inserted first on the basis of that details the last query now use that primary key to act as foreign key and link the parent latest data. If you want, just run that macron on that form you see that all is working okay, my worry was on the SQL server to use that method. But since ebs17 corrected me, I will leave that way since it will only concentrate on one record at a time.

 

[Pat Hartman]

As the help entry for @@Identty indicates, there may be a better choice. Usually Scope_Identity

@@IDENTITY (Transact-SQL) - SQL Server

@@IDENTITY (Transact-SQL)

learn.microsoft.com

SCOPE_IDENTITY (Transact-SQL) - SQL Server

SCOPE_IDENTITY (Transact-SQL)

learn.microsoft.com

 

[Josef P.]

In the example, DLast is pretty much the worst way to get the value of InvoiceID.

Set rst = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)
  Y = 0
      rst.AddNew
      rst("SalesDate") = Date
      rst("InvoiceNumber") = "154696"
      rst.Update

' Why isn't the InvoiceID already queried here? That would be a task from the recordset that is currently open.

[.. some code ..]

Set rs = db.OpenRecordset("tblInvoiceDeatils", dbOpenDynaset, dbSeeChanges)
Set JSON = ParseJson(http.responseText)
'i = 2
For Each item In JSON

'Process data.
    Z = 1
  
        rs.AddNew
        rs("Id") = item("id")
        [...]
        rs("InvoiceID") = DLast("InvoiceID", "tblCustomerInvoice")  '<---  ! ... An Invoice ID is determined for each run, but is this even the one from above?
        rs.Update
        Z = Z + 1
    Next

Suggestion:
If ACE/Jet backend:

Set rst = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)
      Y = 0
      rst.AddNew
      rst("SalesDate") = Date
      rst("InvoiceNumber") = "154696"
      NewInvoiceID = rst.Fields("InvoiceID").Value
      rst.Update

If linked table from active DBMS (SQL-Server & Co.):

Set rst = db.OpenRecordset("tblCustomerInvoice", dbOpenDynaset, dbSeeChanges)
      Y = 0
      rst.AddNew
      rst("SalesDate") = Date
      rst("InvoiceNumber") = "154696"
      rst.Update
      rst.Bookmark = rst.LastModified
      NewInvoiceID = rst.Fields("InvoiceID").Value

see: https://www.access-programmers.co.u...nd-child-tables-in-access.329974/post-1905358

 

[Pat Hartman]

People use "last" when they mean "most recent" and "most recent" is usually implemented as Max() on a date field or an autonumber. "last" simply has no meaning in a relational database because it is a moving target.

 

[nector]

Thank you, Pat Hartman!

For me it's very clear and I'm okay with your explanation.

Regards

Chris