Solved

vb macro oracle connect string adodb

Posted on 2013-11-02
5
589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

630 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