Using Access database for drop-downs in Excel

[adds007]

Hi,
Currently, I am working on a program to link MULTIPLE identical excel files to ONE excel catalogue file that's in a shared drive to fetch data. I realized that doing this using a database might be more efficient. Therefore, I came here to seek some help on getting me started. I want to convert the "excel based database" to "access based database". The current excel master file have named ranges that are copied over to other excel files for drop-down use.

If someone can roughly explain me how I can achieve below things, I would really really appriciate it. NOTE: Database has to be password protected. No one should be able to edit the database.
1) Upon opening excel file, link it to master database file located in a shared drive without needing to open the database
2) Use database to provide live drop-downs in excel file
3) With a click of a button in excel, information from excel drop-downs will be matched againt table in the database that will fetch data and paste into excel.
4)Close the linkage to database upon closing excel file

If the database can perform these four main things without compromising permomance, then the program should be a lot more efficient all thanks to you guys.

[alansidman]

This should get you started in the right direction

http://support.microsoft.com/kb/244761/en-us

Alan

Cross Post: http://www.mrexcel.com/forum/showthr...=1#post2633145

[adds007]

Thanks for your response alansidman!
The website you provided gives a combo box of items copied and pasted one by one from the database. I figured out how to copy a database table to Excel and make a data validation list for in-cell drop-downs.
Is there a way to do this without copy and paste? I mean after creating a link to the database, can i reference cell values to particular recordsets and initialize a named range that's referencing the database to be used for drop-downs.

Thanks