VB - Oracle

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
Who is Participating?
TimCotteeConnect With a Mentor Commented:
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.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
    Set rstValExp = Nothing
    Set cnnAccess = Nothing

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))"

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


LawrenceYAuthor Commented:
The codes above is to create an Oracle table through "real" coding rite. I was actually searching for a VB method.
Thanks man/woman.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.