Google Answers Logo
View Question
Q: Visual Basic 5.0 Code ( Answered 3 out of 5 stars,   0 Comments )
Subject: Visual Basic 5.0 Code
Category: Computers > Programming
Asked by: jbarrancos-ga
List Price: $20.00
Posted: 04 Sep 2002 13:35 PDT
Expires: 04 Oct 2002 13:35 PDT
Question ID: 61711
Visual Basic code (using .edit and .update recordset methods) to
update AS/400 table using ODBC
Subject: Re: Visual Basic 5.0 Code
Answered By: wengland-ga on 04 Sep 2002 18:52 PDT
Rated:3 out of 5 stars

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:


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.


Also, Thomas D. Chamberlain ( posts in
comp.lang.basic.visual.3rdparty on 1995/08/01 in message ID
<3vmdn0$> :

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

  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 &
    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.Fields("rma_no") = 0
        RS.Fields("rma_date") = Now
    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:

At Google Groups:

visual basic sample code update AS/400 table using ODBC 
At Google:

At Google Groups:
jbarrancos-ga rated this answer:3 out of 5 stars

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy