Solved

vb macro oracle connect string adodb

Posted on 2013-11-02
5
576 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running VB/Batch script through Group policy 30 76
excel pivot question 4 40
Search for a value in Column? 5 21
macro for closing opened workbook 6 18
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

914 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

20 Experts available now in Live!

Get 1:1 Help Now