Working with Databases
Currently this field of database technology is being reshaped almost daily, as new technologies, or new versions of older technologies, emerge. Most of your database work will be done through a proprietary API, DAO, RDO, ADO, or ODBC. The latter three all depend to some extent on an additional driver layer that might be sourced from a third party. As with controls, you must perform thorough acceptance testing on any middleware that you use.
SQL issues
A major issue when working with SQL, especially if you work outside the United States, is that of date formats in your statements. The SQL standard is to use a U.S. format MM/dd/yyyy date. Certainly in other countries, such as the U.K., this format can lead to some confusion if you forget to modify the date to provide it in the U.K. format of dd/MM/yyyy. The following code shows a function for formatting any date in a SQL statement.Public Function sFormatDateForSQL(ByVal idtDate As Date) As String
' Convert the passed Date to a string in the
' US format, suitable for using in a SQL Statement.
sFormatDateForSQL = Format$(idtDate, "MM/dd/yyyy")
End Function
Storage patterns for legacy platforms
Wherever possible use the native date format provided by the database product. Most of the latest versions of the most popular products support dates well into the next millennium and beyond. Where a date field is not available, or is not capable of Year 2000 compliance, we will need a little cunning. Here's a look at a couple of storage patterns we can use.Double-precision numbers By storing your dates as double-precision numbers, you render them immediately compatible with Visual Basic's Date data type. Double-precision numbers can store time as well as date information.
TimeSince methods An alternative to the above idea is to store your dates as a Long integer containing the number of seconds, minutes, or days since a defined base date. This base will be a date such as midnight, January 1, 1980, or midnight, January 1, 2000. Conversions to and from this format can be performed using the DateDiff and DateAdd functions discussed earlier in this chapter. The following code shows an implementation of TimeSince. This implementation provides functions to convert a Date to and from a Long, using a base date of January 1, 2000.
Const BASE_DATE As Date = #1/1/2000# ' Base date is 2000-01-01.
Const INTERVAL As String = "n" ' Interval is minutes.
Public Function lDateToTimeSince(ByVal idteDate As Date) As Long
' Convert the passed date and time to a Long integer
' containing the minutes elapsed since the base date.
LDateToTimeSince = DateDiff(INTERVAL, BASE_DATE, idteDate)
End Function
Public Function dtDateFromTimeSince(ByVal ilMinutes As Long) As Date
' Convert the passed Long integer to a Date as
' the number of minutes since the base date.
DtDateFromTimeSince = DateAdd(INTERVAL, ilMinutes, BASE_DATE)
End Function
Obviously, the choice of time interval dictates the date range available, but even if we use seconds we have a range of approximately 135 years (slightly less than 68 years before and after the base date). If storing time is not important or space is at a premium, we can use days as a time interval and store our date as a short Integer.
NOTE
This technique is actually mimicking the storage format employed by Visual Basic itself, but with a lower storage overhead. Visual Basic uses an 8-byte double-precision number to store the number of seconds before or since midnight December 30 1899. We save space by choosing a more recent base date and a more coarse time measurement.
Both the double-precision and TimeSince methods have the added bonus of being numeric data, which is considerably faster for a database to sort, search, and index than alphanumeric data.