‘How to Prompt user to construct the connection string at runtime!!!
‘It uses the same interface that ADODC, DataEnvironment etc use.
‘References:
’1. Microsoft OLE DB Service Component 1.0
’2. Microsoft ADO 2.5
‘Visual Basic Code Below
Public Function GetConnectionString(Optional strOLE As String = “OLEDB”) As String
On Error GoTo ErrHandler
Dim Conn As ADODB.Connection
Dim ObjectDataLink As MSDASC.DataLinks
Set Conn = New ADODB.Connection
Set ObjectDataLink = New MSDASC.DataLinks
Select Case strOLE
Case “OLEDB”
Conn = ObjectDataLink.PromptNew
GetConnectionString = Conn.ConnectionString
Case “ODBC”
Conn.ConnectionString = “”
Conn.Properties(“Prompt”) = adPromptAlways
Conn.Open
GetConnectionString = Conn.ConnectionString
Conn.Close
End Select
Exit Function
ErrHandler:
If Err.Number = 91 Or Err.Number = -2147217842 Then
Exit Function
Else
MsgBox “Error: “ & Err.Description
End If
End Function
