Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

OS400 Commands On AS400 Via VB

I need to run some OS400 (V5R1) commands on an AS400 and have the results returned to a VB program. I don't know if this is possible. I can connect to the AS400 no problem, but I want to send some OS400 commands after that. eg. I want to issue the command WRKACTJOB and then have the active jobs returned to a VB interface, or WRKJOBQ and have the queued jobs returned.

Any help would be appreciated.

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
VB Application Sending Commands To AS400 Client
Hi All,

I have a VB application that connects to the IBM I-Series client and
sends some keystrokes.

I am connecting using PCOMM with the following code:

CODEDim autECLSession As AutConnList
Dim autECLPS As AutPS
varSerial = frmMain.txtSerial1
Set autECLConnList = CreateObject("pcomm.autECLConnlist")
Set autECLPS = CreateObject("pcomm.autECLPS")


'Connecting to Session A

autECLPS.SetConnectionByName ("A")

autECLPS.SendKeys "[FLDEXT]"
autECLPS.SendKeys "1" & "[ENTER]"
autECLPS.SendKeys "1" & "[ENTER]"
autECLPS.SendKeys "STK" & "[ENTER]"
autECLPS.SendKeys varSerial & "[ENTER]"

Vb Commands For Executing System Commands Needed
I would like to be able to execute dos commands, or start othe r programs from my programs but don't know the vb commands for doing it. In C the command is system(); is there an equivelant in basic

does anyone knows if is possible to use VB 6.0 with a as400 database?using ADODB.

Just anyone know where I can get a OLEDB Provider driver for AS400 like the one Microsoft has for Oracle & SQL Server? The only way I know of linking now is to create a linked Access table and use OLE Provider for OBDC Drivers.

Vb To As400
does anyone know how to write to a specific member of an as400 file thru vb using select statements? i can write to a file using:

Set rsGrade = New ADODB.Recordset

sSqlG = "select * from BPCSF.IIM where IPROD = '" & sprod & "'"
rsGrade.Open sSqlG, m_cnAS400

but now i need to write to a specific member of the IIM file??

VB To AS400
Hey guys can someout there help me. I am using VB to read and write to files on our AS400 system. I am using the following code to get a recordset:

Dim rs As New ADODB.Recordset
Dim sSql

Set rs = New ADODB.Recordset

sSql = "select * from ECDX where CXCONS =" & Trim(frmMain.Text1(index).Text)
rs.Open sSql, m_cnAS400, adOpenDynamic, adLockOptimistic

This is working fine but i also make changes to alot of the fields either entering data in blank fields or altering existing data by the text boxes on the form. I think the best way to do this is using a sql update statement but am not quite sure from this point how i would do this. do i have to set up another rs? please advise. thanks in advance.

Does anyone have any problems with vb 6.0
trying to connect to an as/400.

I am having problems summing a total.

err. sql0666 - estimated query processing time 500
exceedes limit 30.

This might not be an as/400 problem, just an odbc one.

Any help would be great. Thanks

ADO With AS400
I am connecting to an AS400 using ADO and a ODBC driver. The connection works fine I also can open tables and view the first record. But I cannot navigate through the recordset. why?? My code is basically the same as connecting to access or SQL server and I can navigate with that..also I tryed using a datagrid to view records and it will only show me the first record..why??

any help would be appreciated


VB To AS400?
I need to write a program that pulls a file from the pc (Infinium) and have it update a file on the 400. What is the best way to automate the import from the pc to the 400? Or where is a good website to go to find out more information on this subject?


I m tring to retrieve records from DB2/as400,But the process seems to be tooooo slow.

I have client access v4r4 sl-5F60796

I m tring out the simplest query possible.
Ihave a ADODC controll which connects to DB2/as4oo using DSN(ODBC) and i m using Datagrid to view the records.

The table i m trying to connect has only 20 columns and around 300 rows.But it takes approx. 5min. to get this information.

I bet there has to be another faster way to do this.

