?
Solved

EXCEL VBA to update Oracle table

Posted on 2011-10-11
16
Medium Priority
?
4,783 Views
Last Modified: 2012-05-12
Experts,

First let me explain what I am trying to do.
I have a excel 2007 spreadsheet "Data Dump" that has some data. Columns: Ticket_ID, Process_Date, Reviewed_Date

I have created a new table in oracle named Stats.  I have named the table columns to match the above.

Objective:  Using VBA (ADO) upload my excel info into this table.

I appreciate the help!

 Data-Dump.xlsm
0
Comment
Question by:Maliki Hassani
  • 9
  • 6
16 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 36951086
Hi Lance
Here is an article on how to connect from VBA to Oracle
http://www.astahost.com/info.php/Vba-Oracle_t10766.html

This article shows how to select data down

You need to do the opposite

' Open DB Connection

' Loop through each row of data on spreadsheet
'With each  loop do something like
oraDynaSet.AddNew
oraDynaSet.Fields(x) = Cells(y).Value
oraDynaSet.Update

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 36951119
The process is straight forward.

1. Open an ADO Connection to Oracle using either Microsoft or Oracle driver:
http://carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForOracleFromMicrosoft
http://carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForOracleFromOracle

2. Concatenate a SQL Insert statement
3. Execute the statement on your connection object
4. Repeat.
5. Close ADO connection.

Leon
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 36951154
The sample shown in BCUNNEY sample is actually not ADO, but rather OO4O In-Process Automation. It will work, but not as portable as ADO. I would also not recomend using the AddNew method in either ADO or OO4O.

Leon
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Maliki Hassani
ID: 36951548
Thanks everyone..  I will try to write it up and get back with you shortly..  I have been trying everything under the sun but no luck thus far.
0
 

Author Comment

by:Maliki Hassani
ID: 36951697
Okay well I am going to have to ask if someone can show me a modified script that would work with what I have provided.  Using my provide field names etc.  Not able to connect at all keep getting error.  in addition, what all references are need as well?
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 2000 total points
ID: 36952024
You will need to establish a reference to "Microsoft ActiveX Data Objects 2.8 Library".

Your Code should look like this:
Sub ADOInsert()
Dim cnAdo As ADODB.Connection
Dim strConnString As String
Dim strSQL As String

    '/ For a Trusted Connection. 
    '/ You will need to supply the User Id and Password
    strConnString = "Provider=OraOLEDB.Oracle;" & _
           "Data Source=MyOracleDB;" & _
           "User Id=/;" & _
           "Password="

    Set cnAdo = New ADODB.Connection
    cnAdo.Open strConnString
    
    '/ this is hardcoded for number of rows and columns.
    For lngRow = 1 To 10
        strSQL = "Insert Into Stats Select "
        strSQL = strSQL & "'" & Range(lngRow, 1).Value & "', "   '/ Ticket_ID
        strSQL = strSQL & "'" & Range(lngRow, 2).Value & "', "   '/ Process_Date
        strSQL = strSQL & "'" & Range(lngRow, 3).Value & "' "    '/ Reviewed_Date
        cnAdo.Execute strSQL
    Next
    cnAdo.Close
    Set cnAdo = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Maliki Hassani
ID: 36952033
Thank you!  Let take me implement..
0
 

Author Comment

by:Maliki Hassani
ID: 36952129
here is my issue.  is this correct format ?
   strConnString = "Provider=OraOLEDB.Oracle;" & _
           "Data Source=?Database name;" & _
           "User Id=?Login to database;" & _
           "Password=?Password to database"
Does the provider stay the same?

also where does the script to tell it to update the table "stats" go?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 36952182
I have not used Oracle in a while, but I believe the Data Source is in your tsname.ora file. The others are right, but no '?' is needed.

The script to load the data is inside the For loop.

Are you trying to Update or Insert the data?
0
 

Author Comment

by:Maliki Hassani
ID: 36952208
insert the excel data into the oracle table

So should I just simply type in "tsname.ora" in the data Source?
Sorry i am a newbie..
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 36952229
No, you should find the tsname.ora file and look up the Data Source name there. Do you have the Oracle client installed?
0
 

Author Comment

by:Maliki Hassani
ID: 36952249
Yes I do..
0
 

Author Comment

by:Maliki Hassani
ID: 36952325
so the error iam getting is
ORA-12154: TNS: could not resolve the connect identifier specified
0
 

Author Comment

by:Maliki Hassani
ID: 36952495
Looks as if my ability to connect using odbc was lost. Redrafting computer and trying again. I think once I connect using the odbc connection I will be able to read the connection string and apply it to your code.
0
 

Author Closing Comment

by:Maliki Hassani
ID: 36953253
Thank you I had to use the microsoft way...  Oracle connection just wasn't working.. Oh well thanks a million!
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 36953497
I am glad it worked out. Thanks for the grade.

Leon
1

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question