Solved

ADO VB and client access express V4R5M0

Posted on 2001-08-27
8
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Postmortem reporting allow us to examine mistakes in a way that focuses on the situational aspects of a failure’s mechanism and the decision-making process of individuals proximate to the failure. Read our guide on how to handle IT post-mortem repor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 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