Open an Access Database and Fill a ListBox in VB.Net Code

AndrewBanfer
AndrewBanfer used Ask the Experts™
on
How do I open an Access Database and Fill a ListBox in VB.Net Code
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can use the OLEDB namespace to do this:

Using con As New OleDBConnection
  con.ConnectionString = "Provider=Miscrosoft.ACE.OLEDB.12;Datasource=Full path to your db")
  con.Open
  Using cmd AS New OleDBCommand
    cmd.Connection=con
    cmd.CommandText = "SELECT * FROM SomeTable"
    Using dt As New Datatable
      dt.Load(cmd.ExecuteReader)
     '/ now fill your listbox
     For each dtr As Datarow in dt.Rows
      YourListbox.Items.Add(dtr("someField")
     Next
    End Using
  End Using
End Using

You'd have to import the OleDB namespace to your code page:

Imports System.Data.OleDb

You'd have to get the right connection string. See below for information on that:

https://www.connectionstrings.com/access/

Author

Commented:
Thanks Scott!
Distinguished Expert 2017
Commented:
I see you have accepted a code solution but a better solution if the contents of the listbox are variable is to bind the RowSource to a table or a query.  That way no one has to remember to run an update procedure should the list ever need to change.  The user should have a form that allows him to add/change items in the list (but probably not delete them unless they've never been used).  When ever the source of the RowSource query changes, the list also changes AUTOMATICALLY.  No code required.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks Pat!  I'll give it a try.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Distinguished Expert 2017

Commented:
@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.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Distinguished Expert 2017

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial