-
What kind of select should I be doing to the database when all I want to do is insert a record?
I am using "select * from tablename". And it is slow I assume because it has to bring back so many records.
I am doing this:
What should I be doing?
stGetRecordSet = "SELECT * FROM MCM_INBOUND_TRAN_TABLE"
If DestinationRS.State = adStateOpen Then
DestinationRS.Close
End If
With DestinationRS
.CursorLocation = adUseClient
.Open stGetRecordSet, Conn, adOpenForwardOnly,
adLockBatchOptimistic, adCmdText
End With
If DestinationRS.State = adStateOpen Then
stGetRecordSet = "INSERT INTO MCM_INBOUND_TRAN_TABLE
(INBOUND_TRAN_TABLE_SEQ_NBR, ORIGINATION_SYSTEM,
DESTINATION_SYSTEM, LENGTH) VALUES
(INBOUND_TRAN_TABLE_SEQ_NBR.NEXTVAL"
& ", 'RMCS', 'CDB', " & (Len(stListMessage) - j) & ")"
Conn.Execute (stGetRecordSet)
End If
-
In the case of inserting a record into table using oracle, you don't have to select the table prior to inserting. So, I guess it is best for you to take out the portion that you select * from tablename and instead, straight away use the "Insert" command.
That should reduce your accessing time and also should increase your performance a bit.
-
Use the Execute command when you want to execute a SQL statement without having to return a recordset.
Dim cn As New ADODB.Connection
Dim cm As New ADODB.Command
'-----------------------------------
'open Oracle database connection
'-----------------------------------
cn.Open ....
With cm
.ActiveConnection = cn
.CommandText = "INSERT INTO MCM_INBOUND_TRAN_TABLE INBOUND_TRAN_TABLE_SEQ_NBR, ORIGINATION_SYSTEM, DESTINATION_SYSTEM, LENGTH) VALUES INBOUND_TRAN_TABLE_SEQ_NBR.NEXTVAL" & ", 'RMCS', 'CDB', " & (Len(stListMessage) - j) & ")"
.Execute
End With
cn.Close
