"Find and Replace" a String in an Access Database

Hi,

Can you elaborate more on your question please?

"MyColumn.replaceAll("Morning", "Afternoon")."
Do you want to replace all the morning with afternoon?

Regards
Dave

what about this generic sql statement (that should work in java as well):
Update MyTable Set MyColumn='Morning' where MYColumn='Afternoon'

Also, are you sure that if you take the suggestion provided in the linked thread
and then use it with jdbc statement by calling updateQuery on MS access DB that fails?
If so, what is the exception?

I only want to update a portion of the column, so it's definitely not going to be "Set MyColumn='Morning' where MYColumn='Afternoon'".

To "objects", is this the only way? Kind of tedious. I thought there might be some ready commands which I can just call.

> I thought there might be some ready commands which I can just call.

Not if your database doesn't support it sorry.
Youy could use an updateable result set (again if supported)
http://www.javaalmanac.com/egs/java.sql/UpdateRow.html

Which driver are you using?

BTW, what's wrong in shooting a query directly like aozarov mentioned:

>> Update MyTable Set MyColumn='Morning' where MYColumn='Afternoon'

Why do you want to select those rows first?

>> I only want to update a portion of the column, so it's definitely not going to be "Set MyColumn='Morning' where
>> MYColumn='Afternoon'".

Can you explain a little more what you mean by portion of the column? Maybe an example will make it clearer.

How about this (warning: this is Access specific code NOT ansi sql):

  update table set MyColumn=left(MyColumn, inStr(MyColumn,'Afternoon')) & 'Morning' & mid(MyCoumn, inStr(MyColumn,'Afternoon')+9)
  where MYColumn like '*Afternoon*'

This will replace the first occurance of 'Afternoon', with 'Morning' in the column.  To replace ALL, keep executing the statement until 0 rows are updated.

"left" and "inStr" are VBA functions or not? if they are, then I'm not able to use them in my Java app.

Hi, alex_ge.

The following works for me one a Win2k Pro system running J2 v1.4.2_03 and interacting with an Access 2000 database table (table is "Table1").  The code that replaces all occurrences of "edwardiii" in my table's "Supplier" column with "ed" is:

     pStatement.execute("update Table1 set Supplier = 'ed' where Supplier ='edwardiii'");

I then pull all recordsets from the "Table1" table and display them in a JTextArea.  Here is the complete routine:

       Connection con;    
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String MoreTextString;
            con = DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=C:/MyDatabase.mdb","","");            
            Statement pStatement = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pStatement.execute("update Table1 set Supplier = 'ed' where Supplier ='edwardiii'");
            pStatement.execute("select * from Table1 where Supplier = 'ed'");
           
            ResultSet rs = pStatement.getResultSet();    
            while (!rs.isLast())
            {
                rs.next();
                String Id = rs.getString("ID");
                String Supplier = rs.getString("Supplier");                
                moreJTextArea.setText(moreJTextArea.getText() + "\n" + Id + " " +Supplier);
                               
            }
            rs.close();
            pStatement.close();
            con.close();            
                   
        }
        catch (SQLException e)
        {
            System.out.println(e.getMessage());
           
        } catch (Exception e)
        {
            System.out.println(e.getMessage());
        }

Thanks all for your efforts. It seems there are no other solutions, other than select, replace and update, as suggested by "objects". So I have accepted his suggestion as the answer