Exporting data from Excel to Oracle

decci_7

New Member
Joined
Jul 17, 2006
Messages
22
Hi,

I have developed one macro which will fetch the data from excel sheet and will upload it to the oracle tables.

Code:
Sub enterdata()

'Create and set login information variables


'Set Variables for Upload
Dim empid As String
Dim name As String
Dim role As String

'Create and Set Session / Create Dynaset = Column Names
Dim Session As Object
Set Session = CreateObject("OracleInProcServer.XOraSession")
Dim OraDatabase As Object
Set OraDatabase = Session.OpenDatabase("decci.7.in.abc.net", "user/password", 0&)
Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM security1", 0&)
    
Range("A2").Select
    Do Until Selection.Value = ""
        empid = Selection.Value
        name = Selection.Offset(0, 1).Value
        role = Selection.Offset(0, 2).Value
        Oradynaset.AddNew
        Oradynaset.fields("empid").Value = empid
        Oradynaset.fields("name").Value = name
        Oradynaset.fields("role").Value = role
        Oradynaset.Update
        Selection.Offset(1, 0).Select
    Loop
End Sub

The data uploading is done as required. But now i have an additional requirement. I want to check in the table before entering whether the employee id i'm entering already exists in the table. If it does then i want to overwrite that record with the latest one from the excel sheet. Does anyone know how to accomplish that?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top