How to use MS Access database with VB.NET 2019

Peter Hibbs 101

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.

Peter Hibbs 101 Reputation points

2021-03-21T14:21:47.273+00:00
Hi,

Sorry, that did not help much. I tried the first video and added the code below to my project as instructed by the video.

Imports System.Data.OleDb

Public Class Form1

Dim con As New OleDbConnection

Firstly, 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?

Peter.
Castorix31 90,681 Reputation points

2021-03-21T14:33:06.69+00:00

Which project type have you created ?

OleDbConnection is in System.Data, added automatically as Reference in a .NET Framework project with VS 2019
Peter Hibbs 101 Reputation points

2021-03-21T15:03:40.953+00:00

When I originally created the project I chose 'Windows Form App (.NET Framework)' in the Create New Project form (if this is what you mean). Was this not correct?

Peter.
Castorix31 90,681 Reputation points

2021-03-21T15:24:36.25+00:00

[Try to reply... in Reply option]

Yes, for example, in a new ".NET Framework 4.7.2" project, I have :
Peter Hibbs 101 Reputation points

2021-03-21T15:57:01.96+00:00

Hi,

If I move the Dim con As New OleDbConnection line to the Form1_Load event it is still underlined in red and says it is not defined.

Peter.
Sam of Simple Samples 5,546 Reputation points

2021-03-21T19:54:09.217+00:00

Actually OleDbConnection is in System.Data.OleDb
Sam of Simple Samples 5,546 Reputation points

2021-03-21T19:56:56.11+00:00

I have Imports System.Data.OleDb at the top of my Form1.vb file and I do not get the error for Dim con As New OleDbConnection.
Sam of Simple Samples 5,546 Reputation points

2021-03-22T03:23:24.02+00:00

The code I posted works. I tested it.

8 answers

Peter Hibbs 101 Reputation points

2021-03-22T14:53:37.88+00:00

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.
Please sign in to rate this answer.
Castorix31 90,681 Reputation points

2021-03-22T15:06:09.06+00:00

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

What you describe is a .NET Core project, although you said you used a .Net Framework project (???)

Peter Hibbs 101 Reputation points

2021-03-22T15:31:14.937+00:00

OK, for the game project I did use the .Net Framework option but for the Database Test project I used the Windows Forms App (.NET) option. Would this be the one to use for a simple database program? There are so many to choose from with no indication of what each does or doesn't do!

Peter.

Castorix31 90,681 Reputation points

2021-03-22T15:45:03.107+00:00

OleDb is mainly used with .NET Framework
You can use it with .Net Core, but you must install the package System.Data.OleDb
(from [Tools] [NuGet Package Manager][Manage Nuget Packages for Solution...] then Browse and typing System.Data.OleDb to find it and install it

Peter Hibbs 101 Reputation points

2021-03-22T16:36:47.993+00:00

Hi Castorix,

OK, thanks. I have downloaded that file on my game project and that has fixed the 'Not Defined' errors which is good.

I have added in the code snippet (from SimpleSamples above) as a test but when I run the code, it fails on line 8 da.Fill(dt) saying that it cannot find the database file. The pathname is correct but I am not familiar with how this code works. Have you got any suggestions on what could be wrong?

Thanks,

Peter.

Sam of Simple Samples 5,546 Reputation points

2021-03-22T16:46:24.213+00:00

The problem is likely the the connection string. Did you get the connection string in the manner I described? If you did not change the Data Source in the connection string in my sample code then that definitely won't work.

Peter Hibbs 101 Reputation points

2021-03-22T17:02:02.917+00:00

Yes, the pathname is definitely correct (I did do what you said), however, I misread the error message. It actually says "Unrecognized database format 'C:\Drive G\VSI 2019\My Game\Word Data.accdb'.". Does VS recognize the .accdb format or does it have to be .mdb or is there some other issue?

Peter.

Sam of Simple Samples 5,546 Reputation points

2021-03-22T17:29:28.583+00:00

Search the internet for Unrecognized database format accdb. There are many possibilities. You might need to install Microsoft.ACE.OLEDB.12.0. Or you might need to install a 32-bit or 63-bit version of the Access library. There are other possibilities.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2021-03-22T17:44:53.023+00:00
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.
Please sign in to rate this answer.
Sam of Simple Samples 5,546 Reputation points

2021-03-22T18:04:15.247+00:00

I think your Who should use TableAdapters in the GitHub repository is very relevant. I think it would help to add that a major part of the frustration is that the majority of TableAdapters is generated code and therefore not documented well.

I agree that SQL Server is a good choice for most uses. SQLite is useful for single-user applications. It is my understanding that updates for the database for this application will only be relevant to the one user. That is how Windows uses SQLite.

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2021-03-22T18:52:35.567+00:00

SQLite is useful for single-user applications.

Totally agree.

the majority of TableAdapters is generated code and therefore not documented well.

Again, agree.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Peter Hibbs 101 Reputation points

2021-03-22T22:48:21.39+00:00

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.
Please sign in to rate this answer.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2021-03-22T23:15:12.847+00:00

FYI RecordSets are very old methods for working with data and should be avoided at all cost in VB.NET code and should only be used inside of MS-Access. Records sets are a hang over from VB6 coders when current methods did not exists.

Sam of Simple Samples 5,546 Reputation points

2021-03-22T23:23:47.28+00:00

I think you can do that. I assume you do not need to write the flags out. I am not sure but you probably do not need to store the word length with the words. You probably can just use a text file. Or you could use XML or JSON or a delimited file such as CSV. XML can be serialized (read) directly into a DataTable. For your current requirements just use a text file and load it into a DataTable. Each record would just be a word and then you can generate the word length when you load a word and create the flag.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to use MS Access database with VB.NET 2019

8 answers

Notes

Your answer