I m not a as400 guy but a vb one.I don't know where i m going wrong.

Can any one help?

AS400 Connection
Hi VB Gurus,

i'm an AS400 programmer and quite new in using VB,
i saw one code that connects to AS400 and queries DB2/400
files depending on the selection criteria passed. Now comes
my problem, i tried it and to my dismay failed hehehe. Any ideas
would be helpful, thanks in advance.

The error begins when i try to define EnvRDo as rdoEnvironment.
Error message is "User-defined type not defined"

Here's the code :

Option Explicit
Dim EnvRDO As rdoEnvironment
Dim ConRDO As rdoConnection
Dim rs As rdoResultset
Dim er As rdoError
Dim qd As New rdoQuery
Dim sBeginTime!
Dim sResultTime!

Private Sub WriteRDOError()
Dim X As Integer
Dim sDate As String
Dim sTime As String
sDate = Format(Date, "")
sTime = Format(Time, "")
X = FreeFile
Open App.Path + "Error.log" For Append As #X
Dim er As rdoError
Print #X, "Program drive time: " + sDate + " " + sTime
Print #X, "************************************************"

For Each er In rdoErrors
Print #X, "Error: " & er.Description
Print #X, "************************************************"
Close #X
End Sub

Private Sub Cmd_List_Click()

On Error GoTo ListError
Call sStart
Exit Sub

Label_Info.Caption = "Error, check the error log!"
End Sub

Private Sub Form_Load()
optField1.Value = True
On Error GoTo LoadError
Screen.MousePointer = vbHourglass


'Environment allocation
Set EnvRDO = rdoEngine.rdoEnvironments(0)
Label_Info.Caption = "Connecting to AS400..."


'Connection allocation
Set ConRDO = EnvRDO.OpenConnection(DsName:="", Prompt:=rdDriverNoPrompt, Connect:="uid=;pwd=;driver={Client Access ODBC Driver (32-bit)};system=S22ABC2;database=TESTDB.LIBRARY;")

Label_Info.Caption = "Connection ok!"
Screen.MousePointer = vbDefault
Exit Sub


End Sub

Private Sub Form_Unload(Cancel As Integer)
Dim result%
Set rs = Nothing
Set ConRDO = Nothing
Set EnvRDO = Nothing
End Sub

Private Sub mnuLopeta_Click()
Unload Me
End Sub

Sub sStart()
sBeginTime! = Timer
Set qd.ActiveConnection = ConRDO
SQL = ""

'Search rules
If optField1.Value = True Then
SQL = "select FIELD1, FIELD2, FIELD3 from TESTDB.LIBRARY where FIELD1 = ?"

If optField2.Value = True Then
SQL = "select FIELD1, FIELD2, FIELD3 from TESTDB.LIBRARY where FIELD2 = ?"
SQL = "select FIELD1, FIELD2, FIELD3 from TESTDB.LIBRARY where FIELD3 like ?"
End If
End If

qd.SQL = SQL
qd(0) = Text1.Text

'Filling the list:
Set rs = qd.OpenResultset(rdOpenForwardOnly, rdConcurReadOnly)

Do Until rs.EOF
List1.AddItem rs!FILED1 + " " + rs!FIELD2 + " " + rs!FIELD3

sResultTime! = Timer - sBeginTime!
Label_Info.Caption = "Selection took: " & Format(sResultTime!, "###0.00") & " seconds"
End Sub

Set Up AS400 DSN On Windows 95 For VB6 To Use
Can someone please explain how to set up an AS400 DSN on my PC ? I am running Windows 95 and have Client Access Express V4 R4 installed.

I am only a beginner, therefore I am trying the easiest things first.

I would like to create an easy VB program to display a physical file on the AS400.

I would like to put the DAO data control on a form and point the DatabaseName property to the DSN. From there I believe I should be able to set my RecordSource property of the data control to a physical file on the AS400.

Also, please correct me if I am wrong.

Thank you for all your help.


