• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

Find OLEDB Provider and DB Server

1. How can I search the list of all the OLEDB Providers in my system?
   One way is to lookup the registry, is there any other way ?

2. How can I look up for the available database servers on the network?


Thanks for giving time...
:-)
c_sh.

0
c_sh
Asked:
c_sh
  • 7
  • 6
1 Solution
 
tkalchevCommented:
What for a servers do you need to find - MS SQL Server, Oracle ... :)
0
 
inthedarkCommented:
The easy way to create a connection string is to create a blank text file. Rename the file to say MyConnection.udl  double click on the file. The click on the providers tab (which lists the providers).  You can then process through the steps to create the file.  Open the file using notepad and you get your connection string.

Hope this helps:~)
0
 
c_shAuthor Commented:
Wonderful inthedark...
This was the thing I was looking for.

Is there any API or function call so that I can get same interface invoked from a VB application?


tkalchev, thanks to you too, I am looking for the thing inthedark has suggest.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
c_shAuthor Commented:
I am thinking if I can get something from oledb32.dll....


c_sh.
0
 
inthedarkCommented:
Or you can use VB's Shell like this:

PID=Shell("Rundll32.exe C:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll,OpenDSLFile C:\MyFolder\MyConFile.UDL", windowstyle)

The advantage of this is than you can see when the program is closed via the PID.  You then pickup the string from the file that was created.


Another way:

Have you used ShelExecute if you have not seen this api call yet here is some sample code:

OK=LaunchOK("C:\MyFolder\MyConFile.UDL")

(But this is harder to find the PID in this method.)

Private Declare Function ShellExecute Lib "Shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, _
        ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Function LaunchOK(FileName As String, Optional Params As String = "", Optional OpenMode As String = "Open")

Dim lr As Long
Dim folder As String

folder = FolderName(FileName)
If Right(folder, 1) = ":" Then
    folder = folder + "\"
End If

lr = ShellExecute(0, OpenMode, FileName, Params$, folder$, vbNormalFocus)
DoEvents
If (lr < 0) Or (lr > 32) Then
    LaunchOK = True
Else
    LaunchOK = False
End If

End Function

0
 
c_shAuthor Commented:
I am thinking if I can get something from oledb32.dll....


c_sh.
0
 
inthedarkCommented:
You cann add oledb32.dll to your references but the OpenDSL file is not available. But some other things may be able to help.  I will take a look.
0
 
c_shAuthor Commented:
I'll just check these options....





0
 
c_shAuthor Commented:
Using ShellExecute() I will not have any command over the launched window.

Using Shell() it seems that this can be done to certain extent, for this I need API names to do following:

1. Check the process state if PID is given.
2. Change the process state (to stop the process and close the window) if its PID is given.

Do you have any idea about this?


c_sh.
0
 
inthedarkCommented:
Here is an example.


fire the command making sure you specify an empty file name

ok=RunCommandOK(commandstring$)

Now the command is complete and the window has been closed. Simply open the file if it has valid date then you know the operator did what they meant to.


Private Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwAccess As Long, ByVal fInherit As Integer, _
        ByVal hObject As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" _
        (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long

Public Function RunCommandOK(CommandLine As String, Optional FocusStyle As VbAppWinStyle = vbNormalFocus) As Boolean
   
    ' runs a batch command of other process until complete
       
    Dim Success As Long
    Dim ProgID As Long
    Dim Handle As Long
    On Error Resume Next
    ProgID = Shell(CommandLine, FocusStyle)
    Handle = OpenProcess(&H100000, True, ProgID)
    Success = WaitForSingleObject(Handle, INFINITE)
    'CloseHandle ####
   
End Function

0
 
c_shAuthor Commented:
Still having problem,
this is the code...

Private Sub Command1_Click()
PID = Shell("Rundll32.exe C:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll,OpenDSLFile D:\Ch\Project\test.UDL", windowstyle)

Handle = OpenProcess(&H100000, True, PID)
Success = WaitForSingleObject(Handle, INFINITE)
'CloseHandle ####
   
MsgBox "Done"
End Sub


Here what I need is control should move forward after Data link dialog is closed.
In above case I am getting the "Done" msgbox before closing the Data Link Property dialog.

I need to know when the dialog box is close.


c_sh.
0
 
inthedarkCommented:
That is because I did not give you the def for INFINITE.
Further your windowstyle should be vbNormalFocus.

Private Const INFINITE = &HFFFF ' (Used by run command)

Here is an extract of code which works for me. I placed loads of handy functions i a class which I use called zGF in a global module I have declared

Global GF as New zGF

This gives me easy access to stacks of routines which I use all of the time.  

make sure "option explicit" is declared at the start of  all modules.

Private Sub Command1_Click()
Dim cmd$
Dim ok As Long
Dim FileName$

' setup file name
FileName = "d:\test.udl"

' make sure file is removed
If Len(Dir(FileName)) > 0 Then
    Kill FileName
End If

' create an empty file
ok = GF.WriteFileOK(FileName, "")

' create the command
cmd = "Rundll32.exe C:\PROGRA~1\COMMON~1\System\OLEDB~1\oledb32.dll,OpenDSLFile " + FileName

' run the command and wait until closed
ok = GF.RunCommandOK(cmd)

Dim sConnection As String

' read the file or return zero length string if file
' is empty

sConnection = GF.ReadFile(FileName)

' return part of sring after provider=
sConnection = GF.RightPart(sConnection, "Provider=")
If Len(sConnection) > 0 Then
    ' add word provirder= back into connection string
    sConnection = "Provider=" + sConnection
Else
    MsgBox "no conection specified"
End If

End Sub
0
 
inthedarkCommented:
In your case I would suggest 1 additional improvment to make the rpovide tab show first. The operator realy needs to choose this first. (But the UDL file processor expects the provider to have already been selected.) See ** Add these lines


Public Function RunCommandOK(CommandLine As String, Optional FocusStyle As VbAppWinStyle = vbNormalFocus) As Boolean
   
    ' runs a batch command of other process until complete
       
    Dim Success As Long
    Dim ProgID As Long
    Dim Handle As Long
    On Error Resume Next
    ProgID = Shell(CommandLine, FocusStyle)

    ' ** add these lines
    doevents ' allow window to arrive
    SendKeys "{Left}" ' make provide window show first

    Handle = OpenProcess(&H100000, True, ProgID)
    Success = WaitForSingleObject(Handle, INFINITE)
    'CloseHandle ####
   
End Function

Hope this helps:~)
0
 
c_shAuthor Commented:
That was fantastic !
Thanks for giving me your valuable time.


c_sh.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now