How to use MS Access database with VB.NET 2019

Question

Hi Experts,

I am trying to write a simple word game in VB.NET 2019. I have created an MS Access database file with a table that holds several thousand words and I need to be able to copy one or more words into the game. I am familiar with SQL in Access but I need to know how I can connect the game to the database. I have Googled the problem, of course, but the answers found are either very complicated or fairly simple but don't work in my code. Initially, what I would want to be able to do is choose a word at random from the database (based on word length) and also be able to test if a word entered by the player exists in the database.

Peter Hibbs.
Novice VB.NET user.

Answer 1

You can adapt one of the basic samples returned by Google, like :
SEARCH IN MS ACCESS RECORDS USING VB.NET
How to search in Ms Access Database records using VB.net | Search records in database
etc...

Answer 2

First let me say that SQLite is installed by all current versions of Windows. It could be useful for this instead of Access. The following uses Access.

Also, you probably should have added a tag for VB.Net to make it more obvious that you want that.

I created a blank Windows Forms project using VB.Net. I double-clicked the form to create a Form1_Load event handler. I added Imports System.Data.OleDb to the top of the source code file. I added a DataGridView to the form. I put the following in the Form1_Load event handler.

Dim cs As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Sam\Documents\db1.mdb"
Dim dt As New DataTable
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
Dim q As String = "Select ShowName from ShowsFall2014"
da = New OleDbDataAdapter(q, cs)
ds.Tables.Add(dt)
da.Fill(dt)
DataGridView1.DataSource = dt.DefaultView

You need to customize that as appropriate. To get the connection string, go to (view) the Server Explorer and right-click on Data Connections and select "Add Connection...". After creating the connection look at the database properties; a connection string is there, you can simply copy it to the clipboard and paste it into the program.

I am using VS 2017 but I assume the preceding works for VS 2019 too.

Answer 3

Hi SimpleSamples,

I have not heard of SQLite but I will look into it later (if I ever get that far with this project).

I don't know what "adding a tag for VB.Net" means so if this is necessary, then perhaps you could elaborate further.

I add your code to my project and it still flags up the OleDbDataAdapter items as 'not defined'.

To try and simplify things a bit I followed your instructions above and created a new project (which I called Database Test) and I did exactly what you said (apart from the pathname to the database file) but I get exactly the same result, the two OleDbDataAdapter words are underlined in red and the error message says they are undefined.

Perhaps VB.NET 2019 does not allow database access (which seems a bit unlikely) or else I am missing something very obvious (also unlikely) or maybe there is some setting in the project that needs to be set. As you can probably imagine, this is very frustrating, when I started this project I assumed that the database part would be relatively straightforward since Microsoft say that database access is built in to the system but it is anything but so far, for me anyway.

Do you have any other ideas as to what is happening or not happening and what I can do next?

Peter.

Answer 4

[I could not answer in comment...]

Visual Studio is saying that that the Imports line is not necessary and also that the OleDbConnection is not defined. So how do I get it defined?

Are you sure that the System.Data Reference is correctly added ?

Answer 5

I have this line Imports System.Data.OleDb at the very top of the VB window, is this correct?

Peter.