I was asked to find utility to test OLEDB conection paramenters.
I desided to use EXCEL with the following procedure:
2. Open MS Excel (I've tested it with Excel 2003) , hope that previous versions are similar.
3. On the Data menu, point to Import External Data, and then click Import Data.
4. In the "Files of Type" select "Microsoft Data Links( *.udl)"
5. Open UDL file created in step 1
6. Select table that you are interesting in.
7. The results should be shown in the spreadsheet.
However even the connection was tested successfully in Data Link Properties, Excel reported “no tables found“ when user tried to open the UDL file.
Then I was asked to find another way to test OLEDB Connection.
Microsoft has KB article How to use Data Link files with the OleDbConnection object in Visual Basic .NET shows only basic connection test.
Then I found An Approach to Viewing the Structure of a Database Using VB 2005 (AKA DatabaseSpy).
I've downloaded the code , but UI for different supported databases(Oracle,SQL Server and Access) doesn't include generic OLEDB provider.
Fortunately, It was easy enough to add new connection tab UDL (in frmConnect.vb) with Browse button and ability to load ,test and assign connection from UDL file.
TODO: It would be better to 
call DATA Link APIs directly as described in “Incorporating the Data Link Properties Editor into a VB.NET Application” article.
Private Sub btnUDLBrouse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLBrouse.Click
Dim openFile As System.Windows.Forms.OpenFileDialog = Me.OpenFileDialogUDL
openFile.Title = "Universal DataLink"
'openFile.DefaultExt = "mdb"
openFile.Filter = "Universal DataLink (*.udl)|*.udl"
openFile.ShowDialog()
txtUDL.Text = openFile.FileName
End Sub
Private Sub btnUDLTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLTest.Click
'Dim response As String
Dim cn As New OleDbConnection()
mConnectionString = "File Name=" & txtUDL.Text
cn.ConnectionString = mConnectionString
TestConnectionString(cn)
End Sub
Private Sub TestConnectionString(ByVal cn As OleDbConnection)
Try
cn.Open()
If cn.State = ConnectionState.Open Then
MessageBox.Show("Connection opened successfully")
Else
MessageBox.Show("Connection could not be established")
End If
' MessageBox.Show("Connection attempt successful, the database connection information provided has been successfully used to connect to the database.", "Connection Successful", MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show("Connection attempt failed.", "Unable to Connect", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
cn.Close()
cn = Nothing
End Try
End Sub
Private Sub btnUDLCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLCancel.Click
Me.Dispose()
End Sub
Private Sub btnUDLOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUDLOk.Click
btnUDLTest_Click(sender, e)
'set app setting global variables
mCurrentDataModel = "UDL"
mCurrentDatabaseType = "UDL"
AppVars.gCurrentDataModel = mCurrentDataModel
AppVars.gCurrentDatabaseType = mCurrentDatabaseType
AppVars.gConnectionString = mConnectionString
'create hashtable to hold settings
Dim ht As New Hashtable
ht.Add("CurrentDataModel", gCurrentDataModel)
ht.Add("CurrentDatabaseType", gCurrentDatabaseType)
ht.Add("ConnectionString", gConnectionString)
' ht.Add("ProviderString", gProviderString)
'ht.Add("ServerName", gServerName)
'ht.Add("UserID", gUserID)
'ht.Add("Password", gPassword)
'serialize data
SerializeAppVars(ht)
Me.Dispose()
End Sub
posted @ Tuesday, September 26, 2006 4:23 PM