Update Stmt - Refer to Field Nm with Variable

[Mavila]

I'm creating a Word macro that Updates an Access DB Table. I have to redo this several times, so I thought if I could refer to the field name with a variable, I could just write the update statement once and then call it each time I need to.

I'm getting a run time error, "No value given for one or more of the required parameters." I'm assuming this is referencing the variable being used for the field name.

From what I'm reading, it doesn't sound like this is possible, so I'm appealing for a good soul's help before I abandon this approach. Thanks for any help anyone might be able to provide.

"FieldNm" is the variable I'm using.

Code is as follows:

Dim Cn As ADODB.Connection '* Connection String
Dim oCm As ADODB.Command '* Command Object
Dim MyProjectIDNum 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 = "UPDATE [My Projects] SET '" & FieldNm & "' = ('" & UpdateData & "') WHERE [ID]= '" & MyProjectIDNum & "'"

oCm.Execute

If Cn.State <> adStateClosed Then
Cn.Close
End If

Application.StatusBar = False
If Not oCm Is Nothing Then Set oCm = Nothing
If Not Cn Is Nothing Then Set Cn = Nothing

[Mavila]

I guess this is not good practice. I just thought it would be more handy to use a variable and CALL a single instance of the UPDATE procedure rather than have several instances of the UPDATE procedure throughout the code.

Oh well.

[stanl]

I guess this is not good practice. I just thought it would be more handy to use a variable and CALL a single instance of the UPDATE procedure rather than have several instances of the UPDATE procedure throughout the code.

Oh well.

try



oCm.CommandText = "UPDATE [My Projects] SET [" & FieldNm & "] = ('"
& UpdateData & "') WHERE [ID]= '" & MyProjectIDNum &ick "'"


I use variables all the time in SQL code, but what I do is create SQL statements in a memo field, then do a quick replace prior to statement execution.