Updating a DB Record with Word Macro

Not sure if this is an Access or Word question, but I'm trying to updated a record in Access from a Word macro. The Update Command statement uses a variable for the field name, but I think I'm not doing some right or maybe you can't use a variable for a field name. I'm getting the "No value given for one or more required parameters" error. I've been over this several times to make sure the Table Name and Field Names are spelled correctly, etc., and I can't figure it out:

Code:

---

strUpdateText = "UPDATE [My Preferences] SET ['" & Pref_Field_Nm & "] = ('" & PrefData & "') WHERE ([User Co Nm] = '" & UserCoNm & "')"

---

Is there a syntax problem? Can you not use a variable for a Field Name?

Thanks.

This is the Database execution part:

Code:

---

    Dim Cn As ADODB.Connection '* Connection String
    Dim oCm As ADODB.Command '* Command Object
    Dim NumID As Long
    Set Cn = New ADODB.Connection
    Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & MyDataPath & "';Persist Security Info=False"
    Cn.ConnectionTimeout = 40
    Cn.Open
    Set oCm = New ADODB.Command
    oCm.ActiveConnection = Cn
    oCm.CommandText = strUpdateText
    oCm.Execute
    If Cn.State <> adStateClosed Then
    Cn.Close
    End If


---

Mavila,

I write to Excel or Access using as statement like this:

UPDATE [Sheet1$] SET [FullNameX]='Joe',[AgeX]='23',[GenderX]='Male' WHERE [SSNX]='1234'

And develop that string using:

Code:

---

Function fcnGetFFDataUpdateStrSQL(oDoc As Document, strTableName As String, bExtractFileName As Boolean, strKeyField As String, strKeyFieldVal As String, _
                            Optional bExcel As Boolean = False) As String
Dim oFF_FormData As FormField
Dim strSet As String
Dim lngFFIndex As Long
Dim strFF_Data As String
  'Initialize SQL statement variable values.
  strSet = vbNullString
  'Extract the document FormField control data.
  For lngFFIndex = 1 To oDoc.FormFields.Count
    Set oFF_FormData = oDoc.FormFields(lngFFIndex)
    If Not oFF_FormData.Name = strKeyField Then
      strFF_Data = oFF_FormData.Result
      strFF_Data = Replace(strFF_Data, Chr(39), Chr(39) + Chr(39))
      strSet = strSet & "[" & oFF_FormData.Name & "]='" & strFF_Data & "',"
    End If
  Next lngFFIndex
  If bExtractFileName Then
    strSet = strSet & "[Record Name]='" & oDoc.Name & "'"
  Else
    strSet = Left(strSet, Len(strSet) - 1)
  End If
  fcnGetFFDataUpdateStrSQL = "UPDATE [" & strTableName & "$] SET " & strSet & " WHERE [" & strKeyField & "]='" & strKeyFieldVal & "'"
CleanUp:
  Set oFF_FormData = Nothing
lbl_Exit:
  Exit Function
End Function

---

Could it be the parens e.g. "(" and ")"