How to get lookup value list via ADO

Humate
04-20-2004, 05:16 AM
I've got an Access database where the database author has set up lots of fields with lookup info. (I.e, they've explicitly set up lookup info for each field in the design view, in Access. So when entering data, in Access, it shows the combo-boxes.) How do I retrieve the lookup value list using ADO/ADOX? I know how to get all the field properties such as 'allow zero length' etc, but what about the lookup stuff... or is it only ever visible inside Access?

NB I know that a better design would be to have all the lookup info stored in a separate table, so everyone can get to it, but I don't want to have to go back to the author and tell them to start all over again

MKoslof
04-20-2004, 07:33 AM
Use SQL, build a query.

To get started with SQL, review this site:

http://www.databasejournal.com/sqletc/article.php/1402351

Humate
04-20-2004, 07:39 AM
OK, show me an example bit of SQL that you think does what I want... (I've been using SQL for years. Either there's some really useful bit of it that I've never come across or else you're misunderstanding my question.)

MKoslof
04-20-2004, 07:43 AM
When you say "look up" info, what do you mean? Do you mean data within each field? If so, there is no better way than SQL. Or I don't understand the question.

Humate
04-20-2004, 07:58 AM
If you're in the Access IDE, and in Design View mode of a table, you can set the "Row Source Type" property of a field to be "Value List", and then type in a series of values that you want the user to be restricted to. All fine as long as the user stays inside the Access IDE. However I want to be able to see what's in the Value List from a VB program, using ADO.

All the stuff that's on the 'General' tab of the Design View (like 'field size', 'format', etc), you can see, because it's included in the object model of ADO catalogs. But I can't find where the Value List is exposed. I was wondering if maybe you just can't do it because it's only ever available when you're inside the Access IDE.

MKoslof
04-20-2004, 08:01 AM
Oh, God, sorry, I completely misunderstood the question :). Well, this value list is not exposed to VB (at least not directly). I don't think you can get to it from VB.

Humate
04-21-2004, 07:25 AM
I'm sure it's impossible now. But - for the sake of anyone ever trying to do the same thing - you can convert all the lookup info to a proper table by using the Table Documenter in Access and a bit of Excel. (Use the documenter, with everything selected, then export to Excel and do some fiddling to extract just the table, field and rowsource values for all tables. The values are comma separated and in quotes but it's easy enough to split them into separate cells. Then it can all be imported back into Access as a new table.)

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum