You can use the Data Link Properties dialog to define an OLE DB connection string. You can start out by predefining the Provider and other attributes, and then let the user browse for an MDB file and choose it if you have predefined Jet 4.0 as the Provider.
Once this selection has been made you can persist the connection as a .UDL (Universal Data Link) file. UDLs were what replaced DSNs a long, long time ago. I'm amazed they get so little use.
Here is some sample code that lets your program specify a UDL and a default path for the user to browse for an MDB file. If the UDL does not exist, it opens the Data Link Properties dialog so the user can choose an MDB, and lets them Test Connection from that dialog before Oking or Canceling their settings. Once it has the connection fully defined it persists it as a .UDL file and opens the Connection object.
If the UDL file exists it opens the Connection using the specs in the UDL.
The key here is the DbOpenPromptSave() function.
'Requries references to:
' Microsoft ActiveX Data Objects x Library (x >= 2.5)
' Microsoft OLE DB Service Component 1.0 Type Library
Private Function DbOpenPromptSave( _
ByVal Conn As ADODB.Connection, _
ByVal UDLPath As String, _
Optional ByVal MDBSearchStartPath As String = "") As Boolean
'Returns True if user cancels the dialog.
On Error Resume Next
GetAttr UDLPath
If Err.Number Then
'No UDL, we need to prompt the user then create one.
On Error GoTo 0
Dim dlkUDL As MSDASC.DataLinks
Set dlkUDL = New MSDASC.DataLinks
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Persist Security Info=False;" _
& "Jet OLEDB:Engine Type=5;" _
& "Data Source=" & MDBSearchStartPath & "\;" _
& "Window Handle=" & CStr(Me.hWnd)
If Not dlkUDL.PromptEdit(Conn) Then
DbOpenPromptSave = True
Exit Function
End If
'Use a Stream as Unicode writer. Using a relative path to save
'respects the Current Directory of the process.
Dim stmUDL As ADODB.Stream
Set stmUDL = New ADODB.Stream
With stmUDL
.Open
.Type = adTypeText
.Charset = "unicode"
.WriteText "[oledb]", adWriteLine
.WriteText "; Everything after this line is an OLE DB initstring", _
adWriteLine
.WriteText Conn.ConnectionString
.SaveToFile UDLPath, adSaveCreateOverWrite
.Close
End With
Conn.Open
Else
Conn.Open "File Name=" & UDLPath
End If
End Function
Private Function DbActions() As Boolean
'Returns True on cancel.
Dim connDB As ADODB.Connection
Set connDB = New ADODB.Connection
If DbOpenPromptSave(connDB, "sample.udl", App.Path) Then
MsgBox "User canceled!"
DbActions = True
Exit Function
End If
DoDbOperations connDB 'Whatever you need to do until closing.
connDB.Close
End Function
The DbActions() function is simply an example of calling DbOpenPromptSave() to open the database. This function opens the database, calls DoDbOperations() (not shown) to actually work with the open database, and then closes the database Connection.
This example uses a relative path (current directory, usually the same as App.Path) for sample.udl and sets the MDBSearchStartPath (where the Select Access database dialog opens) to App.Path (because this dialog defaults to where the last CommonDialog had been opened).
So in other words...
It looks for/saves the UDL sample.udl in CD (usually App.Path), and the MDB selection dialog opens in App.Path. Whew.
I suppose just passing CurDir$() might have been clearer in this case.
I hope this comes close to what you were requesting, it was a little vague.
The MDB selection subdialog the user opens is pretty much a standard CommonDialog.ShowOpen dialog. The user should be able to browse for the MDB file on any drive including file shares on remote systems.