Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO VB and client access express V4R5M0

Posted on 2001-08-27
8
Medium Priority
?
340 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 600 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
In this article, I’ll show how research, determination, and use of modern technology helped me solve a DNA mystery.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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