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