Did you try to determine the amount of records this way:
Set rs = CurrentDb.OpenRecordset("select * from mytable", dbOpenSnapshot)
rs.MoveLast
debug.print rs.RecordCount
That would explain the difference in time
"select *" only opens the table
"select count(*)" uses an index
"select *" in combination with the MoveLast method executes the sql statement on your local computer and uses the data in a table on a remote computer, the whole table is copied over the network.
I could reproduce the problem, see code below:
t = Timer
For i = 1 To 100
Set rs = CurrentDb.OpenRecordset("select * from mytable", dbOpenSnapshot)
rs.Close
Next i
Debug.Print Timer - t
' 7.421875 seconds
t = Timer
For i = 1 To 100
Set rs = CurrentDb.OpenRecordset("select count(*) from mytable", dbOpenSnapshot)
rs.Close
Next i
Debug.Print Timer - t
' 6.746094 seconds
t = Timer
For i = 1 To 100
Set rs = CurrentDb.OpenRecordset("select * from mytable", dbOpenSnapshot)
rs.MoveLast
rs.Close
Next i
Debug.Print Timer - t
' 78.52344 seconds
============================================================
'C' is shorthand for Confusion, 'C++' much more confusion...