Accessing Access tables programmatically in Access 2000

[jgnasser]

I am beginner in Access programming. I have two queries:

1. I have two list boxes and one is populated with names of fields from a table within the access database. I would like to click on the first listbox (lstFields) and the values of the records in that field are added to the second list box (lstValues). This works fine in Access 2002 but not in Access 2000. I guess this is because the libraries are different. I am using a separate Funclion 'Additem' too because the listbox does not support additem method in access 2000. I tried to 'cheat it' and loaded Microsoft DAO 3.6 Object library but did not work, Access crashed instead. Any workaround?

Private Sub lstFields_Click()
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Incidents")

While Not rst.EOF
str = rst.Fields(lstFields.Value).Value
AddItem lstValues, str
rst.MoveNext
Wend

End Sub


2. I am running ArcMap ActiveX inside Access and would like to Access a table within the database and use to prepare a layer. I have done this by referencing the database from file using an ArcObjects code but this brings a complication in that Access sees that the db is being opened twice and the locking causes problems. Is it possible to access the table from within and have it accepted as an arcmap object supporting IFeatureWorkspace interface or just opent the table (recordset?) and pass it as an arcmap object supporting IDataset interface? Since both Access abd ESRI geodatabase use .mdb, I thought it would wok better. Please help.

Kind regards

************************
J. G. Nasser Olwero
Mpala Research Centre
P. O. Box 555, Nanyuki 10400, KENYA

Phone: +254 (0) 62-32758/1
Fax: +254 (0) 62-32750

Website: www.mpala.org

 

[VBITS]

Dare to call yourselve a Access Noob?? NO WAY... Sorry don't know the answer.

 

[Nouba]

AddItem is introduced in Access XP. You could try using the code in Use Classes to Enhance List and Combo Boxes in Access 97.

 

[Pat Hartman]

You can populate the second list box using a different technique. Use the following query as the rowsource for the first listbox -

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6) AND ((Left([MSysObjects].[Name],4))<>"MSys"))
ORDER BY MSysObjects.Name;

This will fill the first listbox with the names of all the tables. In the AfterUpdate event of the FIRST listbox, you need two lines of code -

Private Sub lstTableNames_AfterUpdate()
Me.lstColumnNames.RowSource = Me.lstTableNames
Me.lstColumnNames.Requery
End Sub

The first sets the rowsource of the second listbox to the selected table name from the first listbox and the second requeries the second listbox.

The rowsourceType of the second listbox needs to be set to FieldList.

 

[jgnasser]

Thanks Pat but what this does is that it populates my first listbox with the names of all the tables in the database (except system tables) and when I click on the listbox, it adds the name of the table selected from FIRST listbox to the SECOND listbox. However, what I want is that it populates the first listbox with the names of fields of a selected table and when I click on a field name in the FIRST listbox, it displays all the records for the selected field in the SECOND listbox.

How about my question2, any ideas?

Nasser

 

[jgnasser]

Any new ideas here?? still wondering how to make .openrecordset work in Access 2000

 

[Pat Hartman]

The second listbox should populate with the "fields" from the table selected in the first listbox.

when I click on a field name in the FIRST listbox, it displays all the records for the selected field in the SECOND listbox.

- I'm sorry but this request does not make any sense. Records are not part of a field.

 

[jgnasser]

Well it doest populate with the 'fields.

Private Sub lstTableNames_AfterUpdate()
Me.lstColumnNames.RowSource = Me.lstTableNames
Me.lstColumnNames.Requery
End Sub

in this, I assume you meant lstNames is the first listbox while lstTableNames is the second one and that's how I used it.

Sorry I meant the value for the particular field, not the whole record.

 

[SforSoftware]

Maybe this?

Private Sub lstFieldNames_AfterUpdate() 
  Me.lstColumnNames.RowSource = "SELECT " & Me!lstFieldNames & " FROM MyTable"
Me.lstColumnNames.Requery 
End Sub

Where lstFieldNames is the first list

 

[Pat Hartman]

Did you do this?

The rowsourceType of the second listbox needs to be set to FieldList.

If you want to have a third listbox fill with data values, you'll need to create a query behind the scenes and place it in the rowsource for the third combo and then requery the third combo.

 

[jgnasser]

Yes I managed to solve that. Thanks. I used this, not sure how efficient but it works. The problem is that when any value is missing (Null) from the field that I'm populating from, it gives me an error. No idea how to sort that. Also, how can I ensure that it only displays unique values in the second listbox? I need to kind of go though the values and remove all repeated ones thus if the selected field has values like 1,2,5,3,2,1,2,3,2,; I only want to have 1,2,3,5 in my listbox.

 

[SforSoftware]

Originally posted by jgnasser Also, how can I ensure that it only displays unique values in the second listbox? I need to kind of go though the values and remove all repeated ones thus if the selected field has values like 1,2,5,3,2,1,2,3,2,; I only want to have 1,2,3,5 in my listbox. [/B]

You can do that by using SELECT DISTINCT in your SQL-statement, instead of only SELECT