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
04-20-2004, 07:33 AM
Use SQL, build a query.
To get started with SQL, review this site:
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.)
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.
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.
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.
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.)