Here's an idea I did not see in your post. Did you set a reference
to the ADO library using the reference command in the VBE?

Select Tools > References. Find the Microsoft ActiveX Data Objects
with the highest version number. I use Microsoft ActiveX Data
Objects 2.8 Library.

Yes, I did that already, so that's not it.

Always display your SQL statement somewhere before you execute it. It is
often almost impossible to find a simple problem just by looking at the code
that creates the SQL statement. You can put it into a cell so that you can
see it, or put it in the status bar, or even just put it in a MsgBox. But
always do it, and always include that statement in messages when you are
asking questions about databases.

Seeing your connect string is also generated, you need to do the same for
it.

Is the ClientID a number? If it isn't, you need to put quotes around it.
If it is a number, then you need to show us the complete SQL statement and
the connect string.

Yes, inserting a couple of single quotes into the generated string
around the ClientID did the trick.

Many thanks to all for your assistance.

Hope you don't mind me asking...
What was your final connection string please??

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

Actually I figured it out for myself. There is no step 5 any more,
step 3 and 4 as below will update an existing record.

'Step 3: Build your SQL statement

MySQL = "UPDATE tblClientCodes Set tblClientCodes.[Client surname] =
'" & [Client1Surname].Value & "' WHERE (((tblClientCodes.[ClientID])=
'" & [ClientIdentifier].Value & "'))"


'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockOptimistic


That won't actually do anything if it is a new record you are
creating. To add a new record you need to use INSERT instead of
UPDATE. Like so:

'Step 3: Build your SQL statement

MySQL = "INSERT INTO tblClientCodes (ClientID, [Client surname])
VALUES ('" & [ClientIdentifier].Value & "','" & [Client1Surname].Value
& "')"

'WHERE (((tblClientCodes.[ClientID])= '" & [ClientIdentifier].Value &
"'))"



'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockOptimistic





Hope this helps. Next step I guess would be setting up a test to see
if the record exists, so it knows whether to use the UPDATE SQL or the
INSERT SQL.

But it appears that reading and writing from a database  to Excel is
actually pretty easy to do.