• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Prompt user for connection and build the connection string at runtime, with the known interface tha

Prompt user for connection and build the connection string at runtime, with the known interface tha

Prompt user for connection and build the connection string at runtime, with the known interface that VB uses. Works with OLEDB and ODBC.

Rate Prompt user for connection and build the connection string at runtime, with the known interface tha

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'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