Amend Access database from Excel
 

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

Amend Access database from Excel
 

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

Amend Access database from Excel
 

Troy

Thank you for responding.

I followed your post to the letter including setting references to the
Microsoft ActiveX Data Objects 2.x Library and ran the code directly from
Excel. No errors were reported but the database remained unchanged.

Any ideas where I am going wrong.

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

Amend Access database from Excel
 

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