Access data into Excel worksheet

[inoxo]

Hi

I have a dropdown list in an Excel Worksheet and I would like to use it to select and retreive data comming directly from an Access table or query.

I believe it is possible and I would spare a lot of time if someone could you put me directly on the best way to do it.

Thanks.

 

[Sergeant]

Right, you can make a database query in Excel through the Data/Import External Data/New Database Query menu selection.
This sets up a DSN for your database. It also sets up the CommandText for the query.
You can then read/write this info like:
Sheet1.QueryTables(1).Connection
Sheet1.QueryTables(1).CommandText

You may want to make a new query in your spreadsheet and then use a button to run code that will write these values to a cell so you can analyze them.
Notice that the database path is saved in both properties. If you are using just one database, it's quite easy to rework the CommandText string to select whatever fields you want from whatever table. Then you can save it back into the QueryTable like this:
StrQry = "SELECT field1, field2, field3 FROM 'C:\MyFolder\MyDB.mdb'.MyTableName MyTableName WHERE (MyTableName.MyField='" & Me.MyComboBox & "')"
Sheet1.QueryTables(1).CommandText = StrQry

If you want to change databases, you will need to affect the connection property (I haven't done this, so play with it and make sure you post back your findings.)

BTW, you can tie all of this to your combobox afterupdate event, and finish it with "Sheet1.QueryTables(1).Refresh".

 

[inoxo]

Thank you. I tried to do it so but I have some difficulties because I have no experience in Excel programming. My exeprience is Access VBA. Could you recommend me some information source in relation with this thread ?
Thanks again.

 

[Sergeant]

Well, you can play with it until you get stuck and post here, and you can also go to mrexcel.com, which is a good resource.
The VBA is the same, it's just the Object Model that's different.