Links To AS400???
hello there,
does anyone know how I would go about linking into AS400 platform to communicate with an old database program called Mapics???

the company currently uses IBM client access tools for communication and queries etc.

Any hints for links with VB???


"Smoke me a kipper I'll be back for breakfast!"

AS400 Connection
I would like to make an ODBC connection to an AS400 if that is possible. I am trying to create a connection so I can create a data report. Does anyone know a good way to create this connection?

VB6 Problem With AS400

i'm developing a vb6 program that connect with AS400. My VB6 program generate steps to automatically input the data into AS400 program with the help of IBM PC5250 DDE interface. My VB6 program reads data from SQL Server 2000 database.

So far, there is no problem when there is one to one connection (one vb program to one session of as400 program). But the problems occured when there are more than one connection.

I need to run several vb6 programs with several AS400 programs.

may be anyone who has experience with connecting vb to AS400 can help me?
im desperately need help coz the deadline is within this month.


I have a program written in VB Script which observes data and gives commands to AS400.
It works perfectly, as far as I'm concerned---however, when I run the script while the program is giving me its error response, it locks up.
The error response I'm talking about is when a command is given which is not valid---such as page down when the user is already at the bottom---a red
"x II" appears in the bottom left of the screen. If I run my script while that red X is there, the program locks up and I have to alt-F4 out of it.
Anyone know why or what I can do?

Urgent DB2 && AS400
Hi, Im accessing an AS400 database. I can connect the the AS400 fine using this connection string...

Set dbConnect = New ADODB.Connection
dbConnect.ConnectionString = "Provider=msdasql;Driver=CWBODBC;Data Source=System3;Prompt=3;"

after I connect, I dont know how to access the database that I want. What is the proper syntax for accessing databases in this manner?

Thanks alot!

AppendChunk And AS400
I have problems for save BLOB fields in an AS400 DB2 database with the AppendChunk method.

Inet And As400
I have to download a file from as400 to pc and I need that file in ascii format. On as/400 my file has no ext (txt or other) and the transfer performs always in Bin mode. There's a way to Bypass that problem?


Running VB Exe From An AS400
Does anyone have experience of running a VB program from an AS400 ?

I'm using the STRPCCMD (start pc command), to run the Exe.- have Client
Access version 4.3. The program doesn't have a form, just a module. The
program runs ok, but the AS400 is waiting for something to happen before the
command finishes. It looks like the AS400 doesn't know that the VB program
has finished, and something needs to be returned.

Any ideas very welcome

***** DISCLAIMER ***** This communication contains information which is
confidential. It is for the exclusive use of the intended recipient. If you
are not the intended recipient, please note that any distribution, copying
or use of this communication or the information in it is prohibited. If you
have received this communication in error, please notify us by email or by
telephone on 0151 653 1700 and then delete the email and any copies of it.

AS400 Connection
Thank you very much to Mr. Bromberg for your last answer.

I need a connection to AS400-DB2 with ADO. Now I have the connection, but only read.
What is wrong in the following statements?
"adLockOptimistic" and " "Properties("Updatability")=7" don´t work
I have the VB6 Professional Edition and Client Access Express

Option Explicit

'Declare and instantiate the Connection object
Public conHELDAT As New ADODB.Connection

'declare and instantiate the Command object
Public cmd_FEKSTS0 As New ADODB.Command

'declare the Recordset object
Public rst_FEKSTS0 As ADODB.Recordset

Private Sub Form_Load()
conHELDAT.Open "Provider=IBMDA400;Data Source=AS400;", "", ""
Set cmd_FEKSTS0.ActiveConnection = conHELDAT

cmd_FEKSTS0.Properties(16) = 7
'cmd_FEKSTS0.Properties("Updatability") = 7
cmd_FEKSTS0.CommandType = adCmdText

'Set rst_FEKSTS0 = New ADODB.Recordset
'rst_FEKSTS0.LockType = adLockOptimistic

