Solved

AddNew or Update with ADO and OLE DB

Posted on 2001-08-27
2
895 Views
Last Modified: 2008-02-20
I'm trying to write records on the AS/400. The code to write to the 400 is at
the end of this message. My problem is that I keep getting the message that the
AddNew method is not supported. I am using Visual Basic 6, Service Pack 5.
Is there something I'm missing?

Project References include:
Visual Basic For Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
IBM AS/400 Client Access Express ActiveX Object Library
Microsoft ActiveX Data Objects 2.5 Library

Components: none beyond the standard set.

CODE: ************************************************************************
Dim strSQL                  As String
Dim cnnAS400                As New ADODB.Connection
Dim cmdAS400                As New ADODB.Command
Dim rsAS400                 As ADODB.Recordset
cnnAS400.Mode = adModeReadWrite
cnnAS400.Open "Provider=IBMDA400;Data Source=AERIS10;"
Set cmdAS400.ActiveConnection = cnnAS400
strSQL = Select * From CO88PX.TIME"
cmdAS400.CommandText = strSQL
cmdAS400.CommandType = adCmdText
Set rsAS400 = cmdAS400.Execute
Dim rsAS400
rsAS400.AddNew              'This is the statement that fails
rsAS400!TCEFYR = Year(Date)
rsAS400!TCEFMN = Month(Date)
rsAS400!TCEFDY = Day(Date)
rsAS400!TCEFHR = Hour(Date)
rsAS400!TCEFMIN = Minute(Date)
rsAS400!TCEFSEC = Second(Date)
rsAS400.Update

END CODE *********************************************************************

PS: I am currently using the following as a work around, but it is quite slow

strSQL = "Insert Into CO88PX.TIME " & _
"(TCEFYR, TCEFMN, TCEFDY, TCEFHR, TCEFMIN, TCEFSEC) " & _
"VALUES (" & _
Year(Date) & ", " & _
Month(Date) & ", " & _
Day(Date) & ", " & _
Hour(Date) & ", " & _
Minute(Date) & ", " & _
Second(Date) & "')"
0
Comment
Question by:bhlang
2 Comments
 
LVL 13

Accepted Solution

by:
samic400 earned 50 total points
Comment Utility
I may be wrong because you have defined the connection as read/write but I've gotten this type of message before because my recordset is open as read only.

Try this:

Dim cnnAS400                As New ADODB.Connection
Dim rsAS400                 As new ADODB.Recordset

cnnAS400.Open "Provider=IBMDA400;Data Source=AERIS10;"
Set rsAS400.ActiveConnection = cnnAS400
rsAS400.open "time", 3, 3

rsAS400!TCEFYR = Year(Date)
rsAS400!TCEFMN = Month(Date)
rsAS400!TCEFDY = Day(Date)
rsAS400!TCEFHR = Hour(Date)
rsAS400!TCEFMIN = Minute(Date)
rsAS400!TCEFSEC = Second(Date)
rsAS400.addnew

This is the way I do it. I'm sure there are other ways, but this is just a suggestion.

Good luck..... hope it helps out somehow.

0
 

Author Comment

by:bhlang
Comment Utility
Thanks for your help. It didn't really help me,  but since you were the only one who did help me, I'll give you the points.

The SQL Insert Into method I was using was WAY too slow. So I abandoned it.

Next I tried to use a temporary Microsoft Access Database on the PC and transfer the whole table using an SQL Insert Into statement but it wouldn't work the way I wanted it to.

Finally, I tried this:

I created a TextStream object to let me write to a file. Then I read the records from my source database and wrote them out as a simple string of text (one line per record). That file was copied to the AS/400's QDLS directory where it could be picked up by any AS/400 program. This was significantly faster than the SQL Insert Into method I had been using. I could run the Text File route at least 5 times in the same time that the SQL Insert Into method would transfer the data to the AS/400.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

10 Experts available now in Live!

Get 1:1 Help Now