Solved

VB - Oracle

Posted on 2000-03-06
3
339 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 75 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

733 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