rutgermons
asked on
vb macro oracle connect string adodb
folks
how do i convert this code to oracle, it is the connect string I am needing assistance on
cnn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source=C:\Databases\StaffD atabase.md b"
i.e.
how do i convert this code to oracle, it is the connect string I am needing assistance on
cnn.Open "Provider=Microsoft.Jet.OL
"Data Source=C:\Databases\StaffD
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
will follow up over this weekend
ASKER
thanks for this,will this work though if the server hosting the db sits elswhere (not locally)