John,

The code below uses ADO to connect to the Access database. The query string
assumes the name of the table to be updated is: Table1. If you have the
database password protected, you will need to pass that information in the
connect string.

You will need to set a reference to the latest "Microsoft ActiveX Data
Objects 2.x Library" under Tools | References.

Troy


Sub ADO_UpdateAccessDB()

Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim sDBfile As String
Dim sSQL As String

'Path and name of Access database file.
sDBfile = "C:\My Documents\Junkdb1.mdb"

'Open a connection to the Access database.
Set oConn = New ADODB.Connection
oConn.Open _
"driver={Microsoft Access Driver (*.mdb)};" & _
"dbq=" & sDBfile & ";" & _
"uid=admin;pwd="

'Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn

'Define the Update query.
sSQL = "UPDATE Table1 SET Table1.Category = 'oranges' "
sSQL = sSQL & " WHERE (Table1.Category = 'apples');"

oCmd.CommandType = adCmdText
oCmd.CommandText = sSQL
'Execute the command.
oCmd.Execute

Set oCmd = Nothing
Set oConn = Nothing

End Sub


"John H" wrote in message
...
Hi all!

Simplified Example:

My Access database has 6 columns, the fourth of which is called
"Category".
This category column consists of, say, 30,000 entries, each entry being
either "Apples", "Pears" or "Plums".

Please could someone help me with an example of some code to change all
references in the "Categories" column of my database from "Apples" to
"Oranges".

Thank you in advance

John

I am using Excel version 2002