VB.NET Tutorial - Connect & Query a Microsoft Access Database [FULL]
Vložit
- čas přidán 27. 07. 2024
- In this VB.NET database tutorial I will demonstrate how to connect to a Microsoft Access Database, and how populate a DataGridView and a ComboBox with your query results. Additionally, we will create a wildcard driven database search.
PROJECT RESOURCES:
Access Database Engine 2010 Redistributable: www.microsoft.com/en-us/downl...
Project Source Code & Sample Database: www.dropbox.com/s/8s5ka47mqzi...
SEGMENTED TUTORIAL [4 PARTS]:
• VB.NET Working With A ...
Thank you for supporting VBToolBox!
Hello MR Big Island (AKA VB Toolbox)...
All my life I was passionated by programming and 20 years ago I started learning VB on my own.
I've accomplished some cool things along the way and thought I was pretty good at it.
Then I found your YT channel and you gave me a whole other (and way way way better) understanding of programming VB.
This has changed the game for me and I want to thank you for all the effort you put into making these videaos.
I've nearly watched them all and learn new things each time I watch them.
Enjoy the island and live life!
Awesome Tutorial!!!
I was almost exhausted and about to quit on developing Billing system and eventually found your tutorial as a life saver. Thank you!!!
This is an incredible piece of coding. I highly recommend you watch all 5 videos ... it will change your world! ... Thank you.
Wanted to user this style of Access DBase for a few years, instead I just learned ACCESS, and figured out how to remove the ribbons and the Main Database window, and leave it with just the form, worked ok, just didnt look great, and finally 5yrs later I found this... and its smooth as butter, just followed along with vs2019 and BOOM! worked.. Awesome Tut!
Also, because of the Structure you set up, I was able to plug in the combobox to do a user search in the same way as the text box, YAY!
Thank you, As a self Thought coder, This has been extremely well done. It filled a bunch of gaps for me. Your paste is just perfect, the content is A+. Thank You.
Just wanted to say Awesome Thank You! I have been developing, selling and supporting a program "Prism" for 18 years using VB 6 and 2 days ago installed VB Net 2019 and started try to learn how to use it to rewrite our program. We use MS Access databases to store all data and your video was the only one I found on CZcams where in 1 hour I could open all .mdb files local and across a network+++
Jim Ryan Awesome! Makes me happy to hear that this is seeing some practical use. Sounds like a dream job, too! 😄
I’m envious that you’re getting to play with the latest VB. I haven’t even been able to touch it since I made these tutorials.
I just have to say that this is still a good video tutorial. I had to change the project to compile for "Any CPU" and make a project reference "Microsoft Office 16 Access Database Engine Object" and it still worked perfect. Thanks
I have been working on a project for weeks. This video and several others from you have solved problems I have encountered and have had challenges overcoming. Excellent, excellent tutorials and explanations!!! Thank you
Thank you! I'm very happy that the tutorials have been a help, John! ☺
It's nice to know they're still useful to people even after so long.
This tutorial actually works, other people use the mystery controls/containers like "DataSet", "BindingSource", and "SQLDataAdapter", but he uses code showing what is actually happening. (You remember better when you understand.) He also explains why he uses "Show" event instead of "Load" and why.
Amazing video, perfectly explains everything.
Amazing ! Thanks you so much ;) The most amazing part is that you explain everything clearly so that when i do my presentation i could explain how i actually did it
First tutorial I have watched from you. This was exceptional! Thank you.
You are the best teacher.
thank you very much sir!! never thought one can handle database with one single database class. Thank you for source code as well..
Don't know if you will ever see this but... This video is probably the best I have ever seen on programming, bar none! Aside from the specific issues addressed, the pace is great, I can understand your accent (a huge issue on youtube IMHO), the explanations are clear.
Many years ago I started learning VB6 on my own. Now I am trying to come up to speed on VB.Net and the issues have always been the same: the ability to communicate in a way that is organized, clear, and concise. All of these go toward making the information interesting. Keep up the good work. I am looking forward to watching your other videos. How can we repay you?
Hi, Randy! [VBToolBox here]. Thank you for the great comment! I'm pretty technologically impaired right now, but still checking my email from time to time. It's still awesome to see the nice replies, even if I'm not able to help with all the questions.
Only 6 words! Thank you! Thank you! Thank you! 👍👍👍
You're very welcome! 😀 I'm happy that the videos were helpful.
You are the best of the best
Simple and easy
Keep going and don't stop!.
hello sir, ThankYou soo.. much for your tutorials, its the first time that i am going to code in vb.net for my final year bca project only because of your simple and easy tutorials.. thankyou 1's again
+asha varghese That's great, Asha! Thank you for the encouraging comment. :-)
Excellent tutorial and understandable. Needed this for searching external DB for my software app inside a created report form.
Your The Best Keep Going ! ^_^
Great Tutorial BOSS! I learned a lot of things, thank you very much!!!
Cheers mate!~
Thank you VERY much for this informative and detailed tutorial.
THANK YOU FOR MAKING THIS TUT. AFTER LISTENING AND WATCHING MANY OTHER TUTS. YOU ARE THE BEST OF THE BEST WAYNE LUNDALL 29-09-2017
Thanks for sharing your knowledge.
Thanx Man
You Explained Very Good
Thank you! Awesome Tutorial!
Thank you very much for your teaching !!
Keep it up dude, these videos helped me a lot!
Just what I needed! THX! A lot!
hi, really great video on database, thank you.
This is my best tutorial, thank you very such
This is so good! You are the best explainer ever! Helped me alot!
Thank you very much! Thump UP!!!
You made my day perfectly explain what i require Thank you
One word: PERFECT
Excellent Video. I have several $200 textbooks that don't cover what you did in this video.
$200 text books?? :-O I need to start writing textbooks! lol I'm glad that you found the tutorials helpful. :-D
A number of years ago I created an application in VB6 that created and managed a database for the user. The user could have the software create multiple databases. I never got the opportunity to distribute that application.
Now I'm partially disabled and have been trying to update my skills to .NET. The vast majority of the instructional material I've found is nearly worthless if you want to actually create a database application that actually does something. Most use wizards to create databases and connections, etc. Oh, the texts and MSDN guides cover For-Next Loops and IF-THEN-ELSE structures, some even go into working with text files.
When looking for help people love to talk down to me and explain that VB is now an "Object oriented language". Freakin duh... If all the tutorial materials explain the differences between VB 2015 and VB 2013 that's not much help learning the objects. Sorry, I have this soapbox I can't resist climbing on :).
I plan to go through the remainder of your .NET tutorials in the very near future.
It's great that you're bolstering your .NET skills, Asesinos!
I cut my teeth on VB6 many years ago and ran into the same sort of "community support" road blocks. My stubborn nature sustained me through those dark ages of criticism and useless information.
While I can't claim to be an expert on every topic and my time is extremely limited of late, I always welcome questions and try my best to respond as I'm able. Even if I don't have an immediate answer, it can sometimes be a lot of fun to work on a problem and learn as we go.
Thank you for this. I always had problems understanding how that worked. Suddenly, it just got clearer :O)
+Angelo Gallo Excellent, Angelo! I'm glad that the videos are helping. :-D
Hi, is there any way to get in contact with you? I've been using your dbcontrol class but it doesn't seem to work with my code (Access 2013-16 and VS 2015 CE on Windows 10). I've been comparing source code from a previous program I made with your code (Access 2013 and VS 2013 on Windows 7 Pro) and the new one, and I can't, for the life of me, see what I'm missing.
Since I've seen your videos, you are my go to guy when I run into problems. Thanks infinitely for your helpful videos.
Hello, Angelo. Best way to contact me is via CZcams, though if you want, you can send me a private message with your email and I can contact that way when my work load slows.
Unfortunately, I don't have any way of testing on Windows 10, so my knowledge there is not the strongest. I do have a copy of VS 2015 and office 365, so I might be able to figure it out if I know what's happening.
Wow thanks for the quick response. As I was writing this message to you, I saw my mistake and fixed it. It now works... and I feel so dumb LOL... You are the best! Keep up the amazing work. Looking forward to your next video.
Awesome! Glad you got it sorted. :-) Always happy to help if I'm able.
Great video. I kept having problems with the connection statement. I have Access installed as a part of Office 365. I assumed (incorrectly) that I would not need to download the redistributable database engine, but as soon as I did, everything was OK.
I did find some posts that there were differences in the Access "regular" vs. the Access from Office 365. I went through your entire SQL Server and VB recently and I think it worked right out of the box. I hope you are doing well in your new off-the-grid environment.
Irvin Johnson Thank you! 🙂 It’s been tough, but an adventure, for sure! I’m glad you were able to get it sorted for 365. Perhaps this will help others.
Great job. Thank you so much.
Great tutorial. Thank god that I have an EQ available to cout low frequencies. :)
🙂 Very Helpful. Thumb-up
Thanks a lot for this. :D
Really helpful!!
Thanks a lot!!
+Shaunak Sengupta Very happy that you found it helpful, Shaunak! Thank you for the feedback. :-)
Thank You for making this video.. So Helpful Teacher
thank you really help a lot!!!!
very nicely explained . My code worked
realy good!
+colorpaper princess (colorpaperprincess) Thank you! :-) I saw your artwork and you are very talented, colorpaper princess.
Thank You So much!
thanks very very very much for your super tutorial
thanks so much , have some tutos with 2 related tables access in order to show to deal with delete and insert command
great friend, success always
Very Helpful And Correct ... Thank You ♥
First of all, thank you for your great and helpful tutorials. I have one main question. I followed your procedures in video "VB.NET Tutorial - Connect & Query a Microsoft Access Database [FULL]" to create my database and get queries. I also inserted data based on your "INSERT Records into a Microsoft Access Database" film. A major challenge happens to me, that is, the different access files used for these two procedures (query and insert). In other words, when I wanted to query, the access file placed in the following address was used
C:\Users\....\Documents\VisualStudio2012\Projects\WindowsApplication1\WindowsApplication1\app2.mdb
And inserted data were saved in the following file:
C:\Users\....\Documents\VisualStudio2012\Projects\WindowsApplication1\WindowsApplication1\bin\Debug\app2.mdb
How can I merge these two files???
very interesting loved it
Very helpful..thank you
In the DBControl Class you hardcode the provider and the source. Any idea on how to keep the source as a variable? Meaning, I want to change databases based on user entry once the first Form loads. Thanks
Wonderful !
Really helpful and complete, one thing I'd like to know, how do you modify the fonts of the elements appearing in the datagrid pls ?
hi
great videos, learned a lot from these tutorials
is it possible to make a video on MS access relational data base.
thansk
Thank you soooo much sir ^_^
Wow....good tutorial.....tks
LOVE U MAN
I use your DBControl in my application and it works fine; however, it doesn't provide a method for saving changes in a datagridview back to the original Access database. Do you have a method for this?
excellent tutorail
Thank you! :-)
really good.
How can I add a datareader function and return a string or something within the DBControl? TIA.
Nevermind... I saw your another tutorial, very informative thanks!
Ps. Great tutorial! :)
On the fill combox routine is there a way to have it not populate with duplicate names? I'm using the database as a log for our company so the only thing in the database that is unique is the ID Column. A specific user will be listed ten or fifteen times per night. I would like to have the users name only show up once, so that when I click on the name I can have it show all tasks that particular user performed throughout the night. Thanks
There sure is. In your SELECT use SELECT DISTINCT yournamecolumn. This will eliminate duplication.
Hello, may i know the difference between access file in debug folder and the ones in the main project?
can i know the syntax error how to solve? how can i increase the argument at that addParam .when i add more than 3 parameter it will show me oledbparameter canot accept too many argument
Please help , the combboxox populates only the DisplayMember, how to get the ValueMember of the combobox .
mins has "syntax error in FROM clause" how can I fix it? TIA!
Can you give me a link if you have any of among these videos you have loaded which deals with relating access database tables.thank you
hi,
My data base is within the debug folder however when i execute the access.exception query its saying that it could not find the file, any suggestions?
This is very helpful to my project thank you so much😊
But I have a question,
When I create again for my next form I follow your instruction again but when I run it my search is not functioning even I type the name the datagridview still not changed. And also no error appearing. What is the problem?
100% clear
hi ! thanks for the good explanation!
But i could'nt make image of theses syntax :
why we need : Param, dataadapter, dataset,
in vba we use rs, that maybe dataset.
others syntax didn't make sense to me until i get a rational explanation .
rational explanation mean logical explanation with real life stuff .
we get box as db and inside that box a smaller box as table and the place where we put the box is the room . so i couldn't see the use of param and adapter since we tell the class to import necessary tools to deal with other detail! in the first line of your code.
sorry if my question is not welcome !
thanks for this tutorial.......can you please make a tutorial on hospital management system in vb.net with sql connection?
Hi in this coding how we can provide column size for each item??~
getting this error on Access.AddParam() function
System.NullReferenceException: 'Object reference not set to an instance of an object.'
Access was Nothing.
How would you make it so that the parameters are CaSe senesative
Hi! Here to annoy you once more.
I have a form, I want users to fill a few mandatory fields (not all fields are mandatory).
How would I go about using errorProvider to show(and disappear once filled) the mandatory fields?
Or is there another way to show mandatory fields? i.e. highlight labels, turn fields red?
Again, thank you in advance!
Sir, How can i use DBControl Class to create reports. It doesn't allow me to do so!!!
how can i check if the data already exists? @VB Toolbox
Im having problem on this tutorial. I followed all the steps and its working but the data show in datagrid has not been reflected on ms access database. Can you help me solving this problem. Thanks
Yow. Six years later the dude still has the source code in the dropbox folder. How good is that? This guy is okay.
I really need to get it together one of these days and go through all my old video links and see what's broken. 😅
hi sir , when i make setup for project include connect between database access and data grid view vb.net 2010 and when install on another computer .display massage that cannot know the path of database can you make video to explain this problem ....thanks with best regard
Nice work. Small suggestion. Put this
Public Function Failed()
Return Not String.IsNullOrEmpty(Exception)
End Function
in your DBControl. Thanks.for the help.
+Joe Gillon Thank you, Joe. I actually have just recently implemented this same thing in my latest Access and SQL Control classes, but I haven't had a chance to release any new videos yet.
This is what I am using now:
' ERROR CHECKING
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
Hi ! these videos are absolutely great! Thank you!
Quick question, if i want to fill the combo box with other information i.e. names in my user table, how would i do it?
Thank you very much!!
+Eximious This is covered starting at 39:06 in the video. :-)
+VB Toolbox Thank you very much!!
I've actually got another question, a request more like, hopefully you can help me with it,
I have a primary key in ms access, with long text data type (i.e. INC0001). as you can see its a mixture of letters and numbers, how would l go about incrementing this when creating new records in the table?
Thanks for everything!!
+VB Toolbox Probably a big ask on that last question I know ^_^ but here a function i thought of for a start:
Function getDigits(s As String) As String
Dim retval As String
Dim i As Integer
retval = ""
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1)
+Eximious Sorry for the late reply. It's been chaos here. This morning I was goofing around with this idea and came up with a function that can adjust to any char:number key length and increment it.
Example:
CN
CCN
CNNN
CCCNNNN
etc.
Let me know if you want to try it out. :-)
+VB Toolbox That's no problem at all! I definitely would liketo know the method you have used to get that working, and truly grateful for working on it and helping me out. However, I seem to be struggling, somehow, on the update/edit part.
More specifically, I want to update a selected row on the data grid.Right now, it is similar to yours, in terms of the data grid. Its only selecting a few of the fields I have in my access db. However I want to run another query selecting all fields and showing those in another form. Once it is all showing in another form and are in the right text boxes I want to update that record.
Can you help me with this one instead?
I think the one above with the alphanumeric ID is something I really want to implement once I have a fully functioning add/update/delete capabilities :)
I have sent you so many comments in this video and the other one, hopefully I havent been too annoying!
p.s Would love to show you my code somehow!
Thank you very much!!
sir i am not able to connect ms access with vs 2019 community i tried by installing database engine also but still i am getting error microsoft.ace.oledb.12.0' provider is not registered on the local machine.plz help me
How to make it work on network say LAN so that many people can can work on it simultaneously.
First, Thank you so much for this great video! You're an amazing teacher!
My Question is. What if my database has multiple tables. What would i do to switch between tables and update them??
Create 1 .mdb file and link all .mdb files you need into that 1 file. Open that 1 file and you have access to all files
I am getting an error message at line
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
Message is: System.NullReferenceException: 'Object reference not set to an instance of an object.'
How can I fix this problem
This usually means that you are trying to reference an object or variable that was not instantiated with "New".
Example: Dim DBCmd as New DBCommand
Great tutorials. I tried the code exactly as you wrote it but the wildcard search didn't work.
Hello Sir!! good day... I've been following your tutorials lately but here's the thing.. i want to do a filter that will list according to the columns of the DGV can you help me in this? i tried so many things like deriving a dataset and such but nothing happens in my DGV.. Thank you for the Reply and God Bless..
Thanks for a realy very good introduction , my question is , how can i change the name of column in datagrid , becouse with this code doesnt can from properties , i tried with this dgvData.Columns(2).HeaderText = "Percentage" but it doesnt works , give me a fault , any suggestion? thanks a lot
+Eletheria Kourteridou If the column is pulled via your query you can change the column header in the query itself.
SELECT myPctColumn As Percentage FROM sometable
If you have spaces or protected keywords make sure that you use brackets for your columns:
SELECT [myPctColumn] As [Percentage] FROM sometable
thanks for your answer, it works perfect . Can you have any project with multiple related tables in Access ? for example a CRM soft with customers , products , sales etc. that is a very interesting project and anyone can learn so much....... Thanks again
+Eletheria Kourteridou Absolutely! :-) You can use relational queries in Access.
Example:
Access.ExecQuery("SELECT salesmaster.customer, salesmaster.ordernumber, salesmaster.orderdate, salesitems.partnumber, salesitems.quantity, salesitems.unitprice, salesitems.extendedprice FROM salesmaster INNER JOIN salesitems ON salesmaster.ordernumber = salesitems.ordernumber WHERE salesmaster.customer = 'my customer' ")
I've been struggling to do exactly what this video describes. I wish I saw this video 3 months ago.
Please I love your tutorials. I have subscribed and shared them with my friends who have also subscribed.
Can you help me with how to check duplicate records in the database. I created Access and Vb database management system following your tutorials. Thanks
Could not find file 'C:\Users\i express\Desktop\practice\practice\practice\bin\x86\Debug\connect.accdb'......i keep getting this error , i really dont know where it's coming from.
Something in your project is looking for the database in your Debug folder. When your project is compiled, your template database should be automatically copied to the folder. Maybe check the properties of your access database in the Solution Explorer and verify the source and output paths are correct and that you have full permissions in that directory.
sir,thanking you very much, that you teach a very simply and usefull ,sir i want know how to use date data and numeric data to save to acces database ,in foxpro i use str() for numeric expretion and ctod{} for date expretion
Thanks