Inserting into Oracle table from MsAccess using VBA

[PraveenKadkol]

I have 25000 Rows, that needs to be inserted into Oralce table from MsAccess
When i run below code, i am not getting any error message, Still records are not inserting into destination table.

I am not sure what things i am missing in my code.

Dim objConn As ADODB.Connection
Dim objSourceRs As ADODB.Recordset

Dim objFld As ADODB.Field
Dim strSQL As String
Dim objDt As Date
On Error GoTo Err_Execute



objDt = Forms!frm_IFRS_Main!txt_Cobdate.Value '---> Date passing From MsAccess form


strSQL = "INSERT INTO EUSADMIN_IFRS_FXSPOT2 (FXSPOT_DATE, CCY, CONV_RATE, COUNTRY_ENTITY, CURRENCY, ADP_CODE, ADP_SYMBOL) SELECT '" & objDt & "', CCY, ConV_Rate, Country_Entity, Currency, ADP_Code , ADP_Symbol FROM FxRate"

Set objConn = New ADODB.Connection
Set objSourceRs = New ADODB.Recordset
objConn.CommandTimeout = 10000

objConn.Open "dsn=caldev2.world", "eusadmin", "eusadmin#06"
MsgBox ("database connected")
On Error Resume Next

objConn.Execute (strSQL)


I would appreciate if you guide me.

[willakawill]

I have 25000 Rows, that needs to be inserted into Oralce table from MsAccess
When i run below code, i am not getting any error message, Still records are not inserting into destination table.

I am not sure what things i am missing in my code.

Dim objConn As ADODB.Connection
Dim objSourceRs As ADODB.Recordset

Dim objFld As ADODB.Field
Dim strSQL As String
Dim objDt As Date
On Error GoTo Err_Execute



objDt = Forms!frm_IFRS_Main!txt_Cobdate.Value '---> Date passing From MsAccess form


strSQL = "INSERT INTO EUSADMIN_IFRS_FXSPOT2 (FXSPOT_DATE, CCY, CONV_RATE, COUNTRY_ENTITY, CURRENCY, ADP_CODE, ADP_SYMBOL) SELECT '" & objDt & "', CCY, ConV_Rate, Country_Entity, Currency, ADP_Code , ADP_Symbol FROM FxRate"

Set objConn = New ADODB.Connection
Set objSourceRs = New ADODB.Recordset
objConn.CommandTimeout = 10000

objConn.Open "dsn=caldev2.world", "eusadmin", "eusadmin#06"
MsgBox ("database connected")
On Error Resume Next

objConn.Execute (strSQL)


I would appreciate if you guide me.

Hi. objDT is not a field in FxRate so cannot be selected as you have written.

[PraveenKadkol]

Hi,

So if i want to insert COB date which user enter in form along with other details, then how to pass the string into query?
I have made this change to exisiting query to_date(" & objDt & "',"MM/DD/YYYY"),

Is there any way to insert all the records in one shot? currently i am able to insert into oracle table by each recordset passing to string in Loop.

Please advise.