Set rst_FEKSTS0 = cmd_FEKSTS0.Execute

txtAnzeige.Text = rst_FEKSTS0.Fields!KSBEZ

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set rst_FEKSTS0 = Nothing
Set cmd_FEKSTS0 = Nothing
If Not conHELDAT Is Nothing Then conHELDAT.Close
End Sub

AS400 Connection With ADO
I want with VB6.0 and ADO a connection to a IBM AS400 - Database.
The following statements create an runtime-error.
Dim conn As New Connection
Dim cmd As New ADODB.Command
Dim rec As New ADODB.Recordset

conn.ConnectionTimeout = 30
conn.Open "Provider=IBM AS400 OLE DB Provider;Data Source=heldat;" & _
"User ID=id;Password=pw;SSL=default;Transport Product=Client Access”
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
Set rec = cmd.Execute

The execute-command create this error:
Runtime-error: 2147467259 (80004005)
CWBDB0001 – Verbindungskennung fehlgeschlagen beim Versuch,
Server anzusprechen (System HELDAT)

AS400 Download Using VBA
I was wondering if anyone has more experience with this. Basically I want to download files from AS400 to Excel or Access using VBA code and not use a DSN file because those excel files will be shared and I do not want to have the DSN installed on each user.
I read about ADO but I wasn't able to get more on it.
Thank you.

Getting A Param From AS400 To My VB App
Hello everyone,
I have an simple vb6 application that the user enters an order number and it prints a report. To avoid extra steps and make it simpler, I was asked the following:

-Users will enter the order number from the AS400, as soon as they save it, a command will call my VB program and will pass the order number.
-My part is: to get the order number passed and execute the vb program.

I have to change my program to Visual basic Module because we don't need the interface anymore but
my question is: how do I get the order number coming from an AS400 application?

Thanks for your help


AS400, VB6 And ADO Question

I'm clueless with the AS400. I want to write a VB app, which passes a parm and triggers an app to run on the AS400, maybe source a file that it creates.

So my questions are:

Is there an OLEDB ADO provider I can use?
Is there added software I need on the PC? (Client Access)

Anything would be greatly appreciated. Just need to be pointed in the right direction and I'll chase it down like a junkyard dog.

Retrieve From AS400

Can anyone give me a code to retrieve data from AS400 option 19.

Thanks and Regards

Import To AS400
I am new to VB. I have a file that was imported into an excel spreadsheet. I have imported that into Access. What do I need to do to take that information and import it into a file on the AS/400. I know I will be using ODBC.
Any help would be much appreciated!

Connection Vb6 / AS400

I needed to do a select in VB6, the some files that are inside of the Biblioteca " MANUT2 " in AS400.
Password: XXX
NOTE: In the company we used Client Access to emulate AS400

1. connection Vb6 / AS400
I needed to know how it is that make a connection ODBC (in code) to the Biblioteca " MANUT2 " without having to create ODBC in the Panel of Control.

NOTE: In case they can send me the code or a small example, I thanked.


Linking To As400
I've imported and linked a table on the as400 that has a date field as a decimal data type to my Access Database. I would like to be able to sort by this field but I can't. Does anyone know how to create a new field based off the decimal data field that I can sort off of. I need this to be automatic so that I always have up to date information from the AS400. If anyone has done this or has a better idea please let me know. This is a first for me!

Thank-you for your help!


I try to connect through ODBC a NT machine and an AS400 (Client Access Express V4R5). It works fine but the problem I got is whenever I do the connection for the first time after booting the NT, although on the connect string I have User and Password, it promts the login box from Client Access. How should I set up the DSN (or the Client Access) so that I dont get the login box?
Thanks in advance.

Sendkey With As400 Session?
I'm writing a macro for an as400 TN5250 Session. The sendkeys works when I'm inputing numbers but when I try to TAB or PgDn with sendkey nothing happens. How can I do this on a AS400 session?? Below is a portion of the script.

Set oWshShell = WScript.CreateObject ("WScript.Shell")
For Each Item in arrFile
If IsNumeric(arrFile(a)) = TRUE Then
If i = "10" Then ' Test for PageDown.
oWshShell.SendKeys "{PgDn}"
WScript.Sleep 800 ' Delay allows PgDwn.
End If
oWshShell.SendKeys arrFile(a)
oWshShell.SendKeys "{TAB}"
'oWshShell.SendKeys "{ENTER}"
i=i+1 'Increment

End If
a=a+1 'Increment
End If

VB.NET Connection With AS400 Problem
Hi there,
I have downloaded a VB.NET sample program which connects with the as400 server. The only problem is I cant find the "IBM.Data.DB2.iSeries" import reference . Please help me if u know from where i can download this.

The sample progam is attached with this message

I am using visual studio 2003 version


TextBox And AS400 Database
Hi! I'm using a couple of textboxes, in which I set the max lenght the user can type in to 50. I have also set the database in as400 to store 50 charachters.

My problem is if user does not enter any data and saves his record and then want to edit his data. The data retrieve from the database will appear in the textbox as 25 spaces instead of just blanks. Which means the user can only type a few words before he is locked out as the spaces would be taking up the space in the textbox! Can anyone advise? Thanks

How To Retrieve Data From AS400?
Dear ppl... Im an intern and recently my boss ask mi to work on a vb project... This is my first time using AS400 wit Vb and i have no idea wat to do....
I did manage to setup e ODBC(took mi a day to understand) and managed to retrieve e data from e AS400 server and its now in access format... But can any1 enlighten mi or show mi sample codes as to how to retrieve e data from AS400 and display it in vb? My main problem is wit e connection strings and i have no idea wat i need to type in...

Dim strConn As String, strSQL As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strConn = "User Id=Dian; Data Source=AS400_Test; DATABASE=SISTFL12T; Password=dian9" & cdgDataBase.FileName
strSQL = "select * from SIBRND"
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = strConn

The above is wat i did so far but dun think its e right codes... Can any 1 help mi out thank u bery bery much!

Advice On Connecting To An AS400!
Ok I am battling with a fellow programmer as to what is the best method to use for using VB to connect to an IBM AS400 either ODBC vs a DPC (which is just usually a module of declarations to define navigation variables and such in an AS400). What do you use or recommend to connect to an AS400 with VB? Any advice is much appreciated!!

VB6 And AS400 Client Access

Thanks for any advice in advance.

I've just developed a program using a System DSN pointing to my AS400, when I run it on my PC it works fine, when I try to run it on 2 other XP PC's, it fails with:-

Provider cannot be found. It may not be properly installed.


Run time error "3706" application-defined or object defined error

I have hardcoded the ADODB component to my DSN "CSFP" and hard coded the username/password.

When I use the DSN "CSFP" in Microsoft Excel on 1 of the machines in question, I can get all the data out of the table I am wanting to extract data, so the ODBC part seems okay.

Can anyone help in fixing this issue?


AS400, Crystal & VB Questions?
Im trying to use DAO in VB6.  I want to retrieve a selected recordset via SQL from the AS400 and then run a Crystal report over this recordset.  Do I create a Workspace and Do an OpenConnection in the VB or does the Crystal.Connect do this for me?  We have extremely large files and the Crystal just doesn't like going against them directly for a select.  I'm trying to decrease the number of records that Crystal has to deal with.  I also am trying to get to a completely automated report setup that I can give to my users.  Any Ideas?

Update Db2 In AS400 ** Resolved **
hii, help me.
I want to update my table in db2 database at AS400 Server.
This is my syntax :
     Update A_table set field1="S" from A_table,B_table where A_table.code_id=B_table.code_id and A_table.status="2" and B_table.field1=""

If I run in MSQL Server 7 not problem but in db2, I get error ? Why ?

plz, Help me

Edited by - Shandy on 1/25/2004 10:51:29 PM

