Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | | | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Excel Database or Access?? | Excel Worksheet Functions | |||
Do I need Excel or Access for my database needs? | Excel Discussion (Misc queries) | |||
Linking Access Database to Excel | Excel Discussion (Misc queries) | |||
Excel database access | Excel Programming |