Change Access Entry using Excel VBA

[Spurious]

Hi guys,

How do I change a certain entry in an Access table using Excel VBA?

Thanks

 

[Jon von der Heyden]

Hi

You will need to be much much more specific. Which records do you need to amend? I mean based on what rules?

 

[Trevor G]

Hi guys,

How do I change a certain entry in an Access table using Excel VBA?

Thanks

As Jon has stated you would need to give more details. You can use the Update query SQL code.

So if you can give more information then you will get a better response.

 

[Spurious]

Hi

You will need to be much much more specific. Which records do you need to amend? I mean based on what rules?

Ok, I got a listbox where I read out values from the database, the user will select a value and can choose to edit it.
I will then query with the values and get the unique key (ID) as a recordset (at least that's what my plan was).
Having that ID, I will then select the entire data entry and give the user the possibility to edit it.

Does it make sense?

 

[Jon von der Heyden]

Using ADO, right?

 

[Spurious]

Using ADO, right?

Exactly.

 

[Jon von der Heyden]

If it's just the SQL you need, then as example:

    strSql = "UPDATE " & strTableName & _
             " SET " & strField & "=" & strValue & _
             " WHERE " & strID & "=" & CStr(lngKey) & ";"

This is a just a quick copy paste from one of my projects.

strTableName must refer to the name of the table that contains the records.
strField must refer to the field name that is to be updated.
strValue is the value you want it to be.
strID is the name of the ID field (primary key, usually)
lngKey is the ID value for the record you want to update.

 

[Spurious]

Thanks, will try and report back.
I will probably not need the ID value then probably, I could just use the values from the listbox.

 

[Jon von der Heyden]

Will it identify the record distinctly? Because I imagine you won't want to be updating other records with same field values.

 

[Jon von der Heyden]

Oh and I suggest you use the Command.Execute method. You have no need to return a recordset.

Call command.Execute(RecordsAffected:=lngRecordsAffected, _
                             Options:=CommandTypeEnum.adCmdText Or ExecuteOptionEnum.adExecuteNoRecords)