drop down list from database

I

irene c

Hi, is it possible to create a drop down list which take source of data
directly from a table in MYSQL database?

Thank you in advance for the help
 
B

Bob Phillips

You could have a timed routine, using say Application.Ontime, which builds a
list on a worksheet and use Data Validation to point at that.

An alternative is to use a combobox from the control toolbox or forms
toolbar, and create a procedures to query the database and populate the list
dynamically from the recordset.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I

irene c

Thanks for your reply. But since I am quite new in excel and VBA, could you
please explain it in more detail?
Thanks once again.
 
B

Bob Phillips

Which one?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I

irene c

Am I correct to say that for the first method , I still have to import the
data from the database into a worksheet, whereas the second method I can make
my data Validation to directly point to the database?

If it is correct, then I will appreciate if you could explain the second
method in more details.

I just want to clarify that what I want is to make a drop down list, where
the source is directly from a MYSQL database, so I dont have to firstly
import the data into a worksheet, and make the data validation to point at
that worksheet.

Thanks a lot
 
B

Bob Phillips

Your understanding is correct, although retrieving from the database every
time someone clicks the dropdown arrow might be a bit inefficient.

Firsty, do you understand ADO, and do you have a SQL query to retrieve the
values to populate the list?

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I

irene c

Ow, I didnt realize that retrieving directly from the database is more
inefficient than importing the data into the excel sheet first. But after i
think about it, it is possible.

Well, my first thought that time is that by retrieving the data directly
from the database, I will save space in excel sheet, coz I have a lot of
tables in database, so if I have to import all into the excel sheet, I
thought it will take quite many storage space. Next is if the database change
then I will have to refresh many tables in my excel sheet.

anyway is there any way where I can refresh all tables in different excel
sheets at once ? because I am using ODBC driver, I have to refresh data for
each table individually and it is quite troublesome and wasting time if I
have a lot of table in the excel sheet.

I dont understand ADO, but I already manage to import the data from MYSQL
database into excel sheet to populate the list, by using MYSQL ODBC Connector
driver.

I am sorry for asking so many questions. Thanks a lot in advance.
 
Top