Changing MS Access database records

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
KenD
Posts: 4
Joined: Sat Feb 02, 2008 12:15 am

Changing MS Access database records

Post by KenD »

I need to change many records in an Access database. Thanks to a previous thread in this forum, I am able to create a query which I can use to edit the database. Now I have two questions:

1) In the query design grid I want to use a function to select records based on a portion of the text in a field. For example, if the records are

abcde
abcdf
fdcba

I want to show only the records that begin with 'abcd'.

2) Next, I want to change 'abcd' to 'gh' in each of these records.

How can I do this? There are several thousand records, so it is not practical to change them one by one.

Thanks for your help.
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Changing MS Access database records

Post by DrewJensen »

Wellllll

The first thing is that Base does not have a nice GUI based query tool that supports update, insert and delete queries.

Which means that you must use the correct SQL commands, for the database you are working with - in this case MS Access - in the SQL window in order to do this type of mass update.

So - lets break this down.

I have a mdb file here and I added a table - Table1( ID, txt_val ) I'll use that for the example.

step one - find the proper records.
For this you can can use a LIKE command.

SELECT ID, txt_val FROM Table1 WHERE txt_val LIKE 'abcd%'

The * is a wild card that matches to 0 or more characters, so it will find all the records where val_txt starts with 'abcd'.

Great you had that much already though.

Step two - the SQL update command.

UPDATE Table1
SET txt_val = <some_value>
WHERE txt_val LIKE 'abcd%';

Ok that is the generic structure of the update command - but now you need to construct <some_value>

Step three - build the new value.

MS Access in SQL mode supports the following string functions: LEFT, RIGHT, MID, LEN AND INSTR

Here is the statement then that will do what you wanted.

Code: Select all

UPDATE `Table1`
set `txt_val` = 'gh' & right( `txt_val`, len( `txt_val` ) - len('abcd') )
WHERE `txt_val` LIKE 'abcd%';
In my case I just open the SQL window, Tools > SQL, copy and paste that into the command window - click 'execute'. The result box tells me it ran successfully - open Table1 and I now have
ghe
ghf
fdcba
 Edit: One last thing - be sure to make a copy of your database before you start your mass updates - the changes are permanent when done this way using SQL - there is no roll back function as there is with the MS Access update GUI based query - when you click execute it can! 
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
KenD
Posts: 4
Joined: Sat Feb 02, 2008 12:15 am

Re: Changing MS Access database records

Post by KenD »

Drew - Thank you very much for your detailed answer. It looks like exactly what I needed to know. I will let you know how it works.

Thank you!

Ken
Morgantown WV
Post Reply