Populate a combo box in Excel with dan Access table

[Pete78]

Hi,

Can anyone please help. I am trying to create an Excel worksheet that will have a number of ActiveX controls on it including combo boxes that I would like to be populated with data in tables in an access 97 database.

Here is what I have attempted so far. I'm afraid I don't know if this is even close to being what I need to write:

Dim rs As Recordset
Dim strsql As String
Dim db As Database
Dim rsDatabasePath As String
Dim objAccess As New Access.Application
rsDatabasePath = "V:\Marketg\IM\Projects\ProductChange\ProductChangeDB28102003.mdb"
objAccess.OpenCurrentDatabase (rsDatabasePath)
Set db = "objAccess"
strsql = "SELECT [ProdID] FROM [Products]"
Set rs = db.OpenRecordset(strsql)


I don't even know if this was right how then I can populate my combo box using this recordset.

Any Help would be very much appreciated.

Pete

 

[Anvil19]

Hey Pete

I've found that the best way to fill a comboBox with a list from a database is to write a SQL statement directly into the ComboBoxs' RecordSource Property space.

You will need to use the IN clause as your ComboBox is in Excel and the data is in an Access Database, eg...

SELECT <FieldName> FROM <TableName> WHERE <Conditions> IN &quot;V:\Marketg\IM\Projects\ProductChange\ProductChangeDB28102003.mdb&quot;

Try That

Anvil19

 

[Pete78]

Thanks Anvil19