Visual Basic code (using .edit and .update recordset methods) to
update AS/400 table using ODBC

Greetings!

You can use standard VB ODBC code to write to an AS/400 table, but,
first you must make an index.  Below are two ways to accomplish this:

If you're using ODBC through Visual Basic to access data from an
AS/400 table (file), the data is read-only unless the table has an
index with associated unique keys. This is not due to any limitations
with ODBC; instead, this requirement is due to Visual Basic's use of
optimistic record locking — which requires that all records that are
updated have a unique key value. You can create a unique index with
either SQL or DDS. To create a unique index using SQL, you issue the
following statement:

CREATE UNIQUE INDEX indexname ON
       libraryname/tablename(columnname)

You can issue this SQL statement using the STRSQL (Start SQL) command
to start Interactive SQL, using the RUNSQLSTM (Run SQL Statement)
command, from SQL in a high­level language program, or from an ODBC
application. To create a unique index using DDS, you code a DDS source
file that uses the UNIQUE keyword.

From:
http://www.nease.net/~jnkey/as400flr/400tnt/odbctnt/Performance.htm

Also, Thomas D. Chamberlain (tdcflcn@infinet.com) posts in
comp.lang.basic.visual.3rdparty on 1995/08/01 in message ID
<3vmdn0$h2a@horus.infinet.com> :

Do you have Microsoft Access?

  If so, create a Database with no Tables.  Use the "File", "Attach
Table" option.  When the window appears select "<SQL Database>" then
select the ODBC driver you are using to access the AS/400.  This
should force you to log on to the AS/400.

  Once you have attached an AS/400 file to the Access Database you
need to create an index over the Table.  This is done with the "Create
unique index <INDEX FILENAME> on <AS/400 DATABASE FILENAME>(<INDEX
FIELD>, <INDEX FIELD>)".

  To use this new set-up in Visual Basic you can use the normal "Data
Control" object to access the AS/400 with no SQL statements.

Once that is done, then it is a simple matter of connecting, editing
and so on.  Pseudo code follows:

    Dim Connect As String
    Connect$ = "ODBC;DSN=RMA;UID=" & User & ";PWD=" & Password &
";Database=RMA"
    Set DB = OpenDatabase("", False, False, Connect$)
    SQL$ = "Select rma_no, rma_date from rma_mstr"
    Debug.Print SQL$
    Set RS = DB.OpenRecordset(SQL)
    If month(RS.Fields("rma_no")) < month(Now) Then
        RS.Edit
        RS.Fields("rma_no") = 0
        RS.Fields("rma_date") = Now
        RS.Update
    End If

I hope this has answered your question; if you require further
information, please ask for a clarification before rating this answer.

Thank you for using Google Answers!

Search Strategy:

visual basic sample code update ODBC edit
at Google:
://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&safe=off&q=visual+basic+sample+code+update+ODBC+edit&btnG=Google+Search

At Google Groups:
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&safe=off&q=visual+basic+sample+code+update+ODBC+edit&sa=N&tab=wg

visual basic sample code update AS/400 table using ODBC 
At Google:
://www.google.com/search?hl=en&lr=&ie=ISO-8859-1&safe=off&q=visual+basic+sample+code+update+AS%2F400+table+using+ODBC+

At Google Groups:
http://groups.google.com/groups?hl=en&lr=&ie=ISO-8859-1&safe=off&q=visual+basic+sample+code+update+AS%2F400+table+using+ODBC+&sa=N&tab=wg