Solved

VB - Oracle

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now