Using ADO to Save, Recall and Update records in Access using Excel 2003

[MunniMagic]

I'm not too familiar with Access and linking to it from Excel so thought that on a recent project I would try and build up my skill set and use ADO, plus the use of a database in this instance was appropriate.

So far, I have figured out how to Recall a record that matches a certain condition and how to create a new record in a table (I used a Userform to write to certain cells and then use ADO to create a new record in the database using those cells).

The thing that I am having trouble with is Updating an existing record. I have a table named tbldata (below - also there are many more fields but have shown a few to keep things simple)

[TABLE="width: 500, align: left"]
<TBODY>[TR]
[TD]LogID
[/TD]
[TD]Centre
[/TD]
[TD]Team
[/TD]
[TD]employeename
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Manchester
[/TD]
[TD]Team 1
[/TD]
[TD]Ole Solskjaer
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Sheffield
[/TD]
[TD]Team 2
[/TD]
[TD]Peter Schmeichel
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Edinburgh
[/TD]
[TD]Team 1
[/TD]
[TD]Ruud Van Nistelrooy
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Belfast
[/TD]
[TD]Team 1
[/TD]
[TD]Cristiano Ronaldo
[/TD]
[/TR]
</TBODY>[/TABLE]
What I want to do is to update all fields in a row / record when the LogID matches a value in a certain cell in Excel, let's say:
Sheets("RecalledRecord").Range("C10").Value

I modified the macro I used to recall a record in the hopes that I would be able to achieve the above. Unfortunately I haven't been able to make it work and I can not figure it out.

I'm hoping this is where you will be able to help.
Below is the VBA code I have been using:

'=============================================================================
'- UPDATE AN ACCESS RECORD FROM EXCEL
'- FIND SPECIFIED RECORD IN AN ACCESS TABLE AND UPDATE IT
'=============================================================================
Public Sub updaterecord()

Dim strSQL As String
Dim strPath As String
Dim strSerialCENTRE As String
Dim wb As Workbook
Dim ws As Worksheet
Dim myRange1 As Range
Dim strLogID As String

'//Tell Excel what sheet to copy to and where you want the data!
Set wb = ActiveWorkbook
Set myRange1 = Sheets("RecalledRecord").Range("B3")

'//Tell Excel what serial number you need!
strSerialCENTRE = Sheets("RecalledRecord").Range("C10").Value

'dont seem to need the below with numbers, may only be text
strSerialCENTRE = "'" & strSerialCENTRE & "'"
strLogID = Sheets("main").Range("b10").Value

'Tell Excel where your database is and what SQL statement to run!
strPath = Sheets("setup").Range("d2").Value

strSQL = "UPDATE tbldata SET tbldata.centre = " & strSerialCENTRE & " WHERE tbldata.LogID = strLogID;"

'Go!
Call GetDataFromAccess(myRange1, strSQL, strPath)
      
Sheets("setup").Protect

End Sub

'-----------------------------------------------------------
Sub GetDataFromAccess(ByRef CopyToRange As Range, strSQL As String, strDatabasePath As String)
'-----------------------------------------------------------
'NOTE: Requires reference to ADO library:
'     1. Open the Visual Basic Editor (Alt + Fll)
'     2. Choose Tools | References
'     3. Check box for Microsoft ActiveX Data Object 2.8 Library (or higher)
'-----------------------------------------------------------
Dim rs As Recordset
Set rs = New Recordset
Dim strConnectionString As String

'Create a connection string
strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & _
    strDatabasePath & _
    ";Persist Security Info=False"

'Fetch data
Call rs.Open(strSQL, strConnectionString)

'Check for results
If (rs.EOF And rs.BOF) Then
    Debug.Print "There is no data"
Else
    'Write to value Excel Sheet
    Call CopyToRange.CopyFromRecordset(rs)
End If

'Clean up objects
If (rs.State And ObjectStateEnum.adStateOpen) Then rs.Close
If Not rs Is Nothing Then Set rs = Nothing

End Sub

I hope I have been clear with What I am trying to achieve and how I am trying to do it. Happy to provide more info. This has been doing my head in for a couple of days.