Building
Ado Connection Strings On-the-fly
By Mike Gunderloy
In most cases you’ll know which data you want to work with when you’re writing
your application. Sometimes, though, it’s useful to allow the user to pick
a data source. For example, you might be writing a tool that offers a way
for users to import tables from existing, possibly non-Access databases. In
this case, you need to provide a way for your users to build an ADO connection
string at runtime.
The easiest way to handle this (assuming you have reasonably sophisticated
users) is to display the standard Data Link properties dialog box. By using
the OLE DB core components, you can display this dialog box and automatically
create an ADO Connection object from the user's choices.
To use this technique, you’ll need to include a reference to the Microsoft
OLE DB Service Component 1.0 Type Library. (This type library is installed
by OLE DB, so any computer where ADO is available should have it installed.)
The following code will display the Data Link properties dialog box and return
the result to the user interface:
Private Sub GetConnection()
Dim dlk As MSDASC.DataLinks
Dim cnn As ADODB.Connection
Set dlk = New MSDASC.DataLinks
' Create the Connection by prompting the user
Set cnn = dlk.PromptNew
MsgBox "Your connection string is " &
cnn.ConnectionString
End Sub