Home > How-To Library > Database

How to Prompt user to construct the connection string at runtime!!!

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

'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

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.