CodeItBetter Programming Another VB Programming Blog

'Database - How to Prompt user to construct the connection string at runtime!!!
'It uses the same interface that ADODC, DataEnvironment etc use.

'None
'This snippet was originally a part of MS ADO Stored Procedure Add-In (freely downloadable)
'You can use the Add-In to build ADO Commands etc and skip DataEnvironment limitations and overheads

'References:
'1. Microsoft OLE DB Service Component 1.0
'2. Microsoft ADO 2.5

Public Function GetConnectionString(Optional strOLE As String = "OLEDB") As String
    On Error GoTo ErrHandler
 
    Dim cn As ADODB.Connection
    Dim objDataLink As MSDASC.DataLinks
 
    Set cn = New ADODB.Connection
    Set objDataLink = New MSDASC.DataLinks
 
    Select Case strOLE
    Case "OLEDB"
        cn = objDataLink.PromptNew
        GetConnectionString = cn.ConnectionString
    Case "ODBC"
        cn.ConnectionString = ""
        cn.Properties("Prompt") = adPromptAlways
        cn.Open
        GetConnectionString = cn.ConnectionString
        cn.Close
    End Select
 
    Exit Function
 
ErrHandler:
    ' skip known error conditions; otherwise, report error
    ' (e.g. skip ODBC builder 'action cancelled' error)
    If Err.Number = 91 Or Err.Number = -2147217842 Then
        Exit Function
    Else
        MsgBox "Error: " & Err.Description
    End If
End Function