[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

VB - Oracle

Posted on 2000-03-06
3
Medium Priority
?
362 Views
Last Modified: 2010-05-02
How do you create a table, modify and update a table in Oracle from VB? Can anybody please tell me the procedures to init. any connections and other things
0
Comment
Question by:LawrenceY
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 43

Accepted Solution

by:
TimCottee earned 150 total points
ID: 2587656
I don't actually use Oracle, but in principle the following methods should work.

Create an ADO connection to the Oracle database, you can then use the ADOX methods (Add a reference to ADO For DDL And security to the project first) to create a table and specify the fields and attributes.

    Dim catAccess As New ADOX.Catalog
    Dim tblValExp As New ADOX.Table
    Dim cnnAccess As New ADODB.Connection
    Dim rstValExp As New ADODB.Recordset
   
    'catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
    'catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"

'You will need to replace these lines with connection information to the Oracle DB.

    With tblValExp
        .Name = "tblValExp1"
        .Columns.Append "Stock Name", adVarWChar, 40
        .Columns.Append "Stock Symbol", adVarWChar, 6     '1
        .Columns.Append "Stock Price", adSingle       '2
        .Columns.Append "Est Date", adVarWChar, 10           '3
        .Columns.Append "Est Year", adVarWChar, 6         '4
        .Columns.Append "Price High(0)", adSingle     '5
        .Columns.Append "Price High(1)", adSingle     '6
        .Columns.Append "Price High(2)", adSingle     '7
        .Columns.Append "Price High(3)", adSingle     '8
        .Columns.Append "Price High(4)", adSingle     '9
        .Columns.Append "Price High(5)", adSingle     '10
    End With
    catAccess.Tables.Append tblValExp
    Set tblValExp = Nothing
    Set catAccess = Nothing
    cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
    rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
    rstValExp.AddNew
    rstValExp.Fields(0).Value = "Name"
    rstValExp.Fields(1).Value = "Symbol"
    rstValExp.Fields(2).Value = 1.5
    rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
    rstValExp.Fields(4).Value = "2000"
    rstValExp.Fields(5).Value = 1.5
    rstValExp.Fields(6).Value = 1.5
    rstValExp.Fields(7).Value = 1.5
    rstValExp.Fields(8).Value = 1.5
    rstValExp.Fields(9).Value = 1.5
    rstValExp.Fields(10).Value = 1.5
    rstValExp.Update
    rstValExp.MoveFirst
    Set rstValExp = Nothing
    Set cnnAccess = Nothing


0
 

Expert Comment

by:Spri
ID: 2590229
For starters, to connect to an Oracle database make sure you have Oracle Client installed on the machine.  This is required because the ADO connection object used the TNS Name when connectin to an Oracle database.

Make sure you have added a reference to the ActiveX Data Objects Library
Here is the connection:

dim conMain as AdoDB.Connection
set conMain = new AdoDB.Connection
conMain.open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=Test_DB;Persist Security Info=True"

This example uses the typical Oracle user of scott w/ password of tiger.
The Data Source is the TNS name of the database.

The way I create Oracle tables in VB is by using a command object, this requires knowledge of SQL.
Here is an example(using the connectin from above) that creates a table w/ a user name and password:

dim  comDB as ADODB.Command
set comDB = new ADODB.Command
comDB.CommandText = "Create table user_security ( username char(20), password char(20))"
comDB.Execute


Hope this helps...if you need more post what you need.

Spri

0
 

Author Comment

by:LawrenceY
ID: 2595436
The codes above is to create an Oracle table through "real" coding rite. I was actually searching for a VB method.
Thanks man/woman.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

649 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