Enjoy!

'Creating a Passthrough query read only recordset on the fly in VBA
'Put this in a normal module, call it from any other module

'This is requiring DAO.Database and DAO.QueryDef objects so that
' the recordset will survive after the function ends.

'The DAO objects can just be unset variables of that type.

'You will want to set the Db and qdf variables you pass in to Nothing after you are done
' with the recordset.

Public Function OpenSQLServerRsSnapshot(sSQL As String, ByRef Db As DAO.Database, _
                                        ByRef qdf As DAO.QueryDef) As DAO.Recordset
   Dim retVal As DAO.Recordset
   If Db Is Nothing Then Set Db = CurrentDb()
   If qdf Is Nothing Then Set qdf = Db.CreateQueryDef("")
   qdf.Connect = GetConnectionString()
   qdf.SQL = sSQL
   Set retVal = qdf.OpenRecordset(dbOpenSnapshot)
   Set OpenSQLServerRsSnapshot = retVal
End Function

'You will need to update this connection string for your SQL Server and ODBC Drivers

Private Function GetConnectionString() As String
   GetConnectionString = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=MYSERVER;" & _
     "Trusted_Connection=yes;DATABASE=MyDb;MARS_Connection=yes;"
End Function