access db help

8/4/2019 Access DB Help

1/59

Visual Basic & ADO TutorialLevel:

This tutorial describes how you can use ADO objects in VB6. Now days, almost any time you write full fledged

database application you will want to use ADO. Along with this, as your applications become more and more complex

you will probably not want to rely on Visual Basic's data controls, but instead use the ADO objects directly. Read on to

find out exactly how this can be done.

Originally Written By TheVBProgramer.

The "Alphabet Soup" of Database AccessPrior to VB6 and the introduction of ADO (ActiveX Data Objects), VB programmers would generally use DAO (Data

Access Objects) to interact with local databases such as MS Access and use RDO (Remote Data Objects) to interact with

client/server databases such as Oracle and SQL Server. The concept behind Visual Basic ADO was Universal Data

Access (UDA), where one database access method could be used for any data source; it was designed to replace both

DAO and RDO. DAO remains a viable technology for interacting with MS Access databases as it is faster than ADO for

that purpose; however, ADO is more flexible using ADO, one could develop a prototype database application using MS

Access in the back-end, and with a "flick of the wrist" ( i.e., with very little coding changes) "upsize" that same application

to use Oracle or SQL Server. As far as RDO is concerned, no new versions of it have been developed beyond the version

that shipped with Visual Basic, and there are no future plans for it.In the VB4 and VB5 worlds, RDO was the main method used to interact with client/server databases. RDO works perfectly

fine with VB6, so when folks migrated their VB5 applications over to VB6, little or no coding changes were required.

However, ADO is the preferred method of database access for new VB6 applications . About this TutorialThis tutorial presents three small sample applications using ADO. All three applications use a local MS Access database. The first sample application introduces the ADO Data Control (ADODC) which demonstrates a "quick and dirty" way to

connect to a remote database. The second and third applications use ADO code: the second allows navigation and

searching of a database table; the third allows navigation and updating on a database table. All three connect to an ODBC

Data Source, which must be set up through the Windows Control Panel. How to do this is described below.Note: If you have previously set up a DSN for the Biblio database as described in the previous topic on RDO, you can skip

the section on setting up an ODBC data source and resume here.
http://www.thevbprogrammer.com/http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_1http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_1http://www.thevbprogrammer.com/


2/59

Follow the steps below to set up an ODBC Data Source (this process is also called "setting up a DSN", where "DSN"

stands for "Data Source Name"). These steps assume Windows 2000 for the operating system. On other versions of

Windows, some steps may vary slightly. Via Windows Control Panel, double-click on Administrative Tools, then Data Sources (ODBC). The ODBC Data

Source Administrator screen is displayed, as shown below. Click on the System DSN tab.

Click the Add button. The Create New Data Source dialog box will appear. Select Microsoft Access Driver(*.mdb) from the list and click the Finish button.


3/59

The ODBC Microsoft Access Setup dialog box will appear. For Data Source Name, type Biblio. If desired, you cantype an entry for Description, but this is not required.

Click the Select button. The Select Database dialog box appears. On a default installation of VB6 or Visual Studio 6,the BIBLIO.MDB sample database should reside in the folder C:\Program Files\Microsoft Visual Studio\VB98.

Navigate to that folder, select BIBLIO.MDB from the file list, and click OK.

Note: If VB was installed in a different location on your system, navigate to the appropriate folder. If you do not have the

BIBLIO.MDB sample database file on your system at all, you can download ithere. In that case, copy the file to

the folder of your choice, and navigate to that folder to select the database for this step. When you are returned to the ODBC Microsoft Access Setup screen, the database you selected should be reflected

as shown below. Click OK to dismiss this screen.
http://www.vb6.us/files/VBPrograms/BIBLIO.ziphttp://www.vb6.us/files/VBPrograms/BIBLIO.ziphttp://www.vb6.us/files/VBPrograms/BIBLIO.ziphttp://www.vb6.us/files/VBPrograms/BIBLIO.zip


4/59

When you are returned to the ODBC Data Source Administrator screen, the new DSN should appear as shown

below. Click OK to dismiss this screen.

At this point, the Biblio database is ready to be used with RDO in the sample application.


5/59

Sample Application 1: Using the ADO Data Control (ADODC)To build the first sample application, follow the steps below. Start a new VB project, and from the Components dialog box (invoked from the Project -> Components menu),

select Microsoft ADO Data Control 6.0 (SPx) as shown below and click OK.

The ADO Data Control should appear in your toolbox as shown below:


6/59

Put an ADO Data Control on your form, and set the properties as follows:

Property ValueName adoBiblioDataSourceName BiblioSQL select * from authors

Now put three text boxes on the form, and set their Name, DataSource, and DataField properties as follows:

Name DataSource DataFietxtAuthor adoBiblio AuthortxtAuID adoBiblio Au_IDtxtYearBorn adoBiblio Year Bo

Save and run the program. Notice how it works just like the other data control. Now change the SQL property of the data control to select * from authors order by author and run the program

again. Notice the difference. Change the SQL property back to what it was and add three command buttons to the form, and set their Name and

Caption properties as follows:Name CaptioncmdNameOrder Order by NamecmdYearOrder Order by YearcmdIDOrder Order by ID


7/59

Put the following code in the cmdNameOrder_Click event:adoBiblio.SQL = "select * from authors order by author"adoBiblio.Refresh

Put the following code in the cmdYearOrder_Click event:adoBiblio.SQL = "select * from authors order by [year born]"adoBiblio.Refresh

Put the following code in the cmdIDOrder_Click event:adoBiblio.SQL = "select * from authors order by au_id"adoBiblio.Refresh

Save and run the program and see what happens when you click the buttons.A screen-shot of the sample app at run-time is shown below:

Download the project files for this sample applicationhere.
http://www.vb6.us/files/VBPrograms/UsingADO/SampleApp1.ziphttp://www.vb6.us/files/VBPrograms/UsingADO/SampleApp1.ziphttp://www.vb6.us/files/VBPrograms/UsingADO/SampleApp1.ziphttp://www.vb6.us/files/VBPrograms/UsingADO/SampleApp1.zip


8/59

Sample Applications 2 and 3: Using ADO Code

Note: If you have previously downloaded and set up a DSN for the Property database as described in the previous topic

on RDO, you can skip the set up steps below and resumehere.

Sample applications 2 and 3 use a database called PROPERTY.MDB and can be downloadedhere.

The Property database contains just one table called "Property". The columns of this table are defined as follows:

Column Name Data Type NotPROPNO Number (Long Integer) A number that uniquely identifies the pro

as the Primary Key (although it is not de

database).EMPNO Number (Long Integer) A number that identifies the real estate

system, this would be the foreign key to

