Importing data using ODBC Database

[murphyku]

My goal here is to give the user a prompt (or userform) containing the different ODBC databases they can choose from. Once they choose one, I would like to retrieve data from that database.

I know how to retrieve the data from a specific ODBC database using VBA, but I am stumped as to how to give the user a list of the available ODBC databases to choose from and then use the chosen database to retrieve data.

The data that is to be retrieved is the same for each database. I would just like the user to have control over which database they are retrieving data from.

Any amount of help or ideas is appreciated.

Thanks!

murphyku

 

[WillR]

You can use the datalinks wizard

     '*************************************************************************
     '* 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                *
     '*                                                                       *

     
    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 'or return this variable to your code
     '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

 

[murphyku]

****EDIT****

Thank you very much, I have solved my problem using what you gave me and a few minor tweaks.