As someone else noted, you need to reference the ADO object library
when using ADO, the code is as below.

I've already created a looping version of this which can read  hundreds
of bits of data  in one hit, my next challenge will be creating a sub
that sends the data the other way, to save the data back into Access
when I'm done modifying it, or create a new entry when it is data
typed in from scratch. If anyone wants to preempt my question and
give me a few lines of SQL and ADO code giving the appropriate
connection strings etc, I will be forever in your debt. :)

Here is the fully working macro.

Someone did point out to me that the macro  might have problems with
surnames like O'Donnel and the like because the ' will mangle the
string, but he said a workaround is to create a global varable called
SingleQuote with the value ' and use that in place of the '.

Private Sub ReadFromAccess_Click()

'Step 1: Declare your variables

Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

'Step 2: Declare your connection string

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & [Databasepath].Value

'Step 3: Build your SQL statement

MySQL = "SELECT tblClientCodes.[Client surname] FROM tblClientCodes
WHERE (((tblClientCodes.[ClientID])= '" & [ClientIdentifier].Value & "'))"

'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Copy the recordset to Excel

[Client1Surname].CopyFromRecordset MyRecordset

End Sub