Client Acces For AS400
At my work we are using client access express for Windows to the AS400. It's pretty simple to use....when you turn it on - it brings up a simple logon - type username, type password...then it brings up a menu. etc so on so forth.

Here's what I want to do. I'd like to have a VB app that can automate that setup. Like i click a button in a form, and VB automaticaly type in username, the appropriate menus...etc. Is this type of automatic integration possible?

Access To AS400 Database Via ADO
Hi, I have a problem accessing my IBM AS400 by Visual Basic application with ADO
This is my connection string:
  Dim ADOConnection as ADODB.Connection
  Set ADOConnection = New ADODB.Connection
  ADOConnection.Open "Driver={Client Access ODBC Driver (32-bit)};" & "System=mySystem;" & "Uid=myUser;" & "Pwd=myPassword;"
  Dim RS As ADODB.Recordset
  Set RS = New ADODB.Recordset
  RS.Open "SELECT * FROM COMMESSE.ARCHIVI", ADOConnection, adOpenDynamic, adLockOptimistic

When I try to open recordset, recive and error number -2147467259, undefined error.

Help please

DAO And String Connection With AS400(DB2).
Please, i would like to know the precise string connection with AS400(DB2).

i need the exact string and examples with all sql commands.

Thanks in advance.

Counting Records In AS400
I'm doing a query over an AS400 table and I'd like to know how many records are in the query. I've already tried using the Recordset property "RecordCount" and It doesn't work. It returns -1.
The VB code I'm using go like this:

Dim xCnnAS400 as new Connection
Dim xSQL as string, xCnnStr as string
Dim xRec as new Recordset, xRecSelected as long

xCnnStr = "Provider=IBMDA400;User ID=UserName;Data Source=SourceName;Transport Product=Client Access;SSL=DEFAULT"

xCnnAS400.Open xCnnStr
xSQL = "Select field1, field2 from Table where Cond1 and Cond2"
set xRec = xCnnAS400.Execute(xSQL)
xRecSelected = xRec.RecordCount ' <-- NOT WORKING

I know I could use "select count(*) from table" before the query, but I think that would be inefficient if the query result is big. Is there a better way to do it?

AS400 Gestion Des Travaux
how to verify if an AS/400 job is completed with VB code only.
I can connect to the AS/400 and execute a command/job, but I cannot access to the job handle window.

I use CWBX.dll to connect and execute the command.

Someone can help me ???

Help Sending Data From Excel To AS400
Hi All,

Hope someone out there can help me. I've written lots of macros that work inside Excel and Access, but this is the first time I've wanted to do something outside them. Here is my problem:

Essentially, I'm trying to automate data entry. I'd like to open a particular Excel spreadsheet, save the data on sheet 1 into an array, then transfer this data one cell at a time into our AS400 emulator. I was planning on doing a sendkeys type operation to send the data. As far as my AS400 emulator would know, I'm just a fast typist. I've found that I can't run this from inside Excel, because it doesn't send the sendkeys until after VB finishes executing. That would overrun my AS400 emulator buffer. So running it from inside Excel appears to be out as a solution.

I've looked at some scripting programs like Macro Scheduler 7.1 to help, but I haven't done any VB working from "outside" Excel. Has anyone out there done anything like this, or know where any sample code for a similar project could be found?

Thanks in advance for helping a newbie!


Lock Status Of As400 File Using Vb6
I am trying to determine if a file is locked on the 400. I am using client access and the dll cwbx. I can run the commands to lock the file and then log into the 400 and view lock. I cannot seem to find a way to determine if the file is locked from vb. I thought that if the file was already locked and I tried to lock it again I would get an error but the 400 doesnt care. I also try to wrkobjlck in vb and either way I get a return code of 0. I have seen some stuff to read a dataque but everything that I am ready is for programs that are being run on the 400 and not just single commands.

any help please.

AS400 PC5250 Session Component

I have inserted an AS400 PC5250 session component in my VB form, but the problem I am having is that on form load I have to double click on the component to activate it, also, when it loses focus it became inactivated again (I have to double click again).

Is there anyone who can help me with that ? I would like the AS400 session to remain activated all the time.

Also, what are the properties and methods that I can use with it ?

I thank anyone who helps me with that in advance.


Optimizing Data Transfer From AS400
I have created an application which transfers data from a file on an AS400 to a MSSql Server. My problem is, that it is very slow. The app seems to transfer the whole recordset to the client machine before it starts processing it.

I think there will be a faster way to do the data transfer, but i cannot figure out how. My code which makes the data transfer looks like this:

Dim sSql As String
Dim Rs As ADODB.Recordset

On Error GoTo fejl

'open connection to as400

'create recordset from as400
sSql = "SELECT * FROM " & Mid(sFilename, 8)
Set Rs = deConsumer.conAS400.Execute(sSql)

'enumerate each record in the recordset and insert it into the sql server
Do While Rs.EOF = False
sSql = "INSERT INTO import VALUES ("
For i = 0 To Rs.Fields.Count - 1
If (i > 0) Then sSql = sSql & ", "
sSql = sSql & "'" & Replace(RTrim(Rs.Fields(i)), "'", "") & "'"
Next i

sSql = sSql & ")"
deConsumer.conSql.Execute sSql


Exit Sub

modStatus.writeStatusline sSql

I hope somebody can help me with this problem...

ADO As400/SQL Query String Too Long?
hi all, i'm stymied, so i do hope someone else has encountered this.

i am extracting data from an SQL Server db and inserting into a file on an as400 (iSeries!!) using ADO. when i run my app from within the IDE, i get the following error: "Method 'Open' of object '_Recordset' failed" --similar error if i use the conn.excute() method. however, i've created a test file on the same db with the same permissions and fewer columns and can update using the same methods...i've also copied the results of my query string from the immediate window and run it in the iSeries Navigator query analyzer and it successfully inserts the record.

has anyone else encountered this behavior? the table on the as400 has close to 90 columns--if it's a length issue, how would you break up the insertion when none of the columns allow NULLs? i'm desperately trying to avoid writing a CL program or COBOL!


okay, you guys are brilliant, i trust you...

thanks in advance and let me know if you want to see code!

AS400 Converting Data Type
Hi, I'm working on a program for service point of my company,
with this program the user insert a wrong code number of 7 digit and the program match, in a table with 128.000 records, the nearest 12/15 records similar to the code that user has been inserted.

The query I lauch against the mdb archive work on field of name: "CODE" and is a combination of OR operators to check digit by digit.
The field CODE is numeric type of 8, here the sql string:
(Note: D1, D2 and so on, are the signle digit example "4980405"
D1 D2 D3 D4 D5 D6 D7 relative to "4 9 8 0 4 0 5"
WHERE CODE LIKE "*" & D3 & D4 & D5 & D6 & D7
OR CODE LIKE D1 & D2 & D3 & D4 & D5 & "*"
OR CODE LIKE D1 & D2 & D3 & D4 & D5 & D6 & D7
OR CODE LIKE D1 & "*" & D4 & D5 & D6 & D7
OR CODE LIKE D1 & D2 & D3 & "*" & D6 & D7
OR CODE LIKE D1 & D2 & D3 & D4 & "*" & D7
OR CODE LIKE D1 & D2 & "*" & D5 & D6 & D7
The program work fine on mdb but I need launch the query on AS400
so I made the same program but for AS400.

The AS400 version work also but non so exactly like the mdb version

The only difference is that field "CODE" in the mdb archive is numeric, instead
in AS400 field "CODE" is char.

Maybe I must convert AS400 char data_type to numeric data_type along the sql istruction ? if so how I can do this ? (CONVERT / CAST) ???

Note to connect to AS400 I'm using OLEDB IBMDA400 provider.

May somebody give me a idea to solve the problem ? Tank you in advance !


Copyright 2005-08, All rights reserved