By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Inserting into Oracle table from MsAccess using VBA

P: 14
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.
Nov 15 '06 #1
Share this Question
Share on Google+
2 Replies


100+
P: 1,646
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.
Nov 16 '06 #2

P: 14
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.
Nov 16 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.