Solved

vb macro oracle connect string adodb

Posted on 2013-11-02
5
582 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
ID: 39619730
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
ID: 39621318
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
ID: 39621342
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
ID: 39633091
will follow up over this weekend
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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