question

PeterHibbs-9836 avatar image
0 Votes"
PeterHibbs-9836 asked SimpleSamples commented

How to use MS Access database with VB.NET 2019

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.

windows-forms
· 8
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

Which project type have you created ?

OleDbConnection is in System.Data, added automatically as Reference in a .NET Framework project with VS 2019


0 Votes 0 ·

Actually OleDbConnection is in System.Data.OleDb

0 Votes 0 ·

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.

0 Votes 0 ·

[Try to reply... in Reply option]

Yes, for example, in a new ".NET Framework 4.7.2" project, I have :

79951-systemdata-2.jpg


0 Votes 0 ·
systemdata-2.jpg (86.5 KiB)

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.

0 Votes 0 ·

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.

0 Votes 0 ·

The code I posted works. I tested it.

0 Votes 0 ·
Castorix31 avatar image
0 Votes"
Castorix31 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SimpleSamples avatar image
0 Votes"
SimpleSamples answered SimpleSamples edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterHibbs-9836 avatar image
0 Votes"
PeterHibbs-9836 answered Castorix31 commented

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Castorix31 avatar image
0 Votes"
Castorix31 answered

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







5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PeterHibbs-9836 avatar image
0 Votes"
PeterHibbs-9836 answered Castorix31 commented

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

Peter.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

See the screen copy I posted : you must add a Reference to System.Data first

(as I said, it is added automatically on my VS 2019 version, but if it is not in your case, you must add it manually ([Right-Click] on References, [Add Reference...].))

0 Votes 0 ·
PeterHibbs-9836 avatar image
0 Votes"
PeterHibbs-9836 answered SimpleSamples commented

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.



· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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 (???)





0 Votes 0 ·

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.



0 Votes 0 ·

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






0 Votes 0 ·
Show more comments
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

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.

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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

SQLite is useful for single-user applications.

Totally agree.

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

Again, agree.


0 Votes 0 ·
PeterHibbs-9836 avatar image
0 Votes"
PeterHibbs-9836 answered SimpleSamples commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·