Stack Exchange
Populate RowSource and RecordSource without a persistent db connection
As the title suggests, I wondered if it was possible to populate a RecordSource or RowSource without maintaining a persistent connection to the external database I am querying.
I have tried two methods listed below to no avail:

IN Clause

ps.RecordSource = " SELECT TM_Adjudicator AS Adjudicator, Nz(Sum(PS_DB_view.PS_Points+PS_DB_view.PS_Adhoc),0) AS [Total Points] " & _
                      " FROM SD_Adjudicators_view " & _
                      " LEFT JOIN PS_DB_view ON (((cstr(SD_Adjudicators_view.TM_Adjudicator) = cstr(PS_DB_view.PS_Adjudicator)) AND cdate(PS_DB_view.PS_Date) >= #" & dtStart_fmt & "#) AND cdate(PS_DB_view.PS_Date) <= #" & dtEnd_fmt & "#) " & _
                      " IN '' [MS Access;PWD=**STRING**;DATABASE=I:\**PATH**\PM_DB_view.accdb] " & _
                      " WHERE SD_Adjudicators_view.TM_TeamName = 'Z999' " & _
                      " GROUP BY TM_Adjudicator; "

(this maintains a persistent connection for the life of the userform

DB.Connection

Set dbs = OpenDatabase("I:\**PATH**\PM_DB_testenvironment.accdb", False, False, "MS Access;**STRING**")
Set rst = dbs.OpenRecordset(" SELECT TM_Adjudicator AS Adjudicator, Nz(Sum(PS_DB_view.PS_Points+PS_DB_view.PS_Adhoc),0) AS [Total Points] " & _
                                                                " FROM SD_Adjudicators_view " & _
                                                                " LEFT JOIN PS_DB_view ON (((cstr(SD_Adjudicators_view.TM_Adjudicator) = cstr(PS_DB_view.PS_Adjudicator)) AND cdate(PS_DB_view.PS_Date) >= #" & dtStart_fmt & "#) AND cdate(PS_DB_view.PS_Date) <= #" & dtEnd_fmt & "#) " & _
                                                                " WHERE SD_Adjudicators_view.TM_TeamName = 'Z999' " & _
                                                                " GROUP BY TM_Adjudicator; ")

    Set ps.Form.RecordSource = rst

    rst.Close
    Set rst = Nothing

    dbs.Close
    Set dbs = Nothing

This too maintains a persistent connection until the close function is called, and the RowSource / RecordSource values are lost.
Is there anyway to read the data, display it, and close the database whilst keeping the records visible?

The simplest route would be to load your data into a local temp table. Alternatively you could use an array?

I did think about this @Minty - but the problem is there could be up to 400 users in here, and creating and destroying this many tables corrupted the file last time. I think I may have to admit defeat and just use a linked table for this many users.

Each user should have their own front end, so the table would be local just to the local user. If the data set is the same consider emptying the table and re-using it, rather than deleting.

Yes, there is.
You can use an ADODB disconnected recordset as your record source.
Code:
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=I:\**PATH**\PM_DB_testenvironment.accdb;Jet OLEDB:Database Password=""**STRING**"""
Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient
rs.Open "SELECT stuff FROM Table", conn, 3, 3 'adOpenStatic, adLockReadOnly
Set Me.Recordset = rs
conn.Close
Set rs.ActiveConnection = Nothing
Set conn = Nothing

Note that many features, like sorting and filtering, won't work unless you first re-establish the database connection.
DAO unfortunately doesn't offer this functionality.
