Category Archives: Visual Basic 6.0

Saving values to registry in VB6


Visual Basic simplyfies the way you can store and retrieve values. Usually you can place value in configuration files with easy code. If you want to go more advanced , can keep them at Windows Registry, were the OS keep track of its ihabitants.

Visual Basic 6 allows you store and retrieve values by using SaveSetting and GetSettings methods

Call SaveSetting("MyApp", "Software", "Key", "12312AZ90")
Print GetSetting("MyApp", "Software", "Key")

YouTube Video

Data entry in MSHFlGrid control vb6


MSHFlex Grid[Visual Basic 6.0] control is a goog tool for showing data in a tabular format as in Excel.2018-08-31_221326

Data entry in  Grid also possible with a Text Box which require handling Keyup,KeyPress Events of the TextBox control.

Place a GridControl and a TextBox to your project and add the following code the Textbox Events.

Public Sub MCellEnter()
With MSHFlexGrid_POrder
txt_DEntry.Text = .TextMatrix(.row, .col)
txt_DEntry.Move (.CellLeft + .Left), (.CellTop + .Top), _
.CellWidth, .CellHeight
txt_DEntry.Visible = True
DoEvents
txt_DEntry.SetFocus
End With
End Sub

The above code place the text box on the active cell by adjusting hight, width and top of Text Box

Private Sub txt_DEntry_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
With MSHFlexGrid_POrder
.TextMatrix(.row, .col) = txt_DEntry
If .col < .Cols – 1 Then
.col = .col + 1
MCellEnter
Else
.col = 0
If (.row + 1) < .Rows Then
.row = .row + 1
End If
MCellEnter
End If

End With
End If

End Sub

The above code handle the enter keybehaviour and following keep track of Arrow key movements.

Private Sub txt_DEntry_KeyDown(KeyCode As Integer, Shift As Integer)
With MSHFlexGrid_POrder
.Refresh
.TextMatrix(.row, .col) = txt_DEntry

Select Case KeyCode
Case KeyCodeConstants.vbKeyDown
If .row + 1 < .Rows Then
.row = .row + 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyUp
If (.row – 1) > 0 Then
.row = .row – 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyRight
If (.col + 1 < .Cols) Then
.col = .col + 1
MCellEnter
End If
Case KeyCodeConstants.vbKeyLeft
If (.col – 1) >= 0 Then
.col = .col – 1
MCellEnter
End If
End Select
End With

End Sub

That’s all you need to know

Querying external database in vb6 DAO


DAO is one of many ways to access database in VB6. This tutorial tells you how you can access a .mdb database which is not pointed by the DAO Database object.

I have separate Access 2003 .mdb database files, one is pointed by the Database object conn and the other I want to access using the same connection.

SELECT * FROM [File Path][DBNAME].[TABLE]

Connection

Have a look at the connection. [May be you already know, what its look like, this for those not familiar with DAO in VB6]

Dim externalEmp As Recordset
Dim conn As Database
Set conn = OpenDatabase("D:\105443T.mdb")

Querying Records

The conn is now capable of querying any table within the 105443T database.

Say,

Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
 tr.MoveFirst
 tr.MoveNext
 tr.MoveFirst
 
 For c = 0 To tr.RecordCount - 1
 Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
 tr.MoveNext
 Next c
End If

Querying the external DB

Likewise, I can point external Database too with zero changes in conn object. Just specify the [path of file] [.] [data table Name] in place of the table name in the query.

Set externalEmp = conn.OpenRecordset("select * from C:\users\manoj\documents\office.emp")
If externalEmp.RecordCount > 0 Then
 externalEmp.MoveFirst
 externalEmp.MoveNext
 externalEmp.MoveFirst
 
 For c = 0 To externalEmp.RecordCount - 1
 Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
 externalEmp.MoveNext
 Next c
End If

The same is also possible with ADO and other connections.

That’s it.

Copying rows from one Access database to Another using Query


In access database, we can copy rows from one table to another using query. How can we transfer rows from one database to another?

Using the in  keyword in Insert statement we can specify an external database path as follows in a access query

insert into Account_Transactions(Date ,Name ,Type ) in ‘D:\Sherpharma\T.mdb’ select  Account_Transactions.Date,Account_Transactions.Name,Account_Transactions.Type from  Account_Transactions

Here we copy the rows from current database to the T.mdb files. We can also use this query in programming as well.

 

How to check for Access table in Vb6 at runtime


As we know VB6 let programmer most of the database functions programmatic ways. We are happy to show you a way to check for a tables in your Access database at run time with this tutorial.

As usual we start with a Function, which can be reuse the code, drop the following code in a module.
Function TableExists(databaseName As String, Name As String) As Boolean
‘Table check
Dim TableDb As Database
Dim TableDefinition As TableDef
TableExists1 = False
If Len(databaseName) = 0 Or Len(Name) = 0 Then
 Exit Function
End If

On Error GoTo CheckError
Set TableDb = OpenDatabase(databaseName, , True)
Set TableDefinition = TableDb.TableDefs(Name)
TableExists = True

ExitFunction:
On Error Resume Next
Exit Function
CheckError:
If err.Number 3265 Then
MsgBox “Error #” & err.Number & ” occured: ” & err.Description
End If
Resume ExitFunction
End Function

The code utilizes Access DAO connection and Access 2003/2007 .mdb database . Just pass the exact path to the database and the name of the table and it will search for the table and return true if the file exist.

I think this will save time of many and will let programmers/poets to enable auto checking for  database application

Check for folder/Drive/File in VB 6


In visual Basic 6.0 you can check the existence of file and folder by utilizing the File System Object. Lets take a look at the sub routine  which check the existence of folder in your system.

Is Dir/Folder Exist

‘Returns a Boolean – True if the file exists

Public Function DirExist(OrigFile As String)
Dim fs
Set fs = CreateObject(“Scripting.FileSystemObject”)
DirExists = fs.folderexists(OrigFile)
End Function

The function return false if the folder check fails.

 Is Drive Exist

Public Function DExists(OrigFile As String)
Dim fs, d
Set fs = CreateObject(“Scripting.FileSystemObject”)
    If fs.driveexists(OrigFile) = True Then
    Set d = fs.getdrive(OrigFile)
    DExists = 1
        If d.isready = True Then
        DExists = 2
        Exit Function
        End If
    Else
    DExists = 0
    End If
End Function

The function return a none zero value if the drive found and active other wise it leave a zero.

And finally the file check and it simple with three line code

File Check

Public Function FExists(OrigFile As String)
Dim fs
Set fs = CreateObject(“Scripting.FileSystemObject”)
FExists = fs.fileexists(OrigFile)
End Function

I thing that is enough to for a code poet to make sure the file exist in the system. Please re share the post.

Create Table at run time using DAO connection in Visual Basic 6.0


Using VB6 database connection (DAO) connection we can not only manipulate data from the existing database but also dynamically create new table according to the necessity of the program. This can be achieved using CreateTableDef method of record set object.

Make sure your are included the appropriate references from References window.

Create a new connection 

Dim MedicalShop as Database
Set MedicalShop=opendatabse(GetAppPath()& “shop.mdb”)

Create the Sub for create table

The the sub will utilizes the Tabledef and Fields objects and by using the CreateTabledef command it will create the new table.

Lets add the body of the procedure as follows

Public Sub CreateTable(tbl As String, fl() As String)
Dim tdf As TableDef
Dim fld As Field
Dim f, I
I = 0
Set tdf = MedicalShop.CreateTableDef(tbl)
For Each f In fl
 If f Empty Then
  Set fld = tdf.CreateField(f, dbText)
  tdf.Fields.Append fld
  I = I + 1
 End If
Next
MedicalShop.TableDefs.Append tdf
MedicalShop.Recordsets.Refresh
End Sub

By default the field type is DbText, you can specify each  with different type by passing an addition argument list or a two dimensional array.

Call the CreateTable method

Dim f(3) as String
f(0)=”Entryno”
f(1)=”Name”
f(2)=”amount”
f(3)=”Narration”

Now call the procedure

Call CreateTable(“salesentry”,f)

Will create a new table with fields and having Text field type.

How to add new fields to Access table in VB6


Let me show how to add a new field into an existing access database Table using Visual Basic 6.0.Using the tableDef and Filed object you can create new fields from within the code . 

Firstly, you need to create Database and Recordset object, make sure the DAO access object library is enabled/added to the project.

Create the UpdateTableField Sub

Sub UpdateTableField(tb As String, new_fld As String)Dim CHECK As Boolean
CHECK = False
Dim tbl As TableDef
Dim fld As Field
Dim strName As String
Set tbl = DB.TableDefs(tb)
  For Each fld In tbl.Fields
    If fld.Name = new_fld Then
      CHECK = True
    End If
  Next
If CHECK = False Then
   Set RS = Nothing
   With tbl
   Set fld = .CreateField(new_fld, dbText)
   fld.DefaultValue = “”
   .Fields.Append fld
  MsgBox “new Field Created !, check table”
  End With
Else
  MsgBox “Field already exist !”End If

The ‘TableDefs’ method will fetch the table schema from the database which hold the field information and then fire the checking for table field. dbText hold the default data type. 

Use the sub

Dim DB As Database
Dim RS As Recordset
Private Sub Command1_Click()
Call UpdateTableField(“Table1”, “RoomID2”)
End Sub
Private Sub Form_Initialize()
Set DB = OpenDatabase(“F:\Manoj\Code\DB-AUTO-FIELD-ACCES\db3.mdb”)
End Sub

A new field “RoomID2” will be created in the ‘Table1‘ Table.
Download the Source Code