Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Macro updating database

Status
Not open for further replies.

teja1234

Programmer
May 25, 2010
8
IN
Hi All,

I am new to macro also to this forum, previously i asked two questions but i didn't got proper reply from any one. i am hopeing this time u guys can help me.

I wrote a macro which will load excel sheet data into oracle database every thing is fine but when i run macro each time data from excel sheet updating into database, but i want only change or new rows came in to be updated. Can any one help in this.
i am using Oradynaset.Update to update


Please let me know if i am wrong

Thanks
Teja
 
I wrote a macro which will load excel sheet data into oracle database
Which code ?

BTW, what is Oradynaset ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Thanks for quick reply,
Here is a code
Sub AppendOracleTable()

'Create and set login information variables
Dim logon As String
Let logon = "scott"
Dim password As String
Let password = "tiger"

'Set Variables for Upload
Dim Cust_id As String
Dim Cust_Name As String
Dim Product As String
Dim Order_id As String

Dim Wkb2 As Workbook

' open the workbook you are copying from and activate it
Set Wkb2 = Workbooks.Open(Filename:="C:\Documents and Settings\admin\My Documents\Book2.xls")
Wkb2.Activate

'Create and Set Session / Create Dynaset = Column Names
Dim OraSession As Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Dim Host_name As String
Host_name = "orcl"
Dim OraDatabase As Object
Set OraDatabase = OraSession.OpenDatabase("" & Host_name & "", "" & logon & " / " & password & "", 0&)
Dim Oradynaset As Object
Set Oradynaset = OraDatabase.DBCreateDynaset("SELECT * FROM Test2", 0&)

Range("A2").Select
Do Until Selection.Value = ""
Cust_id = Selection.Value
Cust_Name = Selection.Offset(0, 1).Value
Product = Selection.Offset(0, 2).Value
Order_id = Selection.Offset(0, 3).Value
Oradynaset.AddNew
Oradynaset.Fields("Cust_id").Value = Cust_id
Oradynaset.Fields("Cust_Name").Value = Cust_Name
Oradynaset.Fields("Product").Value = Product
Oradynaset.Fields("Order_id").Value = Order_id
Oradynaset.Update
Selection.Offset(1, 0).Select
Loop
Range("A1:D10").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("D15").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\admin\My Documents\Book4.xls", FileFormat:= _
xlNormal, password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close
ActiveWindow.Close
End Sub

Thanks
Tejs
 
Not sure, but I would think that to do things the way you're wanting to do it, it'd be best running the process from the receiving end - Oracle. Then you could, I'm assuming as I've never directly used Oracle, create a query that looks for any differences between the data source and your current data table.
 
previously i asked two questions but i didn't got proper reply from any one
Your questions all deal with Excel and Oracle, yet you posted them in Access forums. Might I suggest you post the questions in either the Microsoft Office forum or an Oracle forum? You might find better expertise there.


Randy
 
Thanks Randy, Surly will do it.

Regards
Teja
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top