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

Hi Experts,

I have looked at the various links (which I have bookmarked for future reference) but I am thinking now that trying to use a database to hold a few thousand records is far too complicated for my simple requirements.

Looking through the Web pages that you kindly showed me, I came across the DataTable object which seems to be a lot simpler and basically, as far as I can tell, gives me all the facilities that I would need. I have a single table with 1 Text field (which holds 4, 5 and 6 letter words), 1 Number field (which holds the word length) and 1 Boolean field which just flags up if the word has already been used in the current game. Providing I can find a way to copy the data from the database into this table (and I believe the table is saved to disk once it is populated) then I can use standard RecordSet functions to search and fetch data which I am already familiar with in Access.

If anyone knows of any reason why this would not work then please let me know (and why) but hopefully I can get it working once I have researched it some more (although I am sure I will be back with more questions at some point).

Anyway, thanks for all your help, this has been a bit of learning curve but I have found out a lot more about VB.NET 2019 over the last few days which I am sure will be useful in the future.

Peter Hibbs.

Answer 2

Here is a wealth of resources for working with VB.NET with MS-Access and note even though there is a lot of code samples I don't cover every single aspect but there is more than enough to get you beyond started.

• See my GitHub repository for working with VB.NET with MS-Access .accdb. With a three part series on Microsoft TechNet, part 1, part 2, part 3.
• Using TableAdapter, see this GitHub repository

  Notes

• If open to ideas, consider using SQL-Server even after working with MS-Access as code-wise little difference while major differences/benefits is that SQL-Server offers more features in regards to SQL, easy to back-up. This GitHub repository has code samples for moving from MS-Access to SQL-Server via VB.NET.
• For MS-Access to work you need to install the Access database engine. I'm on Windows 10 with the 32bit version of the engine.

Answer 3

OK, there is no References option on the menu but if I click on the Project item the drop down box shows 3 reference options -
Add Project Reference
Add Shared Project Reference
Add COM Reference

If I click on Add COM Reference I get a new form which shows a million (or thereabouts) references some of which seem to be database related (Microsoft Access 16.0 Object Library, Microsoft DAO 3.6 Object Library, Microsoft Data Access Components Installed.., etc, etc to name but a few), should I be selecting one (or more) of these?

Peter.

Answer 4

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

Peter.

Answer 5

[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 ?