Employee table (such a table is not pres

ADDRESS Text (20) The street address of the property.CITY Text (15) The city where the property is located.STATE Text (2) The state where the property is located ZIP Text (5) The zip code where the property is locaNEIGHBORHOOD Text (15) The descriptive name of the neighborhoHOME_AGE Number (Long Integer) Age in years of the home. (A better tabl

field be the date in which the property w

compute the age based on the current d

BEDS Number (Long Integer) Number of bedrooms in the property.BATHS Number (Single) Number of bathrooms in the property (a

indicating 2 bathrooms i.e. 2 full ba

FOOTAGE Number (Long Integer) The footage of the property.ASKING Number (Long Integer) Asking price of the property in whole doBID Number (Long Integer) Bid amount of the potential buyer in whoSALEPRICE Number (Long Integer) Sale price (amount the property actuallyBefore coding or running sample application 2 or 3, you must set up an ODBC data source as was done for the previous

sample application.
http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_2http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_2http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_2http://www.vb6.us/files/VBPrograms/Property.ziphttp://www.vb6.us/files/VBPrograms/Property.ziphttp://www.vb6.us/files/VBPrograms/Property.ziphttp://www.vb6.us/files/VBPrograms/Property.ziphttp://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#SAMP_APP_2


9/59

After downloading the file, move it to the folder of your choice. Then follow the exact same steps as before to set up the

DSN, with these two exceptions:

(1) On the ODBC Microsoft Access Setup dialog box, type PropDB for the Data Source Name.

(2) In the Select Database dialog box, navigate to the location where you have placed the PROPERTY.MDB file.

Sample Application 2

To build Sample Application 2, start a new VB project and perform the following steps.

From the Project -> References menu, check Microsoft ActiveX Data Objects 2.x(where x is the highest versionthat you have on your system) and click OK.

This project uses the StatusBar control, so include the Microsoft Windows Common Controls 6.0(SP6) fromthe Components dialog box, accessed from the Project -> Components menu.


10/59

Create the form shown below. The names of the text boxes in the top frame are shown in the form. Set the Enabled

property of the frame to False, which will automatically disable all of the textboxes within it, which is desired because

this application does not allow updating of the data. The settings for the other controls are given below.


11/59

The navigation buttons have the following properties:

Name CaptioncmdMoveFirst

The text box in the middle of the form has the following properties:

Name txtCurrentQueryMultiLine True


12/59

Locked True

The command buttons have the following properties:

Name Caption EnablecmdAllData Reload All Records TruecmdGetData Run Query Now False

In the "Select Criteria" frame:

The check boxes are an array:Name CaptionchkCriteria(0) EmpNochkCriteria(1) CitychkCriteria(2) State

The labels are also an array:Name

Caption

Enab

lblCriteria(0) = FalselblCriteria(1) Like FalselblCriteria(2) Like False

The textboxes are also an array:Name Caption EnabtxtCriteria(0) EmpNo FalsetxtCriteria(1) City FalsetxtCriteria(2) State False

Place the StatusBar on the form and set its Style property to 1 sbrSimple.


13/59

2. Code the General Declarations section as shown below. Here,

two ADO objects, ADODB.Connection and ADODB.Recordset, are defined at the form level.

The ADODB.Connection object represents an open connection to a data source and a specific database on that data

source, or an allocated but as yet unconnected object, which can be used to subsequently establish a connection.

The ADODB.Recordset object represents the rows that result from running a query,

Option Explicit

Dim mobjADOConn As ADODB.Connection

Dim mobjADORst As ADODB.RecordsetDim mstrSQL As String

3. Code the Form_Load event. Here, the connection object variable mobjADOConn is made available for use by setting it

to a new instance of ADODB.Connection. Then, theConnectionString property and the Open method of the

ADODB.Connection object are used.

The ConnectionString property takes a string with various arguments delimited by semicolons. When using a DSN as we

are in this sample application, you typically need just the DSN name, the user id, and the password. The Open

method then opens the connection to the database.

'----------------------------------------------------------------------------- Private Sub Form_Load()'-----------------------------------------------------------------------------

'set up the form and connect to the data sourceOn Error GoTo LocalError'center the form:Me.Top = (Screen.Height - Me.Height) / 2


14/59

Me.Left = (Screen.Width - Me.Width) / 2' Connect to the Property database:Set mobjADOConn = New ADODB.ConnectionmobjADOConn.ConnectionString = "DSN=PropDB;Uid=admin;Pwd=;"mobjADOConn.OpenCall cmdAllData_Click

Exit Sub

LocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

4. Code the cmdAllData_Click event, which sets or resets the ADODB.Recordset object with a query to display all the

rows in the table. The opening of the recordset takes place in the OpenNewRecordset subprocedure, called from

this event procedure.

'----------------------------------------------------------------------------- Private Sub cmdAllData_Click()'-----------------------------------------------------------------------------

On Error GoTo LocalErrorDim lngX As Long

'select or reload the data to be displayed:mstrSQL = "select * from property"Call OpenNewRecordset'load data into the text boxesCall DataLoad


15/59

' reset the state of the search criteria controlsFor lngX = 0 To 2chkCriteria(lngX).Value = vbUncheckedNextExit SubLocalError:MsgBox Err.Number & " - " & Err.Description

End Sub

5. Create the user-defined subprocedure OpenNewRecordset.

Here, the recordset object mobjADORst is made available for use by setting (or resetting) it to a new instance of

ADODB.Recordset.

The CursorLocation property is then set to the built-in constant adUseClient. The term "cursor" refers to the temporary

rows of a recordset. The cursor location determines whether the cursor is stored on the client or the server,

specified by the values adUseClient and adUseServer, respectively. Server-side cursor (adUseServer) is the

default. There are tradeoffs using both types of cursors. Client-side cursors can take a long time to build becausethe data must be pulled over to the client, but once built, traversing the cursor is usually very fast. Client-side

cursors often support more features than server-side cursors (the reason this sample application is using a client-

side cursor is because we want to use AbsolutePosition property later, which only works with a client-side

cursor). On the other hand, server-side cursors usually build faster but often support fewer features that client-

side cursors.

The Open method of the recordset is then executed. The Open method has the following syntax:

RecordsetObject.Open Source, ActiveConnection, CursorType, LockType, Options

The Sourceargument is an optional variant that evaluates to a valid Command object, SQL statement, table name, stored

procedure call, or filename of a persisted recordset.


16/59

The ActiveConnectionargument is an optional variant that evaluates to a valid Connection object variable name or a

string containing connection string parameters.

The CursorTypeargument is an optional value that determines the type of cursor that the provider should use when

opening the recordset. The possible values and their descriptions are given below:

Value DescriptionadOpenForwardOnly (default) Used to open a forward-only cursor. Forward-only cursors create static

forward-only cursor is not directly updateable and can only be scrolled from beg

"Move" method that can be used with this type of cursor). Forward-only cursors

feature limitations. Forward-only cursors are sometimes referred to as firehosec

adOpenStatic Used to open a static cursor. A static cursor is a static copy of the data in the daother users propagate to the recordset; the recordset never changes. Note: Clie

application) use only adOpenStatic for CursorTypes regardless of which Cursor

adOpenDynamic Used to open a dynamic cursor. A dynamic cursor is a "live" recordset, meaningdeletions by other users affect the recordset. Dynamic-cursor recordsets suppor

bookmarks are supported by the provider). Dynamic cursors offer the most featu

increased overhead.adOpenKeyset Used to open a keyset cursor. Keyset cursors are like dynamic cursors, except a

the recordset. The recordset is affected by changes and deletions, however. The LockTypeargument is an optional value that determines the type of locking that the provider should use when

opening the recordset. The possible values and their descriptions are given below:

Value DescriptionadLockReadOnly (default) Specifies read-only locking. Records can be read, but data cannot be

method used with static cursors and forward-only cursors.adLockPessimistic Specifies pessimistic locking. The provider does what is necessary to ensure s

records at the data source immediately upon editing.adLockOptimistic Specifies optimistic locking. The provider locks records only when you call the adLockBatchOptimistic Specifies optimistic batch locking. Records are locked in batch update mode, a

option is required for client-side cursors.The Optionsargument is an optional Long value that indicates how the Source should be evaluated. The possible values

and their descriptions are given below:

Value DescriptionadCmdText Indicates that the provider should evaluate CommandText as a textual definitio


17/59

statements.adCmdTable Indicates that the provider should evaluate CommandText as a table.adCmdStoredProc Indicates that the provider should evaluate CommandText as a stored proceduadCmdUnknown Indicates that the type of command in the CommandText argument is not know

interpret it. Typically results in poor performance.adExecuteAsync Indicates that the command should execute asynchronously.adFetchAsync Indicates that the remaining rows after the initial quantity specified in the Cache

asynchronously.'----------------------------------------------------------------------------- Private Sub OpenNewRecordset()'----------------------------------------------------------------------------- Set mobjADORst = New ADODB.RecordsetmobjADORst.CursorLocation = adUseClientmobjADORst.Open mstrSQL, mobjADOConn, adOpenStatic, , adCmdText

' display current querytxtCurrentQuery.Text = mstrSQL

End Sub

6. Create the user-defined subprocedure DataLoad. This subprocedure gets the data from the recordset and puts each

field into a text box. Data from the recordset is accessed via the Fields collection.

The Fields collection in ADO works identically to the Fields collection in DAO. A field can be referenced with or withoutspecifying Fields, either by the field name in quotes or by its ordinal position in the resultset. The field can also be

referenced with the bang (!) operator. All of the following would be valid ways of referencing the field "propno":

mobjADORst.Fields("propno") mobjADORst ("propno")mobjADORst.Fields(0)


18/59

mobjADORst(0)mobjADORst!propno

'----------------------------------------------------------------------------- Private Sub DataLoad()'----------------------------------------------------------------------------- On Error GoTo LocalError

'copy the data from the recordset to the text boxes:txtPropNo.Text = mobjADORst.Fields("propno")txtEmpNo.Text = mobjADORst.Fields("empno")txtAddress.Text = mobjADORst.Fields("address")txtCity.Text = mobjADORst.Fields("city")txtState.Text = mobjADORst.Fields("state")txtZip.Text = mobjADORst.Fields("zip")Call SetRecNumExit SubLocalError:MsgBox Err.Number & " - " & Err.Description

End Sub

7. Create the user-defined subprocedure SetRecNum. This sub displays the number of the current record at the bottom of

the screen. The AbsolutePosition andRecordCount properties of the Recordset are used here.

The AbsolutePosition property specifies the current row in a recordset. Note: For AbsolutePosition to return a valid value

with Access (Jet) databases (like the one used in the sample application), the CursorLocation must be set to

adUseClient. An invalid value (-1) will be returned if adUseClient is specified.


19/59

The RecordCount property the total number of rows in the recordset. Note: RecordCount will not return a valid value with

all cursor types (for example, RecordCount will return -1 with a forward-only cursor.) To ensure a valid

RecordCount value, use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use

a client side cursor.

'----------------------------------------------------------------------------- Private Sub SetRecNum()'----------------------------------------------------------------------------- StatusBar1.SimpleText = "row " & mobjADORst.AbsolutePosition _& " of " & mobjADORst.RecordCountEnd Sub

8. Code the events for the navigation buttons as shown below, using the recordset "Move" methods to move to the first,

last, next, or previous record, respectively.

'----------------------------------------------------------------------------- Private Sub cmdMoveFirst_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError

mobjADORst.MoveFirstCall DataLoadExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMoveLast_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError


20/59

mobjADORst.MoveLastCall DataLoadExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMoveNext_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError

mobjADORst.MoveNextIf mobjADORst.EOF ThenBeepmobjADORst.MoveLastEnd IfCall DataLoadExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMovePrevious_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError


21/59

mobjADORst.MovePreviousIf mobjADORst.BOF ThenBeepmobjADORst.MoveFirstEnd IfCall DataLoadExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

9. When one of the check boxes is clicked, the label and text box next to it should be enabled (or disabled, if clicking the

check box unchecks it). Note also that the cmdGetData button (the one with the "Run Query Now" caption) should

only be enabled if one of the checkboxes is checked.

'----------------------------------------------------------------------------- Private Sub chkCriteria_Click(Index As Integer)'-----------------------------------------------------------------------------

' disable the 'Run Query Now' buttoncmdGetData.Enabled = False

'when the user clicks on a check box, enable the label and text'box that go with it.If chkCriteria(Index).Value = vbChecked ThentxtCriteria(Index).Enabled = TruelblCriteria(Index).Enabled = TruetxtCriteria(Index).SetFocus txtCriteria(Index).SelStart = 0txtCriteria(Index).SelLength = Len(txtCriteria(Index).Text)


22/59

' enable the 'Run Query Now' button only if a box is checked.cmdGetData.Enabled = TrueElsetxtCriteria(Index).Enabled = FalselblCriteria(Index).Enabled = FalseEnd IfEnd Sub

10. After the user has selected which fields to use and entered values in the text boxes, they click the cmdGetData button

to create a new recordset with new data. Note that if the user selects (checks) a field, but does not enter search

criteria in the corresponding textbox, an error message is generated and the query is not run.

'----------------------------------------------------------------------------- Private Sub cmdGetData_Click()'-----------------------------------------------------------------------------

'run the query that the user has createdOn Error GoTo LocalError

Dim blnFirstOne As BooleanblnFirstOne = TruemstrSQL = "select * from property where "If chkCriteria(0).Value = vbChecked ThenIf (txtCriteria(0).Text = "") Or (Not IsNumeric(txtCriteria(0).Text)) ThenMsgBox "Employee number is missing or non-numeric. Query not run.", _vbExclamation, _"ADO Example"Exit SubEnd IfblnFirstOne = False


23/59

mstrSQL = mstrSQL & "empno = " & txtCriteria(0).TextEnd IfIf chkCriteria(1).Value = vbChecked ThenIf txtCriteria(1).Text = "" ThenMsgBox "City criteria is missing. Query not run.", _vbExclamation, _"ADO Example"Exit SubEnd IfIf blnFirstOne = False ThenmstrSQL = mstrSQL & " and"End IfblnFirstOne = FalsemstrSQL = mstrSQL & " city like '" & txtCriteria(1).Text & "'"End IfIf chkCriteria(2).Value = vbChecked ThenIf txtCriteria(2).Text = "" ThenMsgBox "State criteria is missing. Query not run.", _vbExclamation, _"ADO Example"Exit SubEnd IfIf blnFirstOne = False ThenmstrSQL = mstrSQL & " and"End IfblnFirstOne = FalsemstrSQL = mstrSQL & " state like '" & txtCriteria(2).Text & "'"End IfOpenNewRecordset


24/59

'make sure that the query did not return 0 rows:If mobjADORst.EOF ThenMsgBox "Your query (" & mstrSQL & ") returned no records! " _& "The default query to return all records will now be rerun.", _vbExclamation, _"ADO Example"'reload the form with all the recordscmdAllData_ClickElseMsgBox "Your query returned " & mobjADORst.RecordCount & " records.", _ vbInformation, _"ADO Example"'load data into the text boxesCall DataLoadEnd IfExit SubLocalError:MsgBox Err.Number & " - " & Err.Description

End Sub

11. Save and run. Note: When entering the "Like" criteria for City and/or State, you can use the wildcard character % to

represent any number of characters and the wildcard character _ (underscore) the represent a single character.

For example, entering "M%" for the City criteria would return all rows where the city field begins with the letter "M"


Sample Application 3

Sample Application 3 demonstrates how to add, update, and delete records with ADO.


25/59

When the application is first run, the user is prompted to enter a minimum asking price to possibly limit the number of

records they want to work with (i.e., "I only want to work with properties that are selling for $200,000 or more). If the user

wants to work with all properties, they would simply accept the default of 0 from the prompt. If the user clicks the Cancel

button, the application will end.

Once the user has entered the minimum asking price, the main screen of the application is displayed. Initially, the screen

is in "browse" mode, where the user can use the navigation buttons to move to the first, previous, next or last record. The

data cannot be edited in this mode. If they want to initiate an add or an update, delete a record, or exit the application,

they may do so via the appropriate button. Saving or cancelling is not applicable in this mode, so those buttons are

disabled.

If the user clicks the Add button, the fields on the screen are enabled and cleared, and the user can enter the information

for the new property. All buttons except Save and Cancel are now disabled. After the user has made entries in the fields,

he or she would click Save to add the new record to the database table, or, if they changed their mind, would click Cancel


26/59

to discard the new record. In either case (clicking Save or Cancel) the user is returned to browse mode. When Save is

clicked, the application validates the entries and will only save the record if all fields pass edit (otherwise, a message will

appear indicating the problem entry and focus will be set to the problem field).

If the user clicks the Update button, the fields on the screen are enabled and the user can modify any or all of the fields

(except for the Property Number, which is the primary key of the table). All buttons except Save and Cancel are now

disabled. After the user has made modifications in the desired fields, he or she would click Save to update the record to

the database table, or, if they changed their mind, would click Cancel to discard the changes. In either case (clicking Save

or Cancel) the user is returned to browse mode. When Save is clicked, the application validates the entries and will only

save the record if all fields pass edit (otherwise, a message will appear indicating the problem entry and focus will be set

to the problem field).


27/59

If the user clicks the Delete button, the user is asked to confirm that they want to delete the current record. If they respond

Yes, the record is deleted from the database table, and the main screen shows the next record in the table.

To build Sample Application 3, start a new VB project and perform the following steps.

From the Project -> References menu, check Microsoft ActiveX Data Objects 2.xLibrary and click OK.


28/59

This project uses the StatusBar control, so include the Microsoft Windows Common Controls 6.0(SP6) fromthe Components dialog box, accessed from the Project -> Components menu. Check this item and click OK.


29/59

Create the form shown below. The settings for the various controls are given below.

There are nine textboxes in the main frame of the form. The names and MaxLength settings for these are givenbelow:

Name PropertiestxtPropNo MaxLength: 5txtEmpNo MaxLength: 4txtAddress MaxLength: 20txtCity MaxLength: 15txtState MaxLength: 2txtZip MaxLength: 5txtBeds MaxLength: 1txtBaths MaxLength: 3 (allows fractional amount, like 1.5)txtAsking MaxLength: 0 (not specified)

Set up the Command Buttons as follows:


30/59

Name CaptioncmdMoveFirst cmdAdd AddcmdUpdate UpdatecmdDelete DeletecmdSave SavecmdCancel CancelcmdExit Exit

All controls on your form should have their TabIndex property set such that the tabbing order is correct. Add a Module to the project, name it modCommon, and enter the code shown below. The code contains procedures

described as follows:

CenterForm Sub to center a form on the screenValidKey Function to validate a keystroke for use in the KeyPress event of a textboxConvertUpper Function to convert an alphabetic character entered in a textbox to uppercaseSelectTextBoxText Sub to highlight the text of a textbox when it receives focus. Used in the GotFoTabToNextTextBox Sub to "autotab" from one textbox to another when maximum number of chara

textbox has been reached.UnFormatNumber Function to strip out non-numeric characters (dollar signs, commas, etc.) from

Option Explicit

Public Const gstrNUMERIC_DIGITS As String = "0123456789"Public Const gstrUPPER_ALPHA_PLUS As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ,'-"


31/59

Public gblnPopulating As Boolean

'------------------------------------------------------------------------ Public Sub CenterForm(pobjForm As Form)'------------------------------------------------------------------------

With pobjForm.Top = (Screen.Height - .Height) / 2.Left = (Screen.Width - .Width) / 2End With

End Sub

'------------------------------------------------------------------------ Public Function ValidKey(pintKeyValue As Integer, _pstrSearchString As String) As Integer'------------------------------------------------------------------------

' Common function to filter out keyboard characters passed to this' function from KeyPress events.'' Typical call:' KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)'

If pintKeyValue < 32 _Or InStr(pstrSearchString, Chr$(pintKeyValue)) > 0 Then'Do nothing - i.e., accept the control character or any key


32/59

' in the search string passed to this function ...Else'cancel (do not accept) any other key ...pintKeyValue = 0End If

ValidKey = pintKeyValue

End Function

'------------------------------------------------------------------------ Public Function ConvertUpper(pintKeyValue As Integer) As Integer'------------------------------------------------------------------------

' Common function to force alphabetic keyboard characters to uppercase ' when called from the KeyPress event.

' Typical call:' KeyAscii = ConvertUpper(KeyAscii)'

If Chr$(pintKeyValue) >= "a" And Chr$(pintKeyValue)


33/59

'----------------------------------------------------------------------------- Public Sub SelectTextBoxText(pobjTextbox As TextBox)'-----------------------------------------------------------------------------

With pobjTextbox.SelStart = 0.SelLength = Len(.Text)End With

End Sub

'----------------------------------------------------------------------------- Public Sub TabToNextTextBox(pobjTextBox1 As TextBox, pobjTextBox2 As TextBox)'-----------------------------------------------------------------------------

If gblnPopulating Then Exit SubIf pobjTextBox2.Enabled = False Then Exit SubIf Len(pobjTextBox1.Text) = pobjTextBox1.MaxLength ThenpobjTextBox2.SetFocusEnd If

End Sub

'----------------------------------------------------------------------------- Public Function UnFormatNumber(pstrNumberIn As String) As String'-----------------------------------------------------------------------------

Dim lngX As LongDim strCurrChar As String


34/59

Dim strNumberOut As StringstrNumberOut = ""For lngX = 1 To Len(pstrNumberIn)strCurrChar = Mid$(pstrNumberIn, lngX, 1)If InStr("0123456789.", strCurrChar) > 0 ThenstrNumberOut = strNumberOut & strCurrCharEnd IfNextUnFormatNumber = strNumberOut

End Function

Code the General Declarations section as shown below. Here, as in the previous sample application, two ADO objectvariables, mobjADOConn and mobjADORst, are defined at the form level, as are some other form-level variables

that will be needed.

Option Explicit

Dim mobjADOConn As ADODB.ConnectionDim mobjADORst As ADODB.RecordsetPrivate mstrSQL As StringPrivate mdblMinAsking As DoublePrivate mblnUpdatePending As BooleanPrivate mstrUpdateType As String

Private mavntUSStates As Variant

Code the Form_Load event as shown below. In it, a programmer-defined Sub named GetMinimumAsking is called(that routine is the one that displays the initial prompt to the user to enter the minimum asking price of the properties

they want to work with). Then, the variant array mavntUSStates is loaded with the 50 US state abbreviations, needed

for validating the state input by the user. This is followed by a call to the CenterForm sub. Then, the ADO connection


35/59

object (mobjADOConn) is instantiated, its ConnectionString property is set, and the Open methodis invoked so that

we can use the Property database in the application. This is followed by a call to the programmer-defined

Sub GetPropertyData (which runs the query to create the recordset that will be used to browse the Property table

records), followed by a call to the programmer-defined Sub SetFormState (which enables and disables controls at the

appropriate time).

'----------------------------------------------------------------------------- Private Sub Form_Load()'----------------------------------------------------------------------------- On Error GoTo LocalError' obtain the minimum asking price for the properties to be worked withGetMinimumAsking' load the array of states to be used for validationmavntUSStates = Array("AK", "AL", "AR", "AZ", "CA", "CO", "CT", "DC", _ "DE", "FL", "GA", "HI", "IA", "ID", "IL", "IN", _"KS", "KY", "LA", "MA", "MD", "ME", "MI", "MN", _"MO", "MS", "MT", "NC", "ND", "NE", "NH", "NJ", _"NM", "NV", "NY", "OH", "OK", "OR", "PA", "RI", _"SC", "SD", "TN", "TX", "UT", "VA", "VT", "WA", _

"WI", "WV", "WY")'center the form:CenterForm Me' Connect to the Property database:Set mobjADOConn = New ADODB.ConnectionmobjADOConn.ConnectionString = "DSN=PropDB;Uid=admin;Pwd=;"mobjADOConn.Open

Call GetPropertyData

SetFormState False


36/59

Exit Sub


Code the GetMinimumAsking Sub, which uses the InputBox function to prompt to the user to enter the minimum asking

price of the properties they want to work with. The resulting value is then stored in the form-level

variable mdblMinAsking.

'-----------------------------------------------------------------------------

Private Sub GetMinimumAsking()'----------------------------------------------------------------------------- Dim strInputBoxPrompt As StringDim strAsking As String

strInputBoxPrompt = "Enter the minimum asking price (for example, 200000) " _ & "for the properties that you want to work with this session." _& vbNewLine _& "To work with ALL properties, leave the default of zero."strAsking = InputBox(strInputBoxPrompt, "Minimum Asking Price", "0")If strAsking = "" Then' user clicked Cancel button on the input box, so end the appEndEnd IfmdblMinAsking = Val(strAsking)

End Sub


37/59

Code the GetPropertyData Sub, which builds the SQL to get the property records meeting the minimum asking price

condition. The Recordset object is then instantiated, its CursorLocation property is set, and its Open method is invoked to

execute the SQL and return the resultset. This is done in a loop in case the resultset does not return any records due to

the fact no records in the table met the asking price condition. In that situation, the user is given the opportunity to specify

a different asking price value. Following this, the programmer-defined Sub PopulateFormFields is called (which displays

the fields from the current record in their corresponding textboxes on the form).

'----------------------------------------------------------------------------- Private Sub GetPropertyData()'-----------------------------------------------------------------------------

On Error GoTo LocalErrorDim blnGotData As BooleanblnGotData = FalseDo'select or reload the data to be displayed:mstrSQL = "select propno" _& " , empno" _& " , address" _& " , city" _& " , state" _& " , zip" _& " , beds" _& " , baths" _& " , asking" _& " from property" _& " where asking >= " & mdblMinAsking _& " order by propno"Set mobjADORst = New ADODB.RecordsetmobjADORst.CursorLocation = adUseClientmobjADORst.Open mstrSQL, mobjADOConn, adOpenDynamic, adLockOptimistic, adCmdText


38/59

If mobjADORst.EOF ThenIf MsgBox("There are no properties with an asking price >= " _& Format$(mdblMinAsking, "Currency") _& ". Do you want to try again with a different value?", _vbYesNo + vbQuestion, _"Asking Price") _= vbYes ThenGetMinimumAskingElseEndEnd IfElseblnGotData = TrueEnd IfLoop Until blnGotData'load data into the text boxesCall PopulateFormFieldsExit SubLocalError:MsgBox Err.Number & " - " & Err.Description

End Sub

Code the PopulateFormFields Sub, which assigns the fields from the current record to their corresponding textboxes on

the form. Note that the gblnPopulating Boolean variable is set to True prior to the assignments and set to False after the

assignments. This value is used to control whether or not certain code executes in the event procedures for some of these

textboxes. The Sub SetRecNum is then called.

'----------------------------------------------------------------------------- Private Sub PopulateFormFields()


39/59

'----------------------------------------------------------------------------- On Error GoTo LocalError

gblnPopulating = True

'copy the data from the resultset to the text boxes:txtPropNo.Text = mobjADORst.Fields("propno")txtEmpNo.Text = mobjADORst.Fields("empno")txtAddress.Text = mobjADORst.Fields("address")txtCity.Text = mobjADORst.Fields("city")txtState.Text = mobjADORst.Fields("state")txtZip.Text = mobjADORst.Fields("zip")txtBeds.Text = mobjADORst.Fields("beds")txtBaths.Text = mobjADORst.Fields("baths")txtAsking.Text = Format$(mobjADORst.Fields("asking"), "Currency")gblnPopulating = False

Call SetRecNumExit SubLocalError:MsgBox Err.Number & " - " & Err.Description

End Sub

Code the SetRecNum Sub. This sub is identical to the one used in Sample Application 2. It displays the number of the

current record at the bottom of the screen using theAbsolutePosition and RowCount properties of the Recordset object.

'----------------------------------------------------------------------------- Private Sub SetRecNum()


40/59

'----------------------------------------------------------------------------- StatusBar1.SimpleText = "row " & mobjADORst.AbsolutePosition _& " of " & mobjADORst.RecordCountEnd Sub

Code the SetFormState Sub, which takes in a Boolean argument used to set the Enabled property of the controls on the

form. Based on whether the value True or False is passed to this sub, this sub ensures that the textboxes are enabled for

adds and updates and disabled for browsing; it also ensures that the various command buttons are enabled or disabled at

the appropriate time. This Sub also sets the form-level Boolean variable mblnUpdatePending.

'----------------------------------------------------------------------------- Private Sub SetFormState(pblnEnabled As Boolean)'-----------------------------------------------------------------------------

txtPropNo.Enabled = pblnEnabledtxtEmpNo.Enabled = pblnEnabledtxtAddress.Enabled = pblnEnabledtxtCity.Enabled = pblnEnabledtxtState.Enabled = pblnEnabledtxtZip.Enabled = pblnEnabledtxtBeds.Enabled = pblnEnabledtxtBaths.Enabled = pblnEnabledtxtAsking.Enabled = pblnEnabled

cmdSave.Enabled = pblnEnabledcmdCancel.Enabled = pblnEnabledcmdAdd.Enabled = Not pblnEnabledcmdUpdate.Enabled = Not pblnEnabledcmdDelete.Enabled = Not pblnEnabledcmdExit.Enabled = Not pblnEnabled


41/59

cmdMoveFirst.Enabled = Not pblnEnabledcmdMoveNext.Enabled = Not pblnEnabledcmdMovePrevious.Enabled = Not pblnEnabledcmdMoveLast.Enabled = Not pblnEnabledmblnUpdatePending = pblnEnabled

End Sub

Code the Form_Unload event. In it, the form-level Boolean variable mblnUpdatePending is tested to see if (well, an

update is pending i.e., whether an add or update is in progress). If the user is in the middle of an add or update and then

clicks the "X" button on the upper-right corner of the form, they will receive the message that they must save or cancel

prior to exiting the application, and the form will NOT be unloaded (because we are assigning a non-zero value to the

Cancel argument in that situation). Provided that an add or update is not in progress, we set the database objects toNothing and the Unload will complete.

'----------------------------------------------------------------------------- Private Sub Form_Unload(Cancel As Integer)'-----------------------------------------------------------------------------

If mblnUpdatePending ThenMsgBox "You must save or cancel the current operation prior to exiting.", _vbExclamation, _"Exit"Cancel = 1ElseSet mobjADORst = NothingSet mobjADOConn = NothingEnd IfEnd Sub

Code the events for the various Textboxes as shown below. The code in these events ensure the following:


42/59

For all, highlight the text in the textbox when it receives focus. For all but the last textbox, if the maximum number of characters typed into the textbox is reached, auto-tab to the

next textbox. Only numeric digits should be entered into the property number, employee number, zip codes, and beds textboxes. Only numeric digits and optionally one decimal point should be entered into the baths and asking textboxes. Force uppercase on the state textbox. When the asking textbox receives focus, the value in there should be unformatted. When the asking textbox loses

focus, its value should be formatted as currency.

'----------------------------------------------------------------------------- ' Textbox events'-----------------------------------------------------------------------------

' property #Private Sub txtPropNo_GotFocus()SelectTextBoxText txtPropNoEnd SubPrivate Sub txtPropNo_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)End SubPrivate Sub txtPropNo_Change()TabToNextTextBox txtPropNo, txtEmpNoEnd Sub

' emp #Private Sub txtEmpNo_GotFocus()SelectTextBoxText txtEmpNoEnd SubPrivate Sub txtEmpNo_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)


43/59

End SubPrivate Sub txtEmpNo_Change()TabToNextTextBox txtEmpNo, txtAddressEnd Sub

' addressPrivate Sub txtAddress_GotFocus()SelectTextBoxText txtAddressEnd SubPrivate Sub txtAddress_Change()TabToNextTextBox txtAddress, txtCityEnd Sub

' cityPrivate Sub txtCity_GotFocus()SelectTextBoxText txtCityEnd SubPrivate Sub txtCity_Change()TabToNextTextBox txtCity, txtStateEnd Sub

' statePrivate Sub txtState_GotFocus()SelectTextBoxText txtStateEnd SubPrivate Sub txtState_KeyPress(KeyAscii As Integer)KeyAscii = ConvertUpper(KeyAscii)End SubPrivate Sub txtState_Change()


44/59

TabToNextTextBox txtState, txtZipEnd Sub

' zipPrivate Sub txtZip_GotFocus()SelectTextBoxText txtZipEnd SubPrivate Sub txtZip_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)End SubPrivate Sub txtZip_Change()TabToNextTextBox txtZip, txtBedsEnd Sub

' bedsPrivate Sub txtBeds_GotFocus()SelectTextBoxText txtBedsEnd SubPrivate Sub txtBeds_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS)End SubPrivate Sub txtBeds_Change()TabToNextTextBox txtBeds, txtBathsEnd Sub

' bathsPrivate Sub txtBaths_GotFocus()SelectTextBoxText txtBathsEnd Sub


45/59

Private Sub txtBaths_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS & ".")' if text already has a decimal point, do not allow another ...If Chr$(KeyAscii) = "." And InStr(txtBaths.Text, ".") > 0 ThenKeyAscii = 0End IfEnd SubPrivate Sub txtBaths_Change()TabToNextTextBox txtBaths, txtAskingEnd Sub

' asking pricePrivate Sub txtAsking_GotFocus()txtAsking.Text = UnFormatNumber(txtAsking.Text)SelectTextBoxText txtAskingEnd SubPrivate Sub txtAsking_KeyPress(KeyAscii As Integer)KeyAscii = ValidKey(KeyAscii, gstrNUMERIC_DIGITS & ".")' if text already has a decimal point, do not allow another ...If Chr$(KeyAscii) = "." And InStr(txtAsking.Text, ".") > 0 ThenKeyAscii = 0End IfEnd SubPrivate Sub txtAsking_LostFocus()txtAsking.Text = Format$(txtAsking.Text, "Currency")End Sub

Code the events for the navigation buttons as shown below, using the resultset "Move" methods to move to the first, last,

next, or previous record, respectively.


46/59

'----------------------------------------------------------------------------- Private Sub cmdMoveFirst_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError

mobjADORst.MoveFirstCall PopulateFormFieldsExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMoveLast_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError

mobjADORst.MoveLastCall PopulateFormFieldsExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMoveNext_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError


47/59

mobjADORst.MoveNextIf mobjADORst.EOF ThenBeepmobjADORst.MoveLastEnd IfCall PopulateFormFieldsExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

'----------------------------------------------------------------------------- Private Sub cmdMovePrevious_Click()'----------------------------------------------------------------------------- On Error GoTo LocalError

mobjADORst.MovePreviousIf mobjADORst.BOF ThenBeepmobjADORst.MoveFirstEnd IfCall PopulateFormFieldsExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

Code the Click event for the cmdAdd button. In it, the textboxes are cleared, the SetFormState sub is called (passing it

a parameter of True, which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons),


48/59

set the form-level variable mstrUpdateType to "A" (indicating that an add is pending) and sets focus to the Property

Number field.'----------------------------------------------------------------------------- Private Sub cmdAdd_Click()'-----------------------------------------------------------------------------

On Error GoTo LocalError

'clear all the text boxes:txtPropNo.Text = ""txtEmpNo.Text = ""txtAddress.Text = ""txtCity.Text = ""txtState.Text = ""txtZip.Text = ""txtBeds.Text = ""txtBaths.Text = ""txtAsking.Text = ""SetFormState TruemstrUpdateType = "A"txtPropNo.SetFocusExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

Code the Click event for the cmdUpdate button. In it, the SetFormState sub is called (passing it a parameter of True,

which will enable the textboxes and the Save and Cancel buttons and disable all the other buttons), set the form-level

variable mstrUpdateType to "U" (indicating that an update is pending), disables the Property Number field (because it is

the primary key and should not be changed) and sets focus to the Employee Number field.


49/59

'----------------------------------------------------------------------------- Private Sub cmdUpdate_Click()'-----------------------------------------------------------------------------


SetFormState TruemstrUpdateType = "U"' being that propno is the primary key, it should not be updatabletxtPropNo.Enabled = FalsetxtEmpNo.SetFocusExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionEnd Sub

Code the Click event for the cmdSave button. The user would click this button after they have completed entries for an

add or update. This sub first invokes theValidateAllFields function, which returns a Boolean indicating whether or not all

entries passed their edit checks. If not, we exit the sub and the record is not saved; the user remains in "update pending"

mode and has the opportunity to correct the entries. Provided that validation is successful, the sub proceeds.

The mstrUpdateType variable is checked to see whether we are dealing with an add or an update.

If we are dealing with an add, we invoke the AddNew method of the Recordset object. The AddNew method prepares a

new row you can edit and subsequently add to the Recordset object using the Update method. After you modify the new

row, you must use the Update method to save the changes and add the row to the result set. No changes are made tothe database until you use the Update method. (The Update method is invoked after the content of the textboxes has

been assigned to the database f ields.)If we are dealing with an update, we can just start modifying the fields (provided an appropriate cursor type has been

selected) unlike DAO and RDO, ADO does not use anEdit method. Changes made to the current rows columns are

copied to the copy buffer. After you make the desired changes to the row, use the Update method to save your changes

or the CancelUpdate method to discard them. (If you move on to another record without invoking Update, your changes

will be lost.)


50/59

The content of the textboxes is assigned to the database fields, then the Update method is invoked. The Update method

saves the contents of the copy buffer row to a specified updatable Recordset object and discards the copy buffer.

The mstrUpdateType variable is checked once again, and if we are dealing with an add, there is some extra work to do.

Although the new record has been added, the original resultset still does not contain the new record.

The Requery method must be invoked, which updates the data in a Recordset object by re-executing the query on which

the object is based. The Find method is then used to position to the new record. The ADO Find method has the following

syntax:RecordsetObject.Find Criteria, SkipRows, SearchDirection, StartThe Criteriaargument is a String value that specifies the column name, comparison operator, and value to use in the

search. Only a single-column name may be specified incriteria; multi-column searches are not supported. The

comparison operator may be ">" (greater than), "=" (greater than or equal), " #7/22/97#").

These values can contain hours, minutes, and seconds to indicate time stamps, but should not contain

milliseconds or errors will occur. If the comparison operator is "like", the string value may contain an asterisk (*) to

find one or more occurrences of any character or substring. For example, "state like 'M*'"

matchesMaine and Massachusetts. You can also use leading and trailing asterisks to find a substring contained

within the values. For example, "state like '*as*'" matchesAlaska, Arkansas, and Massachusetts. Asterisks can be

used only at the end of a criteria string, or together at both the beginning and end of a criteria string, as shown

above. You cannot use the asterisk as a leading wildcard ('*str'), or embedded wildcard ('s*r'). This will cause an

error.

SkipRowsis an optional Long value, whose default is zero, that specifies the row offset from the current row (or bookmark

row specified by the Startargument, if present) to begin the search. By default, the search will start on the current

row.

SearchDirectionis an optional value that determines in which direction the search is performed. The value is specified by

the constants adSearchForward (the default) oradSearchBackward, which equate to values of 1 or -1,

respectively.

Startis an optional Variant bookmark that functions as the starting position for the search.

Note: Unlike DAO, ADO does not have a "NoMatch" property. If the ADO Find method is unsuccessful, the record pointer

is positioned at the end of the Recordset.

The SetRecNum sub is then be called to display the status bar information about the new record. The SetFormState sub

is then called with a parameter of False, which causes the textboxes and the Save and Cancel buttons to be disabled and

all other buttons to be enabled.

Note that in the statement that assigns the contents of the txtAsking textbox to the asking field of the table,

our UnFormatNumber function is used to strip off the non-numeric characters. This is because we are using a display

format that includes a dollar sign and commas on the txtAsking control, and an error would occur if we attempted to

assign this directly to the asking field, which is defined as numeric.


51/59

'----------------------------------------------------------------------------- Private Sub cmdSave_Click()'-----------------------------------------------------------------------------


If Not ValidateAllFields Then Exit Sub

If mstrUpdateType = "A" ThenmobjADORst.AddNewElse' We can just update the fields. No explicit Edit method' is available or needed.End If'save the data to the database:mobjADORst.Fields("propno") = txtPropNo.TextmobjADORst.Fields("empno") = txtEmpNo.TextmobjADORst.Fields("address") = txtAddress.TextmobjADORst.Fields("city") = txtCity.TextmobjADORst.Fields("state") = txtState.TextmobjADORst.Fields("zip") = txtZip.TextmobjADORst.Fields("beds") = txtBeds.TextmobjADORst.Fields("baths") = txtBaths.TextmobjADORst.Fields("asking") = UnFormatNumber(txtAsking.Text)mobjADORst.Update

If mstrUpdateType = "A" Then'after the new record is added, the db must be re-queried'so that the resultset contains the new record:


52/59

mobjADORst.Requery' reposition to the record just addedmobjADORst.Find "propno = " & txtPropNo.Text'display status info about the new recordSetRecNumEnd IfReset:SetFormState FalseExit SubLocalError:MsgBox Err.Number & " - " & Err.DescriptionResume ResetEnd Sub

Code the Click event for the cmdDelete button. The user is first asked to confirm that they want to delete the record, and

if so, the Delete method of the resultset object is invoked, which deletes the current row in an updatable resultset object.

The Requery method is then invoked so that the record is removed from the resultset that the user is working with. The

Find method is then invoked to position the next record after the deleted one. If it was the last record that was deleted,

then we position to the "new" last record using the MoveLast property. PopulateFormFields must then be called todisplay the contents of the new current record.

'----------------------------------------------------------------------------- Private Sub cmdDelete_Click()'-----------------------------------------------------------------------------

On Error GoTo LocalError'when the current record is deleted, the current location in the recordset'is invalid. use the Requery method to re-execute the query and update'the data.If MsgBox("Are you sure you want to delete this record?", _vbYesNo + vbQuestion, _


53/59

"Delete") = vbNo ThenExit SubEnd IfmobjADORst.DeletemobjADORst.Requery' reposition to one past the record just deletedmobjADORst.Find "propno > " & txtPropNo.Text' If it was the last record that was deleted, the Find method will' come back with EOF, in which case we should MoveLast to position' us to the "new" last record ...If mobjADORst.EOF Then mobjADORst.MoveLast'load data into the text boxes:Call PopulateFormFields

Exit Sub


The ValidateAllFields function, which returns a Boolean value indicating whether or not all fields have passed validation

checks. This function calls upon two "helper" functions: PropertyExists and ValidState. When the user is doing an add,

the PropertyExist function is called to see whether or not the proposed Property Number is already being used in the

Property table. If so, the user is informed that they can't use that number (because it is the primary key and must be

unique) and so they must use a different number. The ValidState routine is called to ensure that the user has entered a

valid US state. The code for all three functions is shown below.

'----------------------------------------------------------------------------- Private Function ValidateAllFields() As Boolean'-----------------------------------------------------------------------------


54/59

ValidateAllFields = False 'guilty until proven innocent If mstrUpdateType = "A" ThenIf txtPropNo.Text = "" ThenMsgBox "Property # must not be blank.", vbExclamation, "Property #" txtPropNo.SetFocusExit FunctionElseIf PropertyExists ThenMsgBox "Property # already exists. Please use a different #.", _vbExclamation, _"Property #"txtPropNo.SetFocusExit FunctionEnd IfEnd IfIf txtEmpNo.Text = "" ThenMsgBox "Emp # must not be blank.", vbExclamation, "Emp #"txtEmpNo.SetFocusExit FunctionEnd IfIf txtAddress.Text = "" ThenMsgBox "Address must not be blank.", vbExclamation, "Address" txtAddress.SetFocusExit FunctionEnd IfIf txtCity.Text = "" ThenMsgBox "City must not be blank.", vbExclamation, "City"txtCity.SetFocusExit FunctionEnd If


55/59

If Not ValidState ThenMsgBox "Missing or invalid state.", vbExclamation, "State"txtState.SetFocusExit FunctionEnd IfIf txtZip.Text = "" Or Len(txtZip.Text) = 5 Then' it's OKElseMsgBox "Zip code must either be blank or exactly 5 digits.", _vbExclamation, _"Zip Code"txtZip.SetFocusExit FunctionEnd IfIf Val(txtBeds.Text) = 0 ThenMsgBox "Beds must not be zero.", vbExclamation, "Beds"txtBeds.SetFocusExit FunctionEnd IfIf Val(txtBaths.Text) = 0 ThenMsgBox "Baths must not be zero.", vbExclamation, "Baths"txtBaths.SetFocusExit FunctionEnd IfIf Val(UnFormatNumber(txtAsking.Text)) = 0 ThenMsgBox "Asking must not be zero.", vbExclamation, "Asking" txtAsking.SetFocusExit FunctionEnd If


56/59

' if we make it here, all fields have passed editValidateAllFields = TrueEnd Function

'-------------------------------------------------------------------------------- Private Function ValidState() As Boolean'--------------------------------------------------------------------------------

Dim lngX As LongDim blnStateFound As BooleanblnStateFound = FalseFor lngX = 0 To UBound(mavntUSStates)If txtState.Text = mavntUSStates(lngX) ThenblnStateFound = TrueExit ForEnd IfNextValidState = blnStateFoundEnd Function

'----------------------------------------------------------------------------- Private Function PropertyExists() As Boolean'-----------------------------------------------------------------------------

Dim objTempRst As New ADODB.RecordsetDim strSQL As String

strSQL = "select count(*) as the_count from property where propno = " & txtPropNo.Text objTempRst.Open strSQL, mobjADOConn, adOpenForwardOnly, , adCmdText


57/59

If objTempRst("the_count") > 0 ThenPropertyExists = TrueElsePropertyExists = FalseEnd If

End FunctionCode the Click event for the cmdCancel button. The user would click this button if, during an add or update, they decide

to abandon the operation. Here,PopulateFormFields is called to reset the textboxes to their content prior to the user

clicking the Add or Update button, and SetFormState is called with a parameter of False, which causes the textboxes and

the Save and Cancel buttons to be disabled and all other buttons to be enabled.

'----------------------------------------------------------------------------- Private Sub cmdCancel_Click()'-----------------------------------------------------------------------------

PopulateFormFieldsSetFormState FalseEnd SubCode the Click event for the cmdExit button, which issues the Unload Me statement to fire the Form_Unload event,

which will unload the form and end the application.

'----------------------------------------------------------------------------- Private Sub cmdExit_Click()'-----------------------------------------------------------------------------

Unload MeEnd Sub



58/59

Similar links

Naming Database Objects

Access SQL

VB6 With Access

Oracle and Visual Basic using ADO

Database Access with RDO (Remote Data Objects)

Using Crystal ReportsDatabase Access with the Data Control

Using ADO and the ListView control

Creating PDF files in Visual Basic

VB6 Animated Charts (With FusionCharts)

428076 reads

Mon, 08/15/2011 - 00:33 Anonymous (not verified)

hehe

thank you so much .. it is so helping ty

reply

Sun, 06/26/2011 - 21:47 krunalmsheth

Data not save in data base

I have complete my all procedure to add, del., save, move but when i will enter data in form, & i use save command

then no error found but data not save in my backend file. i will use array for textbox & data connect through

adodb.recordset function in module. I will give right table no in coding for the same form.

reply

Wed, 06/22/2011 - 02:49

NKaze7 (not verified)Thanks for the Tutorial!

Thank you for providing this tutorial!! It helped me out in my studies.

reply

Fri, 06/03/2011 - 05:40 Anisul (not verified)

Help With SQL server 2000 and Data source ODBC

Hello Every1,

I m a student of a private University in Bangladesh.

I m using windows xp. i installed the SQL server 2000. But i can't get connected with Data source ODBC...

Can anybody help me or refer me to know Sql Server 2000 installation process and how to connect Data source

ODBC.

PLease help..

reply

Fri, 05/20/2011 - 07:24 march11 (not verified)

Setting ADOSB character set
http://www.vb6.us/tutorials/naming-database-objectshttp://www.vb6.us/tutorials/access-sqlhttp://www.vb6.us/tutorials/vb6-accesshttp://www.vb6.us/tutorials/oracle-and-visual-basic-using-adohttp://www.vb6.us/tutorials/database-access-rdo-remote-data-objects-vb6http://www.vb6.us/tutorials/using-crystal-reports-vb6-tutorialhttp://www.vb6.us/tutorials/database-access-vb6-data-control-tutorialhttp://www.vb6.us/tutorials/using-ado-and-listview-control-vb6http://www.vb6.us/tutorials/visual-basic-tutorial-pdfhttp://www.vb6.us/tutorials/vb6-chartshttp://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6811http://www.vb6.us/comment/reply/82/6811http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6507http://www.vb6.us/comment/reply/82/6507http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6494http://www.vb6.us/comment/reply/82/6494http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6403http://www.vb6.us/comment/reply/82/6403http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/comment/reply/82/6403http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6403http://www.vb6.us/comment/reply/82/6494http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6494http://www.vb6.us/comment/reply/82/6507http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6507http://www.vb6.us/comment/reply/82/6811http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6811http://www.vb6.us/tutorials/vb6-chartshttp://www.vb6.us/tutorials/visual-basic-tutorial-pdfhttp://www.vb6.us/tutorials/using-ado-and-listview-control-vb6http://www.vb6.us/tutorials/database-access-vb6-data-control-tutorialhttp://www.vb6.us/tutorials/using-crystal-reports-vb6-tutorialhttp://www.vb6.us/tutorials/database-access-rdo-remote-data-objects-vb6http://www.vb6.us/tutorials/oracle-and-visual-basic-using-adohttp://www.vb6.us/tutorials/vb6-accesshttp://www.vb6.us/tutorials/access-sqlhttp://www.vb6.us/tutorials/naming-database-objects


59/59

I am reading a few cells from Excell into Access VBA using ADODB. One of the columns in the spreadsheet contain

Japanese characters.

How can I set the character set to receive these into the VBA variable, now they come in as "??????" ?

Thanks

replyFri, 04/29/2011 - 03:36 Anonymous (not verified)

thanhk so much!!!!! :)

thanhk so much!!!!! :)

reply

Thu, 04/21/2011 - 12:02 MAHI (not verified)

Very helpful tutorial..... it

Very helpful tutorial..... it helps me a lot....

reply

Tue, 04/12/2011 - 00:02 Anonymous (not verified)

thank u guys!

thank u guys!
http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6315http://www.vb6.us/comment/reply/82/6315http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6214http://www.vb6.us/comment/reply/82/6214http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6170http://www.vb6.us/comment/reply/82/6170http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6101http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6101http://www.vb6.us/comment/reply/82/6170http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6170http://www.vb6.us/comment/reply/82/6214http://www.vb6.us/tutorials/database-access-ado-vb6-tutorial#comment-6214http://www.vb6.us/comment/reply/82/6315

access db help

Documents