Updating a DB Record with Word Macro

[Mavila]

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:


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.

[Mavila]

This is the Database execution part:


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

[gmaxey]

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:


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 ")"