How to connect Visual Basic to MS Access

This tutorial will guide you to connect your visual basic 6 to ms access database for you to develop a database application in visual basic.

What we need?

In this tutorial we need ms access database (2000-2003 format) (.mdb), a Visual basic form and a Module

1. Create an ms access database and save it as Data.mdb

2. Kindly open your visual basic 6 program.

3. On the file menu, select New Project and select Standard Exe.

4. By default there is only one Form (Form1).

5. Rename that form into SplashFrm. (you can choose any name you want)

6. In the project explorer, right click and add a Module. See the image below.

Project Explorer

Project Explorer

7. Rename that module into DBConnect. (you can choose any name you want for this module)

8. Before we write the code that connects our visual basic program to ms access, we will first add the Microsoft ADO 2.8 library in our visual basic.

9. To add the Microsoft ADO 2.8 library, in the menu bar select Project and select References.

10. References dialog box will appear, from the list of references, check the Microsoft ActiveX Data Objects 2.8 Library and click OK.

How to add Microsoft ActiveX Data Objects 2.8 Library

How to add Microsoft ActiveX Data Objects 2.8 Library

11. Open the Module that you have created (DBConnect) and paste the code below.

Public rs As New ADODB.Recordset

Public conn As New ADODB.Connection

Public sql As String

Public ConString As String

Sub Main()

On Error GoTo errHandler

ConString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=admin;Data Source=" & App.Path & "\Data.mdb;Jet OLEDB:Database Password="

conn.Open ConString

With rs

.CursorLocation = adUseClient

.CursorType = adOpenDynamic

.LockType = adLockOptimistic

End With

'name of your form

SplashFrm.Show

Exit Sub

errHandler:

MsgBox "Error Opening " & MSDatabase & vbNewLine & Err.Description, vbCritical, "Can't establish a connection"

End Sub

12.  After you have inserted to code above, in the menu bar select Project and click Project1 Properties (Project1 may vary depending on the name of your visual basic project).

13.  Project Properties dialog box will appear, in the Startup Object kindly select Sub Main.

Set the Start-up Object

Set the Start-up Object

Sub Main is the name of the procedure in which we have placed the code that connects visual basic to ms access.

14. You are all set to run your project, press F5 to run the project.

15. The SplashFrm form will appear indicating that you have successfully established a connect to your ms access database.

Note 1: your vb project (.vbp), the executable file (.exe), the form (SplashFrm), the module (DBConnect) and the ms access database (Data.mdb) should be on the same directory or folder.

Note 2: You may wonder that the ms access database has no table, for now we don’t need tables, fields and records. This tutorial will only create a connection between visual basic and ms access.

Note 3: Visual Basic can connect to other database application such as MySQL, SQL Server and PostgreSQL.

This is the very first step on building a database driven application in Visual Basic using MS Access as the backend or the database.

The source file of the above tutorial is also available for download.

We will also try to upload the video tutorial of this article. So stay tuned and watch out for it.

For feedback, inquires and suggestions feel free to leave your comments below. Thank you.

Comments

comments

One Response to How to connect Visual Basic to MS Access

  1. B Anne Salvacion says:

    Thank you so much ♡♡:-D YA I will keep visiting this site for more updates…
    I hope that you will have some tutorial on how to connect java netbeans to ms access.
    Thank you so much for your response. 😀

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.