VB6 With Access

Level:
Level2

Microsoft® Access may be the most powerful programming tool available for Visual Basic. While nearly everything Access does can be accomplished with Visual Basic code (there are exceptions), Access makes most design tasks so much simpler that I consider having a copy of Access as much a necessity for database development as a keyboard.

  • Designing a Database
    Access makes the process of building tables, indexes, and relationships incredibly easy. In a few minutes you can do what would require hundreds of lines of Visual Basic code to accomplish.
  • Building Queries
    While it's important for you to understand Jet SQL, Access makes the process of designing and testing queries much simpler. Once you've built a query design using the Access query design window, you can switch to SQL view and simply cut and paste the SQL statement into your VB code window.
  • Managing Security
    Assigning permissions to objects using Visual Basic and DAO code is an obscure and complex task. Access makes the process easier. Additionally, if you are intending to create secured Jet databases, you must have a copy of Access available since only the Access Workgroup Administrator can create the required system database file.
  • Summary

Designing a Database

Creating a database from a design involves a large number of tedious processes of creating tables, fields, indexes, relationships, default values, rules, and so on. While this can all be done with pure VB code, it's an an obscure and complex process. Here's a short example from the VB help file under the CreateTableDef topic that will create a table with a single field:
	Dim MyTableDef As TableDef, MyField As Field
	Dim MyDatabase As Database
	Set MyDatabase = Workspaces(0).OpenDatabase("BIBLIO.MDB")
	' Create new TableDef.
	Set MyTableDef = MyDatabase.CreateTableDef("Title Detail")
	' Add field to MyTableDef.
	Set MyField = MyTableDef.CreateField("Comments",dbDate)
	MyTableDef.Fields.Append MyField
	' Save TableDef definition by appending it to TableDefs collection.
	MyDatabase.TableDefs.Append MyTableDef
Now, you could skip using the CreateTableDef and CreateField methods and perform the intire process with a single call to the Execute method of the database object using a Jet DDL SQL statement. Here's the general format for a SQL CREATE TABLE statement:
CREATE TABLE table (field1 type [(size)] [index1] [, field2 type [(size)] [index2] [, ...]] [, multifieldindex [, ...]])
The code might look like this (I've modified some names from the previous example for simplicity):
	Dim db As Database
	Dim sSQLDDL As String

	sSQLDDL = "CREATE TABLE TitleDetail ( Comments DATETIME );"
	Set db = Workspaces(0).OpenDatabase(App.Path & "\" & App.EXEName & ".mdb")
	db.Execute sSQLDDL

	db.Close
	Set db = Nothing
Keep in mind that in both of these examples, we have code that creates one table with one field. There are no rules, defaults, indexes, or primary or foreign keys involved. Additionally, the code contains no error handling. Now consider what the code would look like to create a large database design consisting of hundreds of fields in dozens of tables, complete with primary and foreign key constraints, rules, defaults, and so on.

In general, you'll find all aspects of designing a database considerably simpler in Access. Here's an example of what's required to accomplish some basic design tasks using the Access interface:

  • Create a Table
    Select the Table tab in the database window and click New.
  • Create a Field
    In table design view, type the name of the field in the grid, assign a datatype and set any other properties as necessary in the property sheet.
  • Create an Index
    In table design view, select the field and set the indexed property in the property sheet.
  • Create a Relationship
    Open the Relationships window and add the two tables involved in the relationship. Drag a field from one table onto a field in the other table. In the dialog box that appears, check the appropriate boxes to determine if the relationship will be enforced and if it will use cascading updates and deletes.
As you can see, the Access interface greatly simplifies the process of building a database.

Building Queries

Once you have built the basic database design, including tables, fields, indexes, relationships, rules, defaults, and so on, the most common task you will perform will be building queries. All queries are created with Structured Query Language (SQL), but Access provides a graphical design tool for building queries that makes the process easier, faster, and more reliable. It also gives you a chance to see what the SQL for a complex query looks like without needing to fully understand all of the SQL syntax involved. This makes the query design window both a design tool and a learning tool.

Here's an example of the SQL for a complex query that's used as the base source of several reports in a large database application:

SELECT DISTINCTROW 
HLLink.HLID, HLLink.HLCaseID, HLLink.HLRemarks, HLLink.HLCRCInit, 
CaseArchive.CaseStatusClass, CaseArchive.CaseStatusCategory, 
Organizations.OrgName, Organizations.OrgDept, 
Organizations.OrgAddress1, Organizations.OrgAddress2, 
Organizations.OrgCity, Organizations.OrgState, Organizations.OrgZIP, 
Organizations.OrgPhone, Organizations.OrgFax, Organizations.OrgCategory, 
People.PersonPrefix, 
People.PersonFirst, People.PersonMiddle, People.PersonLast, 
People.PersonSuffix, People.PersonTitle, 
People.PersonAddress1, People.PersonAddress2, 
People.PersonCity, People.PersonState, People.PersonZIP, 
People.PersonPhone, People.PersonPExt, People.PersonFax, People.PersonCategory, 
TimeTable.TTBillingID, TimeTable.TTTaskID, TimeTable.TTUser, 
TimeTable.TTStart, TimeTable.TTSeconds, TimeTable.TTAddMin, 
Cases.CaseReportingRegion, Cases.CaseReportingState, 
Cases.CaseTypeClass, Cases.CaseTypeCategory, 
Cases.CaseStatusClass, Cases.CaseStatusCategory, 
Cases.CaseDiagnosis, Cases.CaseIsOffLabel, 
Plans.PlanClass, Plans.PlanCategory, Plans.PlanTitle, 
Products.ProductName, 
Diagnoses.Diagnosis
FROM Diagnoses 
RIGHT JOIN (Organizations 
RIGHT JOIN (TimeTable 
INNER JOIN ((Plans 
RIGHT JOIN (People 
RIGHT JOIN (OPLink AS PhysicianOPLink 
RIGHT JOIN (Products 
RIGHT JOIN (Cases 
RIGHT JOIN HLLink 
ON Cases.CaseID = HLLink.HLCaseID) 
ON Products.ProductID = Cases.CaseProductID) 
ON PhysicianOPLink.OPID = Cases.CasePhysicianOPID) 
ON People.PID = HLLink.HLPID) 
ON Plans.PlanID = Cases.CasePlanID) 
LEFT JOIN CaseArchive 
ON HLLink.HLID = CaseArchive.HLID) 
ON TimeTable.TimerID = HLLink.HLTimerID) 
ON Organizations.OID = HLLink.HLOID) 
ON Diagnoses.DiagAbbrev = Cases.CaseDiagnosis;
Clearly this is a query that is more complex than what is typical in most applications. The effort required to build this query as a SQL statement would be considerable given the complexity, the number of joins, etc. Here's the same query seen in the Access query design window:

Screen Shot

It doesn't take a user interface design expert to see that this query can be constructed and tested much faster using Access than by coding and testing it in Visual Basic. The process of adding tables and joining them using the query design window is as simple as drag and drop. If you've already defined the relationships between tables, Access will even add the joins for you.

Some aspects of building SQL queries are fairly simple to create by just entering the SQL statement. If you're doing a simple select of a few fields from a single table, it's easy enough to just type:

SELECT FirstName, LastName FROM Customers WHERE LastName LIKE 'Smith';
If, however, you're building a query with several tables and multiple outer joins, the query design window will make the process of building and debugging the query much easier. A final bit of advice I'll offer is that you should not use the query design window as a substitute for understanding Jet SQL. If you're building a serious database application, at some point you'll probably need to dynamically generate the SQL for a query in your code. Study the SQL generated by the query designer and the help files to learn the syntax for builing inner and outer joins, setting criteria and sorting records.

Managing Security

Setting up a secured database is one of the most misunderstood topics in Visual Basic. With VB 4.0 and Jet 3.0, you at last have the capability of programmatically assigning permissions to database objects. However, the process is complex and you will still need a copy of Access to create the system database file which is the cornerstone of Jet security. Unfortunately, the Access interface for managing object permissions isn't that much of an improvement over assigning them programmatically.

Properly securing a database isn't difficult, but there are several steps involved which must be followed for the database to be secure.

Setting the permissions for an object using VB code consists of getting a reference to a Document object and assigning the appropriate value to the permissions property. Here's a fairly useless example from the VB help file:

	Dim MyDB As Database, MyDoc As Document
	Set MyDB = Workspaces(0).OpenDatabase("MYDB.MDB")
	Set MyDoc = MyDB.Containers(0).Documents("MyDoc")
	Debug.Print MyDoc.Permissions
The reason the example is useless is that it doesn't actually set any permissions. There's only one key thing to remember when setting the permissions property and that is that the property is a bit field containing all the permissions in a single value. While you can replace the entire set by assigning a value to the property, if you wish to add or remove a permission, you must use the bitwise operators. In the preceding code, here's how you would add the Modify permission to the object:
	MyDoc.Permissions = MyDoc.Permissions Or dbSecWriteDef
To add a permission to an object, Or the appropriate constant with the existing permissions property.

Here's how you would remove the same permission:

	MyDoc.Permissions = MyDoc.Permissions And Not dbSecWriteDef
To remove a permission, use the bitwise And Not operator.

The reason you must use the bitwise operators rather than simply adding or subtracting the constant is that if you happen to add a constant to a permissions property where the permission has already been assigned, the operation will improperly modify the value. The bitwise operators will simply have no effect if the value has already been set (or removed). This applies to bitfields in general - always use the bitwise operators when working with bitfields.

By comparison, the Access user interface provides relatively easy to use dialogs for assigning permissions. Simply select the object from the appropriate object list, select the user or group from the users and groups list, and check or clear the permissions as appropriate. When you're done, click the Assign button.

For what its worth, the Access interface for assigning permissions could use some work. Here's some of the reasons why:

  • The small, fixed size dialog box makes it difficult to work with a large number of objects simultaneously.
  • The dialog presents a list of users rather than groups by default. As a matter of habit, you should always assign permissions to groups rather than users in a multiuser situation - whether it be a database application or anything else.
  • In order to manage users and groups, you need to open a separate, modal dialog box.
  • There's no means provided to see inherited permissions for an object when looking at a user account.
  • The means by which you assign default permissions for new objects is less than obvious and poorly documented.
  • Some permissions cannot be modified at all using the user interface. For example, there is no means provided to grant or revoke the ability to create new objects. This permission is granted by default and you must use DAO code to revoke it. If you wish to prevent users from creating new tables or other database objects, you'll need to write code for it.
  • You need to open a database file to create and manage user and group accounts, even though the accounts are bound to a system database file (Access calls this a workgroup) rather than a particular database.
Overall, the entire set of security management tools in Access are a bit clumsy, but still better than writing a lot of code for something that's done only once or at best infrequently. An improved user interface for managing security with Jet databases would be a fine small project that would be much appreciated by many Access and Visual Basic developers. If you're interested in programming Jet security, this would be a good starter project. Perhaps you can beat me to the punch - my version is definitely on the back burner.

Note: Keep in mind that if you will be creating secured applications with Visual Basic, you must have a copy of Access available to create the necessary system database. There is no means available with Visual Basic code to create the required file. Only the Access Workgroup Administrator can create a system database.

Originally written by Joe Garrick

If you enjoyed this post, subscribe for updates (it's free)

vb/ access

I am trying to run a program on vb, when I try to run it detect that only comments, function will be displayed and it underline this code:
Private declare function RegOpenKeyEx Lib"advapi32" Alias"RegOpenKeyExA"(ByVal hKey As Long, ByVal sameDesired As Long, ByRefPhkeyResult As Long) As Long.
Please I need your help to finish my project. Is under patient bill system

Code in VB6 for connection foxpro2.6 (.dbf)file

Please help me in write the following code in VB6

1. How to connect foxpro 2.6 (.dbf) file in VB6
2. After connecting how to append the new record using "Append" button
3. After entering data in new record, how to save using "Save" button
4. Finally how to print the fields like Name, ID,Address of present record on the Printer

Having trouble calculating a sum in an access report

HI all,
I have a VB6 apps. running an Access report. The report is based on a query of 3 related tables.
I need to compare the total amount paid by individuals (4th table) with an arbitrary Max. value, and choose which value to print.
I have placed a textbox with the following code (as a Control Source) into the report, but keep getting an #Error message

tried the following 2 options - built by the Access builder, and modified as per the examples given
>> Note << [ReceiptNbr] is the Control Source of a textbox on the report (from the query that the Report is based on).

=DSum(tblTransactions!AmountPaid,[tblTransactions],tblTransactions!ReceiptNbr=[ReceiptNbr])

=DSum("[AmountPaid]","tblTransactions","tblTransactions![ReceiptNbr] = " & [ReceiptNbr])

so far - no luck. Anyone out there got any ideas please.

many thanks
Phil

how can i save complete listbox in access by using vb

Hi
i always save the list box in access by using vb, but it save just a selected value i want to save complete listbox in access by using vb. can you help me

searching in database

please i need help in how to view a database and also search for a database in visual basic6(ms Access)

hii

plz send me a code for inser,delete in visual basic 6 with ms access

please help me!

There is a problem in cmdGo_
Click()
Run-time error '3251' :
operation is not supported for
this type of object.
.FindFirst strCriteria
.FindNext strCriteria

plz help me how to search record in access database from vb6 application?
Where Can i download pdf manual for using database in vb? Plz give link

how can i load a picture and

how can i load a picture and at the same time save it?
how can i connect it to database?

lol :P u cannot do all that

lol :P u cannot do all that :P
jus try and do something els useful in life :) thqanking u :)
urs sincerely
lol :P

Query on Runtime

Hi! how can I make a query in my Access table during run time in VB6? Thank you!

vb and html

i need railway reservation project using html coding
i need full project coding.

connecting vb 6 to ms access!

HI!
Can any one please tell me that how can i connect vb 6 to ms access using option or radio button!
please help me any one i really need to know please!

hi i have some project in

hi i have some project in vb.6. i can help your doubt about connect vb with access. please send your E-mail ID. thank you.

how to make an time keeping in vb 6.0 with connection?

how to make an time keeping in vb 6.0 with connection?

pleas help,,,
thank you

visual basic 6.0

hi guys, can you give the code of edit & the delete... please reply as soon as possible thanks..

For Delete on error resume

For Delete
on error resume next
adodc1.recordset.delete
end sub

For Edit
on error resume next
adodc1.recordset.Edit
end sub

I hope that I can help you''''

How to call and view in VB6 an ole picture in a access database

please tell me
1. how to call and view pictures in an Access Database as OLE object and view in a vb picture box

2. How can we get the keyboard signals when a key is pressed (when the vb form is innactive)

REally need your help..

Option Explicit
Public conn As New ADODB.connection
Public rs As New ADODB.Recordset

Sub connection()
Set conn = New ADODB.connection
conn.CursorLocation = adUseClient
conn.Open "Provider = Microsoft.Jet.OLEDB.4.0; Data Source=" & App.Path & "\sample.mdb"
End Sub

Private Sub cmdEXIT_Click()
End
End Sub

Private Sub cmdOK_Click()
Set rs = New ADODB.Recordset
With rs
.Open "Select * from login_user WHERE username = '" & txtuser.Text & "' and password = '" & txtpass.Text & "' ", conn, adOpenStatic, adLockOptimistic
If .EOF Then
txtuser.Text = ""
txtpass.Text = ""
txtuser.SetFocus
MsgBox "Invalid authentication!", vbExclamation, "User Authentication"
Else
'Unload Me
'show form here for admin
MsgBox "Successfully", vbInformation, "User Authentication"
' frmLogin.Hide
'frmMain.Show
' frmWelcome.Show
End If
End With
End Sub

Private Sub Form_Load()
Call connection
End Sub

Will you help me how to find the error of this code?
i really have a problem with this..I need to know on how to store data in access when i input data in my vb.I really appreciate ur help..thank you

VB6 with Access (Database)

First open notepad, leave it blank and then save it as "connection.udl" (No Double Quotation)
then re-open it with OLE DB CORE SERVICES.
On the Data Link Properties, Click the Provider Tab.
On the Provider Tab, select Microsoft Jet 4.0 OLE DB Provider, then click Next Button.
On the Connection Tab, select your database access (make sure that your database access is in 2000-2003 version or lower version, i don't really know if it also works on higher access versions).
then click the Test button, once it shows successful, just click OK button.

Create a VB6 project or open your existing project.
Add module to your project.
this will be the code:

This is sample code only.
Public db As New ADODB.Connection
Public rs As New ADODB.Recordset
Public myclass As New Class1

add class module to your project.
this will be the code:

Sub db_Conn()
If db.State = adStateOpen Then db.Close
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\SidecarPlateNo.mdb;Persist Security Info=False"
If rs.State = adStateOpen Then rs.Close
db.CursorLocation = adUseClient
rs.Open "SELECT*from Sidecar order by RegisteredOwner DESC", db, 1, 3
End Sub

Note: the code on the 2nd line of the class module will be based from the connection.udl file.
just re-open the connection.udl as notepad, that will be the code enclose with double quotations in the 2nd line of the class module. The "SidecarPlateNo" is the name of the access database, just replace it with your access database.
On the 5th line of the class module code, the "Sidecar" is the table title of the access database and the RegisteredOwner is the 1st or main field name in the table of "Sidecar".

add form your project just insert/edit this code for your form.
this will be the code:

'this will make the text all caps after typing in the run mode
Private Sub AllCaps()
txtRO.Text = UCase(txtRO.Text)
txtAddress.Text = UCase(txtAddress.Text)
txtQty.Text = UCase(txtQty.Text)
txtMake.Text = UCase(txtMake.Text)
txtMN.Text = UCase(txtMN.Text)
txtCN.Text = UCase(txtCN.Text)
txtLPN.Text = UCase(txtLPN.Text)
txtSPN.Text = UCase(txtSPN.Text)
txtDayBan.Text = UCase(txtDayBan.Text)
txtSamahan.Text = UCase(txtSamahan.Text)
txtPH.Text = UCase(txtPH.Text)
txtIB.Text = UCase(txtIB.Text)
End Sub

'this will clear all the text in the text fields
Private Sub ClearAll()
txtRO.Text = ""
txtAddress.Text = ""
txtQty.Text = ""
txtQtyUnit.Text = ""
txtMake.Text = ""
txtMN.Text = ""
txtCN.Text = ""
txtLPN.Text = ""
txtSPN.Text = ""
txtDayBan.Text = ""
txtSamahan.Text = ""
txtPH.Text = ""
txtIB.Text = ""
txtDay.Text = ""
cmbMonth.Text = ""
txtYear.Text = ""
txtDateIssued.Text = ""
txtCTCN.Text = ""
txtROSearch.Text = ""
txtSamahan.Text = ""
End Sub

this will fill the database from this program to access
Private Sub FillObj()
txtRO.Text = rs!RegisteredOwner
txtAddress.Text = rs!RO_Address
txtQty.Text = rs!Qty
txtQtyUnit.Text = rs!QtyUnit
txtMake.Text = rs!Make
txtMN.Text = rs!MotorNo
txtCN.Text = rs!ChasisNo
txtLPN.Text = rs!LTOPlateNo
txtSPN.Text = rs!SidecarPlateNo
txtDayBan.Text = rs!DayBan
txtSamahan.Text = rs!Samahan
txtPH.Text = rs!PlateHolder
txtIB.Text = rs!InspectedBy
txtDay.Text = rs!SDate
cmbMonth.Text = rs!SMonth
txtYear.Text = rs!SYear
txtDateIssued.Text = rs!DateIssued
txtCTCN.Text = rs!CommunityTaxCertNo
End Sub

'this will make the text locked/unlocked as yo call this as true or false
Private Sub LockerType(x As Boolean)
txtRO.Locked = x
txtAddress.Locked = x
txtQty.Locked = x
txtQtyUnit.Locked = x
txtMake.Locked = x
txtMN.Locked = x
txtCN.Locked = x
txtLPN.Locked = x
txtSPN.Locked = x
txtDayBan.Locked = x
txtSamahan.Locked = x
txtPH.Locked = x
txtIB.Locked = x
txtDay.Locked = x
cmbMonth.Locked = x
txtYear.Locked = x
txtDateIssued.Locked = x
txtCTCN.Locked = x
End Sub

Private Sub Vanish()
If rs.RecordCount = 0 Then
Call FillObj
Call ClearAll
End If
End Sub

'this is for the data grid code
Private Sub dg1_Click()
rs.Update
If rs.RecordCount <> 0 Then
Call FillObj
ElseIf rs.RecordCount = 0 Then
Call Vanish
End If
Call LockerType(True)
mnuSave.Enabled = False
mnuPrint.Enabled = True
mnuDelete.Enabled = True
mnuEdit.Enabled = True
mnuEditAll.Enabled = True
End Sub

Private Sub Form_Load()
Me.Top = (Screen.Height - Me.Height) / 2
Me.Left = (Screen.Width - Me.Width) / 2

myclass.db_Conn

If rs.RecordCount <> 0 Then
Call FillObj
ElseIf rs.RecordCount = 0 Then
Call Vanish
End If

cmbMonth.AddItem "January"
cmbMonth.AddItem "February"
cmbMonth.AddItem "March"
cmbMonth.AddItem "April"
cmbMonth.AddItem "May"
cmbMonth.AddItem "June"
cmbMonth.AddItem "July"
cmbMonth.AddItem "August"
cmbMonth.AddItem "September"
cmbMonth.AddItem "October"
cmbMonth.AddItem "November"
cmbMonth.AddItem "December"

Set dg1.DataSource = rs
dg1.HeadFont.Bold = True
dg1.Columns(0).Locked = True
dg1.Columns(1).Locked = True
dg1.Columns(2).Locked = True
dg1.Columns(3).Locked = True
dg1.Columns(4).Locked = True
dg1.Columns(5).Locked = True
dg1.Columns(6).Locked = True
dg1.Columns(7).Locked = True
dg1.Columns(8).Locked = True
dg1.Columns(9).Locked = True
dg1.Columns(10).Locked = True
dg1.Columns(11).Locked = True
dg1.Columns(12).Locked = True
dg1.Columns(13).Locked = True
dg1.Columns(14).Locked = True
dg1.Columns(15).Locked = True
dg1.Columns(16).Locked = True
dg1.Columns(17).Locked = True
dg1.Refresh

mnuSave.Enabled = False
mnuDelete.Enabled = False
mnuPrint.Enabled = False
mnuEdit.Enabled = False
optRO.Value = True

Call ClearAll
Call LockerType(True)
End Sub

Private Sub mnuClearAll_Click()
Call ClearAll
Call LockerType(True)
txtRO.SetFocus
mnuNew.Enabled = True
mnuSave.Enabled = False
mnuPrint.Enabled = False
mnuDelete.Enabled = False
mnuEdit.Enabled = False
End Sub

Private Sub mnuDelete_Click()
Dim R As String
R = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete")
If R = vbYes Then
rs.Delete
rs.Update
Call ClearAll
MsgBox "Record successfully deleted!", vbInformation, "Delete"
Else
Me.Refresh
End If
mnuSave.Enabled = False
mnuPrint.Enabled = False
mnuDelete.Enabled = False
End Sub

Private Sub mnuEditAll_Click()
Call LockerType(False)
mnuNew.Enabled = False
mnuSave.Enabled = True
mnuDelete.Enabled = False
mnuPrint.Enabled = True
mnuEdit.Enabled = True
mnuEditAll.Enabled = False
mnuSave.Caption = "Save As"
End Sub

Private Sub mnuExit_Click()
Dim R As String
R = MsgBox("Are you sure you want to exit this database?", vbYesNo + vbCritical, "Exit")
If R = vbYes Then
End
Else
Me.Show
End If
End Sub

Private Sub mnuNew_Click()
txtRO.SetFocus
If mnuNew.Caption = "Cancel" Then
Call ClearAll
Call LockerType(True)
dg1.Enabled = True
mnuNew.Caption = "New"
mnuSave.Enabled = False
mnuDelete.Enabled = False
mnuPrint.Enabled = False
mnuEdit.Enabled = False
dg1.Refresh
Else
Call ClearAll
Call LockerType(False)
dg1.Enabled = False
mnuNew.Caption = "Cancel"
mnuSave.Enabled = True
mnuDelete.Enabled = False
mnuPrint.Enabled = False
mnuEdit.Enabled = True
mnuEditAll.Enabled = False
mnuSave.Caption = "Save"
End If
Call AllCaps
End Sub

Private Sub mnuPrint_Click()
If txtRO.Text = "" Then
MsgBox "Input 'Registered Owner'!", vbInformation, "Input"
txtRO.SetFocus
Exit Sub

If txtAddress.Text = "" Then
MsgBox "Input 'Address'!", vbInformation, "Input"
txtAddress.SetFocus
Exit Sub

If txtQty.Text = "" Then
MsgBox "Input 'Qty.'!", vbInformation, "Input"
txtQty.SetFocus
Exit Sub

If txtQtyUnit.Text = "" Then
MsgBox "Input 'Qty. (Unit)'!", vbInformation, "Input"
txtQtyUnit.SetFocus
Exit Sub

If txtMake.Text = "" Then
MsgBox "Input 'Make'!", vbInformation, "Input"
txtMake.SetFocus
Exit Sub

If txtMN.Text = "" Then
MsgBox "Input 'Motor No.'!", vbInformation, "Input"
txtMN.SetFocus
Exit Sub

If txtCN.Text = "" Then
MsgBox "Input 'Chasis No.'!", vbInformation, "Input"
txtCN.SetFocus
Exit Sub

If txtLPN.Text = "" Then
MsgBox "Input 'LTo Plate No.'!", vbInformation, "Input"
txtLPN.SetFocus
Exit Sub

If txtSPN.Text = "" Then
MsgBox "Input 'Sidecar Plate No.'!", vbInformation, "Input"
txtSPN.SetFocus
Exit Sub

If txtDayBan.Text = "" Then
MsgBox "Input 'Day Ban'!", vbInformation, "Input"
txtDayBan.SetFocus
Exit Sub

If txtSamahan.Text = "" Then
MsgBox "Input 'Samahan'!", vbInformation, "Input"
txtSamahan.SetFocus
Exit Sub

If txtPH.Text = "" Then
MsgBox "Input 'Plate Holder'!", vbInformation, "Input"
txtPH.SetFocus
Exit Sub

If txtIB.Text = "" Then
MsgBox "Input 'Inspected by'!", vbInformation, "Input"
txtIB.SetFocus
Exit Sub

If txtDay.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
txtDay.SetFocus
Exit Sub

If cmbMonth.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
cmbMonth.SetFocus
Exit Sub

If txtYear.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
txtYear.SetFocus
Exit Sub

If txtDateIssued.Text = "" Then
MsgBox "Input 'Date Issued'!", vbInformation, "Input"
txtDateIssued.SetFocus
Exit Sub

If txtCTCN.Text = "" Then
MsgBox "Input 'Community Tax Certificate No.'!", vbInformation, "Input"
txtCTCN.SetFocus
Exit Sub

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Call AllCaps

DataReport1.Sections("Section1").Controls("lblRO1").Caption = Form_Sidecar.txtRO.Text
DataReport1.Sections("Section1").Controls("lblAddress").Caption = Form_Sidecar.txtAddress.Text
DataReport1.Sections("Section1").Controls("lblQty").Caption = Form_Sidecar.txtQty.Text
DataReport1.Sections("Section1").Controls("lblQtyUnit").Caption = Form_Sidecar.txtQtyUnit.Text
DataReport1.Sections("Section1").Controls("lblMake").Caption = Form_Sidecar.txtMake.Text
DataReport1.Sections("Section1").Controls("lblMN").Caption = Form_Sidecar.txtMN.Text
DataReport1.Sections("Section1").Controls("lblCN").Caption = Form_Sidecar.txtCN.Text
DataReport1.Sections("Section1").Controls("lblLPN").Caption = Form_Sidecar.txtLPN.Text
DataReport1.Sections("Section1").Controls("lblSPN").Caption = Form_Sidecar.txtSPN.Text
DataReport1.Sections("Section1").Controls("lblDayBan").Caption = Form_Sidecar.txtDayBan.Text
DataReport1.Sections("Section1").Controls("lblDay").Caption = Form_Sidecar.txtDay.Text
DataReport1.Sections("Section1").Controls("lblMonth").Caption = Form_Sidecar.cmbMonth.Text
DataReport1.Sections("Section1").Controls("lblYear").Caption = Form_Sidecar.txtYear.Text
DataReport1.Sections("Section1").Controls("lblDateIssued").Caption = Form_Sidecar.txtDateIssued.Text
DataReport1.Sections("Section1").Controls("lblCTCN").Caption = Form_Sidecar.txtCTCN.Text
DataReport1.Sections("Section1").Controls("lblRO2").Caption = Form_Sidecar.txtRO.Text
DataReport1.Show
End Sub

Private Sub mnuSave_Click()
If txtRO.Text = "" Then
MsgBox "Input 'Registered Owner'!", vbInformation, "Input"
txtRO.SetFocus
Exit Sub

If txtAddress.Text = "" Then
MsgBox "Input 'Address'!", vbInformation, "Input"
txtAddress.SetFocus
Exit Sub

If txtQty.Text = "" Then
MsgBox "Input 'Qty.'!", vbInformation, "Input"
txtQty.SetFocus
Exit Sub

If txtQtyUnit.Text = "" Then
MsgBox "Input 'Qty. (Unit)'!", vbInformation, "Input"
txtQtyUnit.SetFocus
Exit Sub

If txtMake.Text = "" Then
MsgBox "Input 'Make'!", vbInformation, "Input"
txtMake.SetFocus
Exit Sub

If txtMN.Text = "" Then
MsgBox "Input 'Motor No.'!", vbInformation, "Input"
txtMN.SetFocus
Exit Sub

If txtCN.Text = "" Then
MsgBox "Input 'Chasis No.'!", vbInformation, "Input"
txtCN.SetFocus
Exit Sub

If txtLPN.Text = "" Then
MsgBox "Input 'LTo Plate No.'!", vbInformation, "Input"
txtLPN.SetFocus
Exit Sub

If txtSPN.Text = "" Then
MsgBox "Input 'Sidecar Plate No.'!", vbInformation, "Input"
txtSPN.SetFocus
Exit Sub

If txtDayBan.Text = "" Then
MsgBox "Input 'Day Ban'!", vbInformation, "Input"
txtDayBan.SetFocus
Exit Sub

If txtSamahan.Text = "" Then
MsgBox "Input 'Samahan'!", vbInformation, "Input"
txtSamahan.SetFocus
Exit Sub

If txtPH.Text = "" Then
MsgBox "Input 'Plate Holder'!", vbInformation, "Input"
txtPH.SetFocus
Exit Sub

If txtIB.Text = "" Then
MsgBox "Input 'Inspected by'!", vbInformation, "Input"
txtIB.SetFocus
Exit Sub

If txtDay.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
txtDay.SetFocus
Exit Sub

If cmbMonth.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
cmbMonth.SetFocus
Exit Sub

If txtYear.Text = "" Then
MsgBox "Input 'Date'!", vbInformation, "Input"
txtYear.SetFocus
Exit Sub

If txtDateIssued.Text = "" Then
MsgBox "Input 'Date Issued'!", vbInformation, "Input"
txtDateIssued.SetFocus
Exit Sub

If txtCTCN.Text = "" Then
MsgBox "Input 'Community Tax Certificate No.'!", vbInformation, "Input"
txtCTCN.SetFocus
Exit Sub

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

Call AllCaps

If mnuSave.Caption = "Save As" Then
Call Dbase
rs.Update
MsgBox "Editing Complete", vbInformation, "Message"
mnuSave.Caption = "Save"
Else
rs.AddNew
Call Dbase
rs.Update
MsgBox "Saving Complete", vbInformation, "Message"
End If
dg1.Refresh
dg1.Enabled = True
mnuNew.Enabled = True
mnuDelete.Enabled = True
mnuPrint.Enabled = True
mnuSave.Enabled = False
mnuEdit.Enabled = True
Call LockerType(True)
End Sub

Private Sub mnuSearch_Click()
Call ClearAll
Call LockerType(True)
optRO.Value = True
txtROSearch.SetFocus
End Sub

'this is for filling the database
Private Sub Dbase()
rs!RegisteredOwner = txtRO.Text
rs!RO_Address = txtAddress.Text
rs!Qty = txtQty.Text
rs!QtyUnit = txtQtyUnit.Text
rs!Make = txtMake.Text
rs!MotorNo = txtMN.Text
rs!ChasisNo = txtCN.Text
rs!LTOPlateNo = txtLPN.Text
rs!SidecarPlateNo = txtSPN.Text
rs!DayBan = txtDayBan.Text
rs!Samahan = txtSamahan.Text
rs!PlateHolder = txtPH.Text
rs!InspectedBy = txtIB.Text
rs!SDate = txtDay.Text
rs!SMonth = cmbMonth.Text
rs!SYear = txtYear.Text
rs!DateIssued = txtDateIssued
rs!CommunityTaxCertNo = txtCTCN.Text
End Sub

Private Sub optRO_Click()
Call ClearAll
Call LockerType(True)
txtROSearch.Enabled = True
txtSamahanS.Enabled = False
End Sub

Private Sub optSamahan_Click()
Call ClearAll
Call LockerType(True)
txtSamahanS.Enabled = True
txtROSearch.Enabled = False
txtSamahanS.SetFocus
End Sub

Private Sub txtAddress_LostFocus()
txtAddress.Text = UCase(txtAddress.Text)
End Sub

Private Sub txtCN_LostFocus()
txtCN.Text = UCase(txtCN.Text)
End Sub

Private Sub txtCTCN_GotFocus()
With txtCTCN
.SelStart = 0
.SelLength = Len(txtCTCN.Text)
End With
End Sub

Private Sub txtCTCN_KeyDown(KeyCode As Integer, Shift As Integer)
If txtCTCN.Locked = True Then
Beep
End If
End Sub

Private Sub txtCTCN_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case Is = 8, 46, 48 To 57
Case Else: KeyAscii = 0
Beep
End Select
End Sub

Private Sub txtDateIssued_GotFocus()
With txtQtyUnit
.SelStart = 0
.SelLength = Len(txtQtyUnit.Text)
End With
End Sub

Private Sub txtDateIssued_KeyDown(KeyCode As Integer, Shift As Integer)
If txtDateIssued.Locked = True Then
Beep
End If
End Sub

Private Sub txtDateIssued_LostFocus()
txtDateIssued.Text = Format(txtDateIssued, "dd-mm-yy")
End Sub

Private Sub txtDayBan_LostFocus()
txtDayBan.Text = UCase(txtDayBan.Text)
End Sub

Private Sub txtIB_LostFocus()
txtIB.Text = UCase(txtIB.Text)
End Sub

Private Sub txtLPN_LostFocus()
txtLPN.Text = UCase(txtLPN.Text)
End Sub

Private Sub txtMake_LostFocus()
txtMake.Text = UCase(txtMake.Text)
End Sub

Private Sub txtMN_LostFocus()
txtMN.Text = UCase(txtMN.Text)
End Sub

Private Sub txtPH_LostFocus()
txtPH.Text = UCase(txtPH.Text)
End Sub

Private Sub txtQty_LostFocus()
txtQty.Text = UCase(txtQty.Text)
End Sub

Private Sub txtQtyUnit_GotFocus()
With txtQtyUnit
.SelStart = 0
.SelLength = Len(txtQtyUnit.Text)
End With
End Sub

Private Sub txtQtyUnit_KeyDown(KeyCode As Integer, Shift As Integer)
If txtQtyUnit.Locked = True Then
Beep
End If
End Sub

Private Sub txtQtyUnit_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case Is = 8, 46, 48 To 57
Case Else: KeyAscii = 0
Beep
End Select
End Sub

Private Sub txtRO_LostFocus()
txtRO.Text = UCase(txtRO.Text)
End Sub

Private Sub txtROSearch_Change()
If txtROSearch.Text = "" Then
mnuDelete.Enabled = False
mnuEdit.Enabled = False
Set rs.DataSource = Nothing
Set dg1.DataSource = Nothing
Set rs.DataSource = rs
Set dg1.DataSource = rs
dg1.Refresh
Call ClearAll
Else
rs.Filter = "RegisteredOwner like '" & txtROSearch.Text & "%'"
mnuDelete.Enabled = False
mnuEdit.Enabled = False
If Not rs.EOF Then
Call FillObj
mnuDelete.Enabled = True
mnuEdit.Enabled = True
End If
End If
End Sub

Private Sub txtSamahan_LostFocus()
txtSamahan.Text = UCase(txtSamahan.Text)
End Sub

Private Sub txtSamahanS_Change()
If txtSamahanS.Text = "" Then
mnuDelete.Enabled = False
mnuEdit.Enabled = False
Set rs.DataSource = Nothing
Set dg1.DataSource = Nothing
Set rs.DataSource = rs
Set dg1.DataSource = rs
dg1.Refresh
Call ClearAll
Else
rs.Filter = "Samahan like '" & txtSamahanS.Text & "%'"
mnuDelete.Enabled = False
mnuEdit.Enabled = False
If Not rs.EOF Then
Call FillObj
mnuDelete.Enabled = True
mnuEdit.Enabled = True
End If
End If
End Sub

Private Sub txtSPN_LostFocus()
txtSPN.Text = UCase(txtSPN.Text)
End Sub

Private Sub txtYear_GotFocus()
With txtYear
.SelStart = 0
.SelLength = Len(txtYear.Text)
End With
End Sub

Private Sub txtYear_KeyDown(KeyCode As Integer, Shift As Integer)
If txtYear.Locked = True Then
Beep
End If
End Sub

Private Sub txtYear_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case Is = 8, 46, 48 To 57
Case Else: KeyAscii = 0
Beep
End Select
End Sub

hope its not to late... just try to understand it. all the variable the i used, you can change their name as what your variable name is..
^_^

fetch the specific record from access to list box

Hello,
can you just help,
i have made the form with feilds cid,detail where in this the cid gets repeated again and again so what exactly i want that whenever at run time i pass the value for cid in textbox and on the click of cmd button the query result of repeated details of specific cid should get display in listbox in sequence.
the following query is written in click event of cmd button:
dim varcid as integer
varcid=cint(txtcid.text);
"select detail from official where cid="&varcid
thank you

dear how can i browse table

dear
how can i browse table names with tree control.
i need your help
thank you

dear. how can i browse

dear.
how can i browse tables in ms access without converting it in vb.
i need your help.
if it's possible explain it from begining in details please.
thanks a lot

go to project menu select

go to project menu select DataEnvironment , connections->properties-> select database engine microsoft jet 4.0 oledb provider then click next, in the database source select your database folder then click on test connection button, it shows connection is successful, click ok. now right click on connection1 and select add new command, right click on add new command and select properties. in general tab command name should be selected as command1 and connection1 in connection. now select database object as table. now apply and ok. you will see all tables you have created in ms access will display in vb6...
thanks

PROGRAMME IN VB

I AM NEW IN VB PLEASE DISPLAY
I WANT LEDGER ACCOUTN EG.
1 DEPOSIT AMOUNT ,WITHDWAW AMOUNT
DISPLAY,FIND ACCOUTN INFO MONTHLY,YEARLY ACWISE

it's easy to get all the things using database

First you create the database with name "bank" then create table with name "account" in this there are few feilds like "ac_no" with datatype as integer, a_name with datatype as string, balance with datatype as double , withdraw with datatype as double and save it then

create your form with labels and respective feilds of data you have in your "account" table and add more text box for for making the user to enter the amount for depositing in his or her a/c no. or withdrawing the amount from his or her a/c no. then make then cmdbutton with name "deposit", "withdraw", "monthly report" and "yearly report" "save".
take an adodc control on form go to its properties by right clicking on the control then select the provider from list to "microsoft jet oledb 4.0 provider" then select the database name from the drive you have stored further select the table name from list of recordsourse then test connection is it gives the successful tested then press ok.

now what you have to do is that select the feild from your form and go to its properties in right side window i.e. "properties window" set the record source to adodc1 and record type to table name after getting it done select specfic feild and select the feild from the properties window of its respective feild after getting it all done for all feilds, now come to cmddeposit in this you have to pass an query to make an change in balance amount i.e on click event of deposit pass query as "select name, balance from account where ac_no"=&cint(txtac_no.text); where here you are passing the number from user input from the form through the textbox before to this set the cmdtype="text" only then then qquery will get executed after this pass another query for update for balance amount i.e. "update account set balance=balance+"cint(txtdeposit.text); sismillary you have to do for withdraw also

importing data between database access 97 using vb6

hi,

I have an application to copy records from one existing table in database to other table in other database access 97 having the same structure using vb6
will i have some help please

OK IS POSSIBLE 1ST OPEN

OK IS POSSIBLE
1ST OPEN SOURCE DATABASE AND
EXPORT RECORDS TO TARGET DATABASE

how to access autonumber

how to access autonumber field in msaccess data base to visual basic 6.0 before entering data in the form please send us the code

with regards
Satish.S

vb6.0

HI...................... I M LOOKING 4 FULL DESCRIPTION OF DATABASE CONNECTIVITY VB6.0 WITH MS ACCESS.

convert to report

how to convert a report in visual basic to microsoft excel and microsoft access.....

help me...

And insert a data access in visual basic using the table in access..... reply.

thanks......

how to search a ms access database with date using vb

how to search fields in ms access database using date and time picker control in vb6.0

can i ask something???

how to connect VB6 to microsoft office access 2007???

How to link the MS Access Database to VB6

Hi to all,

I'm new here and i want someone help me give an option on how to link the MS access database to VB6.. How I can add data, edit data, delete data in the MS Access database using the VB6 programs... anyone can give me options, whats the best way to add, edit, delete and search data using primary... thank you for your favorable response on this matter...

regards,

john

Connecting Ms Access to VB6

Convert Database to Access 97 and use the the following code:
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(App.Path & "\databasename.mdb")
Set rs = db.OpenRecordset("tablename")

to add a record
WITH RS
.ADDNEW
.FIELDS(N) = TEXTBOX.TEXT
.UPDATE
END WITH

to edit a record
WITH RS
.EDIT
.FIELDS(N) = NEWVALUE
.UPDATE
END WITH

to delete a record
WITH RS
.EDIT
.DELETE
END WITH

Access 2013

How about Connect Access 2013 to vb 6.0? I cant find when i can connect using microsoft Access 2013,pls help me in this.thank u

how to fetch a data from database and display it on ms excel

Hi...
please let me know how to fetch a data from database and display it on ms excel
tq

solve the quary

i cannect the database in access by ADO data controler 6.0 ....and create a databaseand here create two tables where from we retrive the value by creation relation ship table ......
please give the quary how we retrive the value.....

Crystal report help with vb6

I create one project purchase and sales in mobile store.i want monthly report.How to generate that.And we get starting and ending date get details from starting date to ending date please help me. i am a new comer.

Hai, im looking for this

Hai, im looking for this answer too... im having problem that same with u.. have u got the answer? lets share :)

Tutorial is informative

Tutorial is informative specially for beginners like me!!!

trade project(timetable management system)

hi
I need some assistance in developing this software.thankyou.

Excellent Tutorial

Hi, I really appreciate your tutorial. Although I am a beginner in database design with VB, atleast I have acquired more hands-on experiences than this. But I always have problems at a particular point. In this case, the problem is thus:

I am trying to retrieve the values of a table (Login) and then use them to program access login. The table has 2 fields (Username and Password).
After creatiing a connectiion, I will query the table and store the values of the username and password fields in 2 global variables (usname and psword).
I will compare the usname and psword variables with the values of txtUsername.text and txtPassword.text fields on the form.
If they match, the next form (MainWindow.show) method will execute, else the error message will display and setfocus again on txtUsername.text.

I am having problems with this. Please help me. Thanks

pls review the code below:

Private Sub cmdOK_Click()
'establish a connection and check for correct password and username

Dim uname$, pword$
usname = txtUsername.Text
psword = txtPassword.Text

Set myConnection = New ADODB.Connection

myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=C:\projectfile971.mdb"

myConnection.Open

' Create a Recordset by executing a SQL statement
strSQL = "SELECT * FROM Login WHERE Username = " & txtUsername

Set myRecordSet = myConnection.Execute(strSQL)

usname = myRecordSet!Username
psword = myRecordSet!Password

'If usname = txtUsername And psword = txtPassword Then

'If usname = txtUsername And psword = txtPassword Then

' Close the recordset and connection.

Me.Hide
MainWindow.Show

' Else
MsgBox "Invalid Password, or username. Please try again!", vbCritical + vbOKOnly, "Login Error?"
txtUsername = ""
txtPassword = ""
txtUsername.SetFocus
SendKeys "{Home}+{End}"

'end If
myRecordSet.Close
myConnection.Close
End Sub

Connect to Access in VB6

'I add a module called dBase, and add the following code:

'dbase Declarations

Public rs As New Recordset
Public cn As New connection
Public cmd As String

Public Sub Go()

Call dbase
Call connection

Set rs = New ADODB.Recordset

End Sub

Public Sub dbase() ' call this procedure to connect to dbase
cmd = "Provider=microsoft.jet.OLEDB.4.0; ;" & "Data Source = " & App.Path & "\YourDb.mdb;Jet OLEDB:Database Password=Password1"
End Sub
Public Sub connection() ' run this procedure to open a connected dbase
''
Set cn = New ADODB.connection
With cn
.ConnectionString = cmd 'to connect to Access dbase
.Open
End With
End Sub

'Then, simly call the connection by typing':

dbase.go

'after this, you can simply access the access db:

'Example to populate listview called PlayList pulling data from the MyList table

with rs
sql = "select * from MyList"
.Open sql, cn, adOpenForwardOnly, adLockOptimistic
If .EOF Then
.Close
Else
Do While Not .EOF
Set itmx = Playlist.ListItems.Add(, , CStr(rs!ID))
itmx.SubItems(1) = CStr(rs!MySong)
itmx.SubItems(2) = CStr(rs!Singer)
.MoveNext
Loop
.Close
End If
end with

':)

Try this

I Think u nid this. U simply try this code to create open connection to a recordset

'Create and open the recordset
rsLogIn.Open SQL, cnLogIn, adOpenDynamic
If LogIn.BOF And rsLogIn.EOF Then
Exit Sub
End If

paroll system

hi
i wan to see the idea about one organaizations paroll system
thank u

Application

I wanted a way of connecting a database table to a VB environment

Create Table

dsdi wont to know that when i "Dim MyDB As Database" . i must references ? in my project

if you want to declare MyDB

if you want to declare MyDB as Database. first you should choose a reference "Micrisoft DAO 3.6 Object Library

how to fetch already created database in access

hi
how can i fetch the data from a databse which allready created through MS access