Troy

Ignore my previous post.

Got it working perfectly.

Thanks a bunch

John


"TroyW" wrote in message
...
| 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
|
|
|
|
|
|
|