Skip to: Site menu | Main content

Login

Name: 
Password:
Remember me?
Register

Set up an ADO Database Connection String

written by Will Riley - Last updated Oct 2004

Here is some code that enables a user to get the correct connection string in order to connect to the required database. Sometimes the syntax of this can be a little tricky so here is a utility that we can use to do the hard part for us.

Option Explicit 
 
Sub Get_A_Connection() 
     '*************************************************************************
     '* The following code enables the user to get a valid connection string  *
     '* for use when returning data to Excel using ADO.                       *
     '*                                                                       *
     '* Requires references to the following:                                 *
     '* 1. Microsoft ActiveX Data Objects 2.x Library                         *
     '* 2. Microsoft OLE DB Service Component 1.0 Type Library                *
     '*                                                                       *
     '* Special thanks to XL-Dennis for code & idea sharing ("2 heads > 1")   *
     '*************************************************************************
     
    Dim cnADO As ADODB.Connection 
    Dim objFinder As MSDASC.DataLinks 
    Dim strCon As String 
     
     'Set up reference to the relevant ADO Objects.
    Set objFinder = New MSDASC.DataLinks 
    Set cnADO = New ADODB.Connection 
     
    On Error Goto Err_stop 
     'Show the Database Connection wizard
    strCon = objFinder.PromptNew 
     'Test connection.
    cnADO.Open strCon 
     'show connectionstring in messagebox
    MsgBox strCon 
     'Print the connection string to the VBE Immediate Window.
    Debug.Print strCon 
     'Clean up.
     
ExitPoint: 
    cnADO.Close 
    Set cnADO = Nothing 
    Set objFinder = Nothing 
    Exit Sub 
     
Err_stop: 
    If Err.Number = 91 Then 
        Resume ExitPoint 
    End If 
End Sub