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

N

nector

Member
Local time
Today, 06:26
Joined
Jan 21, 2020
Messages
443
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 :

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
 

Attachments

i think you mean DMax()?
 
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.
 
nector said:
I'm not a friend of the domain functionality for obvious reason, slow performance
Click to expand...
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.
 
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.
 
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.
 
theDBguy said:
In SQL Server, you can also use the @@IDENTITY function
Click to expand...
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?
 
ebs17 said:
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?
Click to expand...
I won't. Just pointing out another option available, since the OP mentioned using SQL Server as BE.
 
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.
 
In the example, DLast is pretty much the worst way to get the value of InvoiceID.

Code: 
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:
Code: 
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.):
Code: 
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
 
Last edited:
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.
 
Thank you, Pat Hartman!

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

Regards

Chris
 
You must log in or register to reply here.

Similar threads

N
Solved Importing a Json File into Ms Access Tables
Replies
5
Views
714
Josef P.
J
N
Solved Scraping Json from a web using VBA
Replies
11
Views
927
isladogs
isladogs
N
Solved Storing received Json from the internet into Parent table & child table at one go
2
Replies
21
Views
1,125
nector
N
N
Solved Caching recordset in MS Access VBA
2
Replies
26
Views
1,618
tvanstiphout
T
N
Solved Progress meter for reports & queries in MS Access
Replies
16
Views
698
tvanstiphout
T

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom