Solved

ADO VB and client access express V4R5M0

Posted on 2001-08-27
8
315 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the hope of saving someone else's sanity... About a year ago we bought a Cisco 1921 router with two ADSL/VDSL EHWIC cards to load balance local network traffic over the two broadband lines we have, but we couldn't get the routing to work consi…
When we talk about DevOps toolchains, I sometimes wonder how many people really get what we’re talking about. I don’t know if it’s just semantics or tone or something else, but sometimes I think it just sounds like buzzword sausage. So it’s always …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now