AndrewBanfer
asked on
Open an Access Database and Fill a ListBox in VB.Net Code
How do I open an Access Database and Fill a ListBox in VB.Net Code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat! I'll give it a try.
Pat: This is VB.NET, not Access. .NET listboxes don't have properties like RowSource.
You can bind a Listbox to a datatable:
Dim dt As New Datatable
dt.Load(cmd.ExecuteReader)
YourListBox.Datasource = dt
YourListBox.DisplayMember = "SomeField"
YourListBox.ValueMember = "SomeField"
Also, the method I suggested would not require anyone to "remember to run an update procedure should the list ever need to change". It would update AUTOMATICALLY (caps intended). The method I suggested gets data from a database, so if the information in that database changes, the list would show that new data. Granted you may need to refresh after a data update, but you'd have to do the same if you were using an Access listbox and you added new data.
You can bind a Listbox to a datatable:
Dim dt As New Datatable
dt.Load(cmd.ExecuteReader)
YourListBox.Datasource = dt
YourListBox.DisplayMember = "SomeField"
YourListBox.ValueMember = "SomeField"
Also, the method I suggested would not require anyone to "remember to run an update procedure should the list ever need to change". It would update AUTOMATICALLY (caps intended). The method I suggested gets data from a database, so if the information in that database changes, the list would show that new data. Granted you may need to refresh after a data update, but you'd have to do the same if you were using an Access listbox and you added new data.
@Scott,
Please excuse my confusion. I realize the question asked how to do this with .Net code but Andrew seemed to want the solution to affect the actual Access application rather than a .Net form connected to Jet/ACE. I prefer your second solution to the first if in fact this question is related to a .Net form.
@Andrew,
If you are not working with an Access application, technically you are using Jet (.mdb) or ACE (.accdb) as a database engine and you do not even need to have Access installed unless you want to use the Access GUI to act as the SSMS for the Jet/ACE database. Your client PCs never need Access installed unless you want to allow them to modify the schema. Without Access installed, All you need to have installed is the Jet or ACE database engine which is free. you would simply use ADO or DAO to create/modify schemas if you didn't have Access the RAD tool installed for you to use as a schema maintenance GUI. When using Jet/ACE as a datastore, for .Net or any other FE application, you treat the Jet/ACE database exactly as you would any other RDBMS. You need to write your queries using "Access" SQL Syntax and you need to figure out the correct connection string but otherwise, ODBC is ODBC.
Please excuse my confusion. I realize the question asked how to do this with .Net code but Andrew seemed to want the solution to affect the actual Access application rather than a .Net form connected to Jet/ACE. I prefer your second solution to the first if in fact this question is related to a .Net form.
@Andrew,
If you are not working with an Access application, technically you are using Jet (.mdb) or ACE (.accdb) as a database engine and you do not even need to have Access installed unless you want to use the Access GUI to act as the SSMS for the Jet/ACE database. Your client PCs never need Access installed unless you want to allow them to modify the schema. Without Access installed, All you need to have installed is the Jet or ACE database engine which is free. you would simply use ADO or DAO to create/modify schemas if you didn't have Access the RAD tool installed for you to use as a schema maintenance GUI. When using Jet/ACE as a datastore, for .Net or any other FE application, you treat the Jet/ACE database exactly as you would any other RDBMS. You need to write your queries using "Access" SQL Syntax and you need to figure out the correct connection string but otherwise, ODBC is ODBC.
The second method still requires you to open a connection and command object, so the only thing you save is the loop. It's more a matter of preference than anything else. There are other ways to do it as well - create a BindingSource, or a Class, or an Array. Each has their own pros and cons, and none are "better" than any other.
Not sure how a discussion of what ACT/JET is, or how it's installed, or how you work with schema changes, is relevant.
Not sure how a discussion of what ACT/JET is, or how it's installed, or how you work with schema changes, is relevant.
It's relevant because Andrew thinks he is working with Access and you think (and I do as well) that he is not working with Access. He is in fact working with Jet or ACE (we don't know which) and Access doesn't even need to be loaded to work with Jet and ACE unless you want to use the Access GUI to manage the database schema much as you would use SSMS to manage the schema for SQL server. People who don't develop with Access tend to think that Jet/ACE ARE Access and so confusion reigns.
ASKER