Solved

vb macro oracle connect string adodb

Posted on 2013-11-02
5
572 Views
Last Modified: 2013-11-17
folks

how do i convert this code to oracle, it is the connect string I am needing assistance on

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\Databases\StaffDatabase.mdb"


i.e.
Private Sub UserForm_Initialize()
    On Error GoTo UserForm_Initialize_Err
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=C:\Databases\StaffDatabase.mdb"
    rst.Open "SELECT DISTINCT [Department] FROM tblStaff ORDER BY [Department];", _
             cnn, adOpenStatic
    rst.MoveFirst
    With Me.ComboBox1
        .Clear
        Do
            .AddItem rst![Department]
            rst.MoveNext
        Loop Until rst.EOF
    End With
UserForm_Initialize_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
UserForm_Initialize_Err:
    MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    Resume UserForm_Initialize_Exit
End Sub

Open in new window

0
Comment
Question by:rutgermons
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
Robert Schutt earned 500 total points
Comment Utility
I got it to work on my local Oracle with:
'    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
'             "Data Source=C:\Databases\StaffDatabase.mdb"
    cnn.Open "Provider=ORAOLEDB.ORACLE" & _
            ";Data Source=XE" & _
            ";User ID=SYSTEM" & _
            ";password=SYSTEM"
    rst.Open "SELECT DISTINCT DEPARTMENT FROM TBLSTAFF ORDER BY DEPARTMENT", _
             cnn, adOpenStatic

Open in new window

0
 

Author Comment

by:rutgermons
Comment Utility
Robert
thanks for this,will this work though if the server hosting the db sits elswhere (not locally)
0
 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
Yeah, it should work fine but probably you need to configure the Oracle SID via TNS and make sure the target database allows remote connections, especially the newest (free) versions of Oracle I think don't allow this by default for obvious security concerns. And if you're talking about a separate location/network altogether then you need to make sure the connection (on port 1521 by default if I remember correctly) can pass firewalls and so on.
0
 

Author Comment

by:rutgermons
Comment Utility
will follow up over this weekend
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 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

15 Experts available now in Live!

Get 1:1 Help Now