AddNew or Update with ADO and OLE DB
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) & "')"
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) & "')"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.