Microsoft Access and Visual Basic 6.0 Connectivity using self made Module

Got problem in Database connection in visual basic 6.0? Well, if you do understand the basics of visual basic, and knowledge in SQL commands, you can manipulate your database in any ways possible. I have created an easy step by step tutorial for you.
1. Open your MS Access and name the database as you want to be (e.g. MyDbase.mdb). Create your tables (e.g. LoginTable) with fields (e.g. username and password) and save it in your folder (e.g. c:/Test/).


2. Next stuff? Open your visual basic and create a design in your form of log-in.

3. After you have created a design, Click on Project in the Menu bar, choose Preferences and check Microsoft ActiveX Data Objects 2.0 Library, This is to include the link library for the database connection that we will be performing later.

4. After that, we will be creating our module for database connection. What is the help of this module? It is for us to minimize the time of coding in our forms and just call the function stored in our module. How to create a module? Click on Project, then Add Module

5. Just copy the code below and in your new module as it is
'This is gSQL and gDML updated version 1.1 released
'includes new features like gTestEMpty,gGetInfo,gGetReCSeT,gAPPendInFo
'wallyibong.blogspot.com

Public dept As String
Public zq As Recordset
Public getval(25) As Variant
Public getstud, getstudno As String
Public studid As String
Global total

Public Function gSQL(ByVal strSQL As String) As Recordset
Dim cnn As Connection
Dim rs As Recordset
Dim cmd As Command
Dim strcnn, con As String
Set cnn = New Connection
Set cmd = New Command
Set rs = New Recordset
Dim a As String

'connection string for sql server 2005 database
‘strcnn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa; Password=Wally;Initial Catalog=clearanceSystem;Data Source=WALLYBOY"
'connection string for Microsoft Access 2003 database
strcnn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\myDbase.mdb;uid=admin;pwd=;"

cnn.Open strcnn
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.Open cmd
Set gSQL = rs
Set rs = Nothing
Set cmd = Nothing
Set cnn = Nothing
End Function
Public Sub gDML(ByVal strSQL As String)
Dim cmd As Command
Dim cnn As Connection
Dim strcnn As String
Set cmd = New Command
Set cnn = New Connection

'connection string for sql server 2005 database
‘strcnn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa; Password=Wally;Initial Catalog=clearanceSystem;Data Source=WALLYBOY"
'connection string for Microsoft Access 2003 database
strcnn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\myDbase.mdb;uid=admin;pwd=;"

cnn.Open strcnn
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute
Set cnn = Nothing
Set cmd = Nothing
End Sub

6. Seems tricky and mind boggling but just Save your module as dbase.bas

7. Just to explain the code briefly, the above function is for PUBLIC SUB GSQL is for searching/retrieving records only in our database, the PUBLIC SUB GDML function is for Deleting, Updating and Inserting records purposes.

8. If you are using SQL Server Database, the connection string is already given in the module as commented but you have to change the following:
strcnn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa; Password=Wally;Initial Catalog=clearanceSystem;Data Source=WALLYBOY"

ID = (The Id of your SQL Server), My default ID is sa
Password = (The password of your SQL Server)
Catalog = (name of the database you have created in SQL Server where the tables are stored
Source = (The name of your server)

9. While if you are using Microsoft Access, the connection string is also given, just commented because you cannot use two databases at a time.
'connection string for Microsoft Access 2003 database
strcnn = "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\myDbase.mdb;uid=admin;pwd=;"

Dbq= (where your database should be located)
Uid = admin (yeah, don’t change it, it is always admin)
Pwd =if you have password (in my case, I haven’t any)
10. Finished. You can connect now to your database? But how can we use the module?