Dale's WorkBench

Click here to advertise your product to over 25,000 VB programmers

June 1997

Return to Article Index
Dale's WorkBench
by Dale Shaw

Over the last few months we have been discussing the creation and use of VB Classes, creating your own object types and some uses. My aim this month was to produce some more examples of the use of object classes but ….. something got in the road: databases.

I have been creating database applications for years and years (starting with dBase II on an Apple II) and have spent many a year on FoxPro. I've cut a few VB database apps over the years but recently some large projects have caused me to do a bit of a re-think on some of the things I was doing and so here I present a few of my tips and some (hopefully) useful code.

While I have your attention: For those who liked the SQLBuilder code presented last month there's MORE! The code presented was a cut down version of my full code that does INSERT, UPDATE and DELETE statements as well as SELECT. The latest version also generates Transact-SQL (for MS SQL Server) for use in dbSQLPassthrough statements. Keep an eye on my goodies page for an update.

Well, back to the matter at hand….

Problem #1: Date Formats ….

Life is simple if you develop for the US market but out here in the rest of the world ODBC & JET cause a bit of grief when it comes to storing and retrieving data based on SQL strings. When using 'British' date formats (dd/mm/yy) things can sometimes go a bit astray. For example:
SELECT * FROM ORDERS WHERE 
ORDERDATE#5/1/97#
In a U.S. date system, ODBC would retrieve everything for the first of May. No problem. However, run it on our PCs (dd/mm/yy) and you would expect the result set to be for the 5^th of January. Good theory, doesn't quite happen that way. JET takes the above string to be a US date and returns May 1's data.

OK, I could live with that. Let's try this one:
SELECT * FROM ORDERS WHERE 
ORDERDATE#15/1/97#
Obviously, JET looks at this and says…. January 15^th…. (dd/mm/yy)!!! So, we could safely assume that you should always use US date formats in SQL strings. I would but I don't trust this to always work! I prefer:
SELECT * FROM ORDERS WHERE  
ORDERDATE#15-JAN-1997# 
which avoids any confusion (and aids Year 2000 stuff!). I use this function to do the formatting:
Public Function  
GetDate(WhatDate As Date, Optional InclTime As Variant) As String  
	 
GetDateFormat(WhatDate,"dd-mmm-yyyy")
	If IsMissing(InclTime) Then 
		InclTime  False 
	EndIf
	if InclTime then
		GetDateGetDate & " " & 
Format(WhatDate,"Short Time")
	End If 
End 
Function
Problem #2: Field Widths….

There is nothing worse. You draw a field on screen that looks the right size but the user can type as much data as they like. They hit your SAVE button and WHAMMO:
Data too large for 
field.
What to do? VB text boxes have a Maxwidth property that lets us control the amount of data a user can type into a field and this is just perfect for avoiding errors. The problem is setting the Maxwidth property for every field, on every form… and then maintaining it should you (or worse, someone else) change the field size of the underlying data. The code presented below solved this and a few other problems.

SetWidths is called both before AND after the user enters data onto your form. For example:
Private Sub  
FORM_ACTIVATE() 
	SetWidths Me, datCustomer.Recordset
End Sub
and…
Private Sub  
cmdOK() 
	SetWidths Me, datCustomer.Recordset 
	datCustomer.Update 
End Sub 
The reason for calling it twice is to solve two problems. The first time we call SetWidths it sets the MaxWidth property to prevent the user typing in too much data. Calling it a second time is my safety net. It chops the data if required (which, of course, it shouldn't be) and the AllowZeroLength property of the table is checked to ensure SOMETHING goes into the database in case someone has forced the requirement on use that the field may not be NULL.

Of course, some of you may find the second requirement here odd. If the database designer has deemed that we must enter data then why bypass it. I designed this as a safety net for me in one project where I didn't have control of the underlying database and the 'required' data kept changing!

If you want to ensure users fill in all relavant fields then you could use code similar to this check that they have entered data in all required fields and then prompt for missing data (maybe change the field backcolor and place a message on status bar. You could then call this from a timer or the LostFocus on each data entry field (or, at a push, the OK/Save button).
Public Sub 
SetWidths(DataForm As Form, DataControl As Recordset) 
	Dim Ctl As Control 
	Dim FldType As Integer 
	Dim FldWidth As Long 
	Const DummyChar As String  3D  " " 
	' Set MaxWidth property for each 
	' databound text / memo 
	' textbox. 
	On Error Resume Next 
	For Each Ctl In DataForm.Controls 
		If TypeOf Ctl Is TextBox Then 
			If Ctl.DataField <> "" Then 
				Err.Clear 
				FldType  DataControl.Fields(Ctl.DataField).Type 
				If Err.Number  0 Then 
					If FldType  dbMemo Or FldType  dbText Then 
						If FldType  dbMemo Then 
							FldWidth  65535 
						Else 
							FldWidth  DataControl.Fields(Ctl.DataField).Size=20
						End If 
						Ctl.MaxLength  FldWidth 
						If Len(Ctl.Text) > FldWidth Then 
							Ctl.Text  Left(Ctl.Text, FldWidth) 
						End If=20
						If Len(Trim(Ctl.Text))  0 And (Not 
DataControl.Fields(Ctl.DataField).AllowZeroLength) Then 
							Ctl.Text  DummyChar 
							' Or put up an error if I was told to verify! 
						End If 
					End If 
				End If 
			End If 
		End If 
	Next 
End Sub
Problem #3: Security…

Access / JET provides some nice security features but….

I was working on a project where security was important and data stored in the MDB should not be accessible to anyone other than the 'owner'. Unfortunately, JET is not structured to prevent access on a row-by-row basis. I also was a bit stuck because I did not control the network or related security.

So… Encryption seemed the logical approach. To keep things flexible I used the ValidationText property of each database field to reflect whether I should encrypt a field or not. I then threw together some rudimentary scrambling routines to do the hard work. (These routines are NOT presented here for security reasons! There are several OCXs available as well as ample encryption source code around).

The result:
Public Sub EncrFields(RS As  
Recordset) 
	Dim ProcName As String 
	ProcName  "EncrFields" 
	On Error GoTo EncrFields_err
	Dim Fld As Field 
	For Each Fld In RS.Fields 
		If Fld.Type  dbMemo Or Fld.Type  dbText Then 
			If Not IsNull(Fld.value) Then 
				If Fld.ValidationText  "*" Then 
					Fld.value  Encr(Fld.value) 
				End If 
			End If 
		End If 
	Next 
Exit Sub

EncrFields_err: 
	If ErrorTrap(ProcName)  vbRetry Then Resume: Else Resume Next 
End Sub
Good luck with the code and don't let the database blues get you...

Dale Shaw

About the Author

Dale is a Microsoft Certified Solution Developer, Systems Engineer and Trainer based in Auckland, New Zealand and is the principal of LAN Technologies.

.