Solved

VB - Oracle

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

16 Experts available now in Live!

Get 1:1 Help Now