In my MS Access FrontEnd connected to an SQL Server, I’m using a combination of pass-through queries and “normal” ODBC connection via file-DSN (for “easy bound forms”) for my forms. Within VBA functions and Subs I use some ADO connections to directly change data in the tables.
I need pass-through queries for some forms as I need to use DISTINCT keywords on tabels with MEMO / NVARCHAR(max) fields.
Currently I’m using the pass-through query to get the data into my forms like that:
strSQL = "SELECT DISTINCT tbl_Changes.Change_Nr, Title, Comment FROM [tbl_Changes] inner JOIN tbl_Parts ON tbl_Changes.Change_Nr = tbl_Parts.Change_Nr " & _
"WHERE '" & strProject & "' IN (" & strAllProjects & ") " & _
"ORDER BY tbl_Changes.Change_Nr DESC"
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = Application.TempVars("tempvar_StrCnxn")
qdf.sql = strSQL
qdf.ReturnsRecords = True
'Debug.Print strSQL
Set rst = qdf.OpenRecordset
Set Forms![frm_ChangePartsOverview].Recordset = rst
Forms![frm_ChangePartsOverview].Requery
'No rst.Close to have the data still in the Form!
Set qdf = Nothing
But I think this is not the correct approach to do it, or is it?
Advertisement
Answer
Regarding your concerns in the comments:
rst.Close is redundant in VBA, the garbage collector takes care of closing recordsets and only in very rare cases will you need to close them manually.
In theory, you could have a Form_Close or Form_Unload handler to close the recordset, but there’s no need.
DAO also caches database connections and reuses them when needed (something which some users use to keep passwords out of connection strings, since if a connection is established with the password, it’s cached and can be used in linked tables without specifying it again, but this also causes bugs, for example with SET IDENTITY_INSERT ON which DoCmd.TransferDatabase uses).
That said, there are alternate approaches, but they all come with advantages and disadvantages.