Solved

ADO VB and client access express V4R5M0

Posted on 2001-08-27
8
321 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

778 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