Set Access form record source based on SQL Server temporary table

Question

I use MS Access with SQL Server 2019. The application can process deliveries - meaning that I scan barcodes with serial numbers, which get saved into a SQL Server temporary table. After the process is finished and validated, the data from the temporary table is moved into the "real" table and the temporary table is disposed of.

Something like this:

pub_Conn.Execute "INSERT INTO #TempUnits " & _
                        "(ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds) " & _
                            "VALUES (" & pub_rsDelivery("ProductID") & ", " & _
                                    pub_rsDelivery("PurchaseOrderDetailID") & ", " & _
                                    pub_rsDelivery("DeliveryDetailID") & ", " & _
                                    "'" & strSerialNumber & "', " & _
                                    strConfig & ", " & _
                                    "'" & Format(DateAdd("m", pub_rsDelivery("Warranty"), pub_rsDelivery("DateShipped")), "yyyy-mm-dd") & "')"

And this is the transfer to my production tables:

    pub_Conn.BeginTrans
    
    pub_Conn.Execute "INSERT INTO tbl1Units (ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds) " & _
                        "SELECT ProductID, PurchaseOrderDetailID, DeliveryDetailID, SerialNumber, Config, SupWarrantyEnds FROM #TempUnits"
                        
    pub_Conn.Execute "SELECT DeliveryDetailID, COUNT(*) AS QtyDelivered INTO #TempUnitsQty FROM #TempUnits GROUP BY DeliveryDetailID"
    pub_Conn.Execute "INSERT INTO #TempUnitsQty (DeliveryDetailID, QtyDelivered) " & _
                        "SELECT DeliveryDetailID, Quantity FROM #TempProducts"
    
    pub_Conn.Execute "UPDATE tbl1DeliveryDetails " & _
                        "SET ActualQuantity = T.QtyDelivered " & _
                        "FROM #TempUnitsQty T JOIN tbl1DeliveryDetails D ON T.DeliveryDetailID = D.DeliveryDetailID"

    pub_Conn.Execute "DROP TABLE IF EXISTS #TempProducts"
    pub_Conn.Execute "DROP TABLE IF EXISTS #TempUnitsQty"
    pub_Conn.Execute "DROP TABLE IF EXISTS #TempUnits"
    
    pub_Conn.Execute "UPDATE tbl1Deliveries SET DateReceived = GETDATE() WHERE DeliveryID = " & Forms!frmDeliveryDetails!DeliveryID
    
    pub_Conn.CommitTrans
    MsgBox "Dodávka byla v pořádku přijata.", vbInformation + vbOKOnly, "Úspěch"

Works great, but I want to be able to see the progress of scanning, for example:

Product A (0/5 items scanned)

This is no problem when I use local Access tables and a couple of queries, but I would like to know if and how I can feed the form record source from a SQL Server temporary table. Something like:

Me.RecordSource = pub_Conn.Execute "SELECT * FROM #TempUnits"

(wrong syntax, but just to clarify what I'm looking for)

score 0 · Answer 1 · answered Aug 09 '22 at 13:19

I have figured it out myself. I created a pass through query in Access:

SELECT A.ID, D.ProductName, C.PurchaseOrderCode, A.SerialNumber
FROM ##TempUnits A 
JOIN tbl1PurchaseOrderDetails B ON A.PurchaseOrderDetailID = B.PurchaseOrderDetailID
JOIN tbl1PurchaseOrders C ON B.PurchaseOrderID = C.PurchaseOrderID
JOIN tbl1Products D ON B.ProductID = D.ProductID
ORDER BY A.ID DESC

Set Access form record source based on SQL Server temporary table

1 Answers1