A while ago we were having a discussion about having a ADO objects feed data into an Access Multiple Items form. Today I discovered one can do almost exactly the same with a DAO.Recordset based on DAO.QueryDef.
Link to our prior Access Continuous Form fed via ADO objects:
"Example of ADO objects being bound to a Continuous / Multiple Items type form"
http://www.access-programmers.co.uk/forums/showthread.php?t=258559&page=2#post1319473
So my working DAO.Recordset version:
To build the DAO.QueryDef object and store it to the FE DB:
Then the code for the form that is going to use the DAO.QueryDef object:
So this success has me considering if I could swap out using FE Temp Tables to cache multiple records to be displayed in an Access Continuous Form, instead use a DAO.Recordset, but...
What about my use of FE Temp Tables where upon saving an edited record back to the BE DB, I select out of the BE DB that one updated record, refreshing the FE DB Temp Table, and then refresh the Access Form UI to display in the Mutiple Items grid that just updated record?
Is there a way I can do that same sort of thing retaining the rest of the unchanged rows already in the DAO.Recordset, delete by record ID the one being updated, and download a fresh copy of that updated record retaining the rest of the records already in the DAO.Recordset object?
I suppose I could do like my example code and define the "Dim daoRST As DAO.Recordset" object up at the form level so it would remain in-scope the entire time the particular form is open.
Is it possible to perform a delete by unique row ID within the populated DAO.Recordset, then select into the DAO.Recordset by unique row ID the record that was updated? Sort of using the DAO.Recordset as an in-memory table the Continuous Form gets bound to.
I am thankful,
Link to our prior Access Continuous Form fed via ADO objects:
"Example of ADO objects being bound to a Continuous / Multiple Items type form"
http://www.access-programmers.co.uk/forums/showthread.php?t=258559&page=2#post1319473
So my working DAO.Recordset version:
To build the DAO.QueryDef object and store it to the FE DB:
Code:
Public Function dao_builddao() As Boolean
Dim strSQL As String
Dim daoDB As DAO.Database
Dim daoQDF As DAO.QueryDef
strSQL = "SELECT [t].[pid],[t].[quoteid],[t].[metflg],[t].[metid],[t].[mettitle],[t].[toolstatusid],[t].[tooltypetitle],[t].[partnumber],[t].[parttitle],[t].[partvendortitle],[t].[toolstatustitle],[t].[lttotal],[t].[toolduedate],[t].[besttoolcost],[t].[prodpartflg]" & vbCrLf & _
"FROM [tblRptPartsToolCost] AS [t]" & vbCrLf & _
"ORDER BY [t].[partnumber]"
'Attach to the FE DB
Set daoDB = CurrentDb()
'Build the Query
Set daoQDF = daoDB.CreateQueryDef("testdao")
With daoQDF
.SQL = strSQL
.Close
End With
'Clean up the connection to the database
Set daoQDF = Nothing
Set daoDB = Nothing
dao_builddao = True
End Function
Code:
Option Compare Database
Option Explicit
Dim daoDB As DAO.Database
Dim daoRST As DAO.Recordset
Private Sub Form_Open(Cancel As Integer)
'Attach to the FE DB
Set daoDB = CurrentDb()
'Execute the saved QueryDef
Set daoRST = daoDB.OpenRecordset("testdao")
End Sub
Private Sub Form_Load()
With Me
.InsideHeight = 8400
'.InsideWidth = 2200
Set .Recordset = daoRST
End With
'Advance UI to the 7th record
DoCmd.GoToRecord acDataForm, "PartsToolCost_DAO", acGoTo, 7
End Sub
Private Sub Form_Close()
daoRST.Close
Set daoRST = Nothing
Set daoDB = Nothing
End SubWhat about my use of FE Temp Tables where upon saving an edited record back to the BE DB, I select out of the BE DB that one updated record, refreshing the FE DB Temp Table, and then refresh the Access Form UI to display in the Mutiple Items grid that just updated record?
Is there a way I can do that same sort of thing retaining the rest of the unchanged rows already in the DAO.Recordset, delete by record ID the one being updated, and download a fresh copy of that updated record retaining the rest of the records already in the DAO.Recordset object?
I suppose I could do like my example code and define the "Dim daoRST As DAO.Recordset" object up at the form level so it would remain in-scope the entire time the particular form is open.
Is it possible to perform a delete by unique row ID within the populated DAO.Recordset, then select into the DAO.Recordset by unique row ID the record that was updated? Sort of using the DAO.Recordset as an in-memory table the Continuous Form gets bound to.
I am thankful,
Last edited:
