If you declare as public in header then don't use Dim in procedure.
Consider DAO recordset.
Public rs As DAO.Recordset

Sub MySub()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTable")
'do something with recordset
End Sub
Be aware that public/global variable loses value in unhandled run-time error.