Need drop down of database tables

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
In the example below, columns A,B,C&D are imported via Import External Data. As of now, I have to manually go through the import dialogue to get to the tables I want.

Is it possible to:

A) Make cell C3 refer to a given database ("PGSales2005.mdb") perhaps navigating to a given folder on my hard drive...

B) Make cell C4 a dropdown list of available tables in cell C3's DB... and...

C) Once cell C4 is entered, have cells A7:D40 import automatically.

I know there are formatting and filtering issues involved, and I believe I can work them out. The main thing is being able to get cell C4 as a dropdown of available tables.

Any ideas? Any pointers to some good reading on this so I can research it on my own?

Thanks in advance.

bobmc
ZIP ZONES.xlt
ABCDEFGHI
1ZIPCODE/ZONEPOSTAGECALCULATOR
2
3Database:PGSales2005
4Table:TableA
5
6PaidCustomerNameZIPItemQtyDelWgt/OzZonePostage
744.85JackCalnan90068VHS342454.90
844.85EricSCorp01002DVD341683.85
944.85RichardWurzbacher20721DVD341683.85
1029.90JamesG.Robinson75048DVD22873.85
Sheet1
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
I doubt you will be able to do this using MSQuery, but you could do it by using ADO to query your Access database(s)

I actually created a userform that had a single combobox on it (called combobox1) and a button called cmdGetdata

Here are the codes

Code:
Private Sub UserForm_Initialize()
    Dim cnt As ADODB.Connection
    Dim stDB As String, stConn As String, stSQL As String
    Dim TablesSchema As ADODB.Recordset

    'clear combobox
    Me.ComboBox1.Clear
    'Instantiate the Connection object.
    Set cnt = New ADODB.Connection
     
     'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "\" & "TestExcel.mdb"
     
     'Create the connection string.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
    
    With cnt
        .Open stConn
    'Get all database tables.
        Set TablesSchema = cnt.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
        Do While Not TablesSchema.EOF
       'add them to combobox
           With Me.ComboBox1
                .AddItem TablesSchema("TABLE_NAME")
            End With
            TablesSchema.MoveNext
        Loop
    End With
     
    Set cnt = Nothing
End Sub

The above code populates the combobox with all tables in DB specified in cell C3

Code:
Private Sub cmdGetData_Click()
Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stDB As String, stConn As String, stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet1 As Worksheet
    Dim Lrow As Long

     'Instantiate the Connection objects.
    Set cnt = New ADODB.Connection
    Set wbBook = ThisWorkbook
    Set wsSheet1 = wbBook.Sheets("sheet1")
    'set the row for returning the recordset
    Lrow = wsSheet1.Cells(65536, 1).End(xlUp).Row + 1
    
    'Path to and the name of the database.
    stDB = ThisWorkbook.Path & "\" & wsSheet1.Range("C3").Value
     
    'Create the connectionstring.
    stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & stDB & ";"
     
    'Create the SQL-statement.
    stSQL = "SELECT * FROM " & Me.ComboBox1.Value
    stSQL = stSQL & " WHERE CustomerID = 2"
     
    With cnt
        .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
        .Open stConn 'Open connection.
         'Instantiate the Recordset object and execute the SQL-statement.
        Set rst = .Execute(stSQL)
    End With
     'Copy the recordset to specified excel sheet & range.
    With wsSheet1
        .Cells(Lrow, 1).CopyFromRecordset rst
    End With
    
    cnt.Close
         'Release objects from memory.
    Set rst = Nothing
    Set cnt = Nothing
End Sub

The above code then returns the data from the specified table to XL

Note that you need to set a reference in the VBE to MDAC 2.x (Microsoft Data Access Compnents file - 2.5 or > )

Hope this helps.

If you want the sample file, PM me with email address & I will send it to you
 

bobmc

Board Regular
Joined
Mar 13, 2002
Messages
142
Thanks, WillR, but I think we've surpassed my level of incompetence here.

I take it that I paste the code you've provided above into the worksheet VBA. I think I might be able to fumble through the code to make necessary modifications, but first, some potentially stupid questions:

What is "ADO"?

Does the VBA code you show above need to be activated somehow?

Also, I don't know what "userform" is referring to.

Like I said... we're beyond my level of expertise here, so I may just need to do some studying up!

Thanks again for the help.

bobmc
 

WillR

Well-known Member
Joined
Feb 18, 2002
Messages
1,143
OK, I can't attach files here, but I have attached an example of the working code Here

You need to unzip both files to the same directory.

You can access the code in the Excel Worksheet by pressing CTRL+F11
 

Forum statistics

Threads
1,148,220
Messages
5,745,456
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top