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
MSHFlex Grid[Visual Basic 6.0] control is a goog tool for showing data in a tabular format as in Excel.
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 IfEnd With
End IfEnd 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_DEntrySelect 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 WithEnd Sub
That’s all you need to know
It is something like complicated when you want to export reports from reporting objects like List view in VB6. In fact Excel object has provided a way to directly export contents of a table into Excel format in a quick manner using DAO or ADO connection.
To illustrate this we need,
You may be familiar with the database connections in VB6, the following is an example of ADO data connection.
Dim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordsetadodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\emp.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Accounts “, adodb_db, adOpenKeyset
The excel objects are required to create a new Excel file in order to export the rows of a table.
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
The CopyFromRecordset of Excel object didn’t fetch columns, so we do it manually as follows.
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next cEnd if
The Excel Worksheet object’s CopyFromRecordset help you to copy records to excel rows, all you need to specify the cell range.
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rs
Here is the complete code
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As ObjectDim adodb_db As New ADODB.Connection
Dim adodb_rs As New ADODB.Recordsetadodb_db.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\SherPharma\SIGMA1819.mdb;Persist Security Info=False”
adodb_db.Open
adodb_rs.Open “select * from Account_Transactions “, adodb_db, adOpenKeyset‘Set rs = MedicalShop.OpenRecordset(“select entryno,itemname,total from Sales_Particulars “)
If adodb_rs.RecordCount > 0 Then
adodb_rs.MoveFirst
adodb_rs.MoveNext
adodb_rs.MoveLast
adodb_rs.MoveFirst‘Start a new workbook in Excel
Set oExcel = CreateObject(“Excel.Application”)
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)For c = 0 To adodb_rs.Fields.count – 1
oBook.Worksheets(“Sheet1”).Cells(1, c + 1) = adodb_rs.Fields(c).Name
Next c
oBook.Worksheets(“Sheet1”).Range(“A2”).CopyFromRecordset adodb_rsoExcel.Visible = vbTrue
End If
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]
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")
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
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.
Create auto filed ,which automatically generate value, programmatically in Vb6 is easy with two line of code.
What we will do when you need create auto value programmatically or with query.
Here what you need to do
alter table YourTable column [auto] counter(1,1)
You can use the statement with Execute method of ADO connection in VB6 as follows
adodbconnection.Execute “alter table YourTable alter column [auto] counter(1,1)”
