Solved

ADO VB and client access express V4R5M0

Posted on 2001-08-27
8
327 Views
Last Modified: 2008-03-17
I've made a VB program connecting with an AS400 system using client access express v4r5m0 and service pack sf65345. I'm using MDAC2.5 on the PC (a lot of problem to bypass the IBM install bug, but now it works)

I've to manage a lot of tables, and the ADO connection with AS400 works but it's so slow.
I've made SQL queries directly from VB and i've seen the connection is quite quick but the query is very slow.

Another problem is that i can't make the UPDATE vb method or UPDATE query works. It doesn't work if i want to update a record comingh from a SELECT query. Otherwise if i read the entire table and i select ("manually", using if statements) a record, the UPDATE works.

My questions are:
What can i do to speed up the SQL queries?
What can i do to UPDATE a record selected from a SELECT query?

I'm not an AS400 programmer and i don't know this environment. I've onnly made a VB software to manage some of the table stored into the AS400 disks.

To make my programs i've used the IBM examples and wizards but i cant find a real working example with SQL SELECT queries and UPDATE queries.

zakalwe
0
Comment
Question by:Zakalwe
8 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 6428449
For the SELECT update, Check your authorisation by the System operator, possibly you have no update rights to that table.
0
 

Author Comment

by:Zakalwe
ID: 6428472
I think i've the update rights because i can update a recordset but only if read without a SELECT query. If i use a SELECT query i can't use any update method (from vb or SQL)

zakalwe
0
 
LVL 13

Expert Comment

by:samic400
ID: 6429480
I would suggest using stored procedures to return a result set or selecting only the necessary records you wish to see to speed up your data return.

When you open up the recordset, are you using the defaults at open time? Read is the default and you wouldn't be able to update.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Zakalwe
ID: 6431847
I open the connection using:

adodbconnection.Open "Provider=IBMDA400;Data Source=as400name;", "", ""

and

ad400connection.Open AS400name, "", ""

so except the provider i don't use any parameter, then
i open the recordset:

Set adodbcommand.ActiveConnection = adodbconnection    '

adodbcommand.Properties("Updatability") = 7 'this is the open type, change+delete+insert

adodbcommand.CommandText = AS400libraryname & as400TableName & ".FILE(*FIRST, *NONE)"
adodbcommand.Parameters.Append adodbcommand.CreateParameter("P1", adChar, adParamInput, 1)
Set recordset = adodbcommand.Execute(Rcds, Parms, adCmdTable)    ' open recordset


So i think the "Updatability" property should give me the ability to update, or not?

Zakalwe

0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6460685
select queries wont allow updates.  you need to us update statements.

update as400file  set fielda = "test" where yourcondition is met


0
 
LVL 2

Accepted Solution

by:
mmcmillen earned 300 total points
ID: 6460698
As far as speeding up your queries..  I don't know of any way to do that .  maybe narrowing your search criteria. if the data you are after needs to be in a certain order in your recordset, it may be faster to create a logical file on the as400, and query the logical  instead of the physical file and sorting .  Network traffic, network line speed will also play a part in speed.
0
 
LVL 2

Expert Comment

by:mmcmillen
ID: 6463817
Can you explain the C grade?
0
 

Author Comment

by:Zakalwe
ID: 6463847
It's because your solutions, in my opinion, are correct but very general. I've found by myself one of this.
However your answer are useful to me because now i know i'following a correct way.
A lot of thanks
Zakalwe
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
iSeries Library Syncing 3 180
Find Date Difference in AS400 Query 11 534
OPNQRYF with numeric fields 4 272
Create AS400 Logical File 5 90
ConnectWise and their customers need to ensure critical alerts automatically reach the right person at the right time. MSP superheros efficiently respond to these alerts key is providing automatic, intelligent alerting that generates a complete audi…
Riverbed Technology's webinar discusses networking for the cloud era with simplified SD-WAN cloud connectivity.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question