excel UF link to Accesd DB

[CuriousGeorg]

Good morning,

Im wondering if this is possible. I know it can be done via excel alone or access alone but I need to be able to use both.

What I need is for there to be the data base in Access and then a Userform in Excel.

I will be having a drop down in the userform for the user to select a person and a department. What I want to know is if the user and dept are in an access database is there a way I can use a link between "if user selects department A then x,y,z user appears in the next combobox. IF user selects department b, then a,b,c, user appears in the second combobox etc?

I have done it where the list is in excel an dcan be done using indirect however I need to maintain the list via Access rather than that individual excel spreadsheet

[Bob Phillips]

You can create a parameterized query in Access, and call that query from Excel using ADO to return a recordset that can be manipulated in Excel.

[CuriousGeorg]

Ok, thanks.. (ill look that up in more detail). Least I know it can be done. Which will make my life a lot easier especially as I can use the database to form various workbooks

[Bob Phillips]

It can be done, I am working on two projects at the moment using it.

When you Google it, look for ADO and Command object, that is the way to pass parameters to a query. Your Access query might look something like


Parameters param.person As Text ( 48), param.department As Text (24);
SELECT person, department, salary, start_date, grade
FROM StaffDetails As staff
WHERE staff.person = param.person AND staff.department = param.department;