Databases - Referencing Tables

How to reference tables and the fields they contain.

Paradox | MS Access 97 | MS Access XP | Delphi | ASP

Paradox

The following code steps through all the records in a table and then calls a context sensitive routine (subject.Determine_Age) that computes computes a persons age based on the specified dates.

method Check_Data()
var 
  testTableTC                TCursor
  DateOfBirth, AgeOnThisDate Date
  CalculatedAge              SmallInt
  Error_Count                SmallInt
endvar

testTableTC.open("testdate")
testTableTC.edit()

Error_Count = 0

scan testTableTC :    ; The colon is required!

  DateOfBirth   = testTableTC."Date of Birth"
  AgeOnThisDate = testTableTC."Age on This Date"

    ; Check for a valid comparison
  if DateOfBirth > AgeOnThisDate then 
    testTableTC."Computed Age" = blank()
    loop ; Get next record from table
  endif

  CalculatedAge = subject.Determine_Age(DateOfBirth, AgeOnThisDate)
  testTableTC."Computed Age" = CalculatedAge

  if testTableTC."Correct Age" <> CalculatedAge  then
    Error_Count = Error_Count + 1
  endif

endscan

  testTableTC.close()
  TESTDATE_UI_TableFrame.forceRefresh()

  Number_of_Errors_UI_Field = Error_Count

  if  Number_of_Errors_UI_Field = 0
    then  Number_of_Errors_UI_Field.Font.Color = Green
    else  Number_of_Errors_UI_Field.Font.Color = Red
  endif

endmethod Check_Data

MS Access 97

MS Access uses a Recordset to access data in a table. Methods are provided for First, Last, Next, Previous, and the like.

Individual fields are referenced via

Recordset_X!FieldName
Recordset_x![FieldName]

Use CurrentDB to refer to the current database. In the following, the word Set is required. It causes a variable to point to (reference) the object.

Dim dbsA As Database
Set dbsA = CurrentDB

Dim dbs As Database
Dim Some_Recordset As Recordset
Dim SQL_Str As String

	' Return reference to current database.
Set dbs = CurrentDb
SQL_Str = "SELECT * FROM Orders WHERE [StateField] = 'VA'"
Set Some_Recordset = dbs.OpenRecordset(SQL_Str)

With Some_Recordset
  Do While Not .EOF
    TempStr = !Field1 & "some text" & (10 * !Field2) & vbCr
    Debug.Print TempStr
    .MoveNext
  Loop
End With

Some_Recordset.Close
Set dbs = Nothing

More Details

Example

The following code correctly computes a persons age based on the specified dates.

Private Sub Button20_Click()
Dim MyDB As Database, MyTable As Recordset
Dim iBDOY As Integer, iDOY As Integer, iAge As Integer
Set MyDB = DBEngine.Workspaces(0).Databases(0)              ' Older syntax
Set MyTable = MyDB.OpenRecordset("testdate", DB_OPEN_TABLE) ' Open table

MyTable.MoveFirst   ' Locate first record

Do Until MyTable.EOF    ' Begin loop
  MyTable.Edit          ' Enable editing
  iDOY = DateDiff("y", "1-Jan", Format$(MyTable.[Age on This Date], "dd-mmm")) + 1
  iBDOY = DateDiff("d", "1-Jan", Format$(MyTable.[Date of Birth], "dd-mmm")) + 1
  iAge = Year(MyTable.[Age on This Date]) - Year(MyTable.[Date of Birth])
  If iBDOY > iDOY Then iAge = iAge - 1
  MyTable.[Computed Age] = iAge
  MyTable.Update        ' Save changes
  MyTable.MoveNext      ' Locate next record
Loop                    ' End of loop
MyTable.Close           ' Close table

[Computed Age].Requery

End Sub

Note that instead of using

Set MyDB = DBEngine.Workspaces(0).Databases(0)              ' Older syntax
Set MyTable = MyDB.OpenRecordset("testdate", DB_OPEN_TABLE) ' Open table

with Access 97, you could use either

Set MyTable = CurrentDB.OpenRecordset("testdate", DB_OPEN_TABLE) ' Open table
Set MyTable = CurrentDB.OpenRecordset("testdate", dbOpenTable)   ' Open table

Here is an alternate method to walk through a table. However, there is no good way to determine when the last record has been processed. (BTW, this algorithm gives incorrect answers.)

DoCmd.GoToRecord , , A_FIRST ' Locate first record.

Do Until 1 = 0 ' Begin infinite loop
  If [Computed Age] = 0 Then Exit Do   ' Get out of the loop.
    [Computed Age] = _
       Int(([Age on This Date] - [Date of Birth]) / 365.25 + 0.5) - 1
    DoCmd.GoToRecord   ' Locate next record.
Loop     ' End of loop.

MS Access XP

Don't confuse MS Access 97 and MS Access XP - they are not the same products, any relationship is purely cosmetic.

In the help, all the old VisualBasic (ie, MS Access 97) commands are are still listed ... but don't try to use them - they don't work! For instance

Dim dbsExample As Database

produces

Compile Error:

User-defined type not defined

Instead of the commands listed in the help, you have to use something like this.

Dim conDatabase    As ADODB.Connection
Dim Some_Recordset As ADODB.Recordset

Dim SQL_Str As String

    ' Return reference to current database.
SQL_Str = "SELECT * FROM Batch_Preparation_tbl " + _
          "WHERE [Batch_ID] = " + Batch_Name_UIEdit.Value + _
          "  and [SOP_Step] = '1'"

Set conDatabase = CurrentProject.Connection
Set Some_Recordset = New Recordset
Some_Recordset.Open SQL_Str, conDatabase

With Some_Recordset
  Performed_By_UIEdit.Value = !Performed_By
  Date_Prepared_UIEdit.Value = ![Date/Time]
  ' Debug.Print TempStr
End With

Some_Recordset.Close
conDatabase.Close

Set Some_Recordset = Nothing
Set conDatabase = Nothing

A Fix

Well, there is a partial fix to this problem. In MS Access XP,

Open a code page (the menu options are dependent on what type of page is displayed).
From the menu, select
In the dialog box, check (include)
(The version is probably machine dependent.)

This fixes MOST of the problems using the old examples ... but some variable declarations still cause a problem. In the following example, I've show 3 different ways to declare MyTable - in a real example, it should only be declared once.

  Dim dbsExample As Database    ' This works in both versions
  Dim MyTable As Recordset      ' This still fails in XP, but works with MS Access 97
  Dim MyTable                   ' Declaring a variant works in XP
  Dim MyTable As DAO.Recordset  ' This works in XP
  Set MyTable = CurrentDb.OpenRecordset("Test_LU", dbOpenTable)

Of course, I have never found any documentation about this ... instead, one of the people who read these pages gave me the tip.

Delphi 5.0

Unless you have manually created field objects, you should use FieldByName to select each field. with Table1 do begin Close; Open; while not EOF do begin tempStr := FieldByName('LASTNAME').AsString; Next; end;

Blob Fields

Delphi provides 4 stream types that can be used to read and write blobs. (Remember, any time you use a stream, be sure to Free it in a try..finally block.)

	Source File	Comment
TMemoryStream	classes.pas	Converts a buffer (block of memory) to/from a stream
TBlobStream	dbtables.pas	Used with BLOB fields
TIBBlobStream	ibblob.pas	Interbase BLOB fields - more properties and methods than TBlobStream
TIBDSBlobStream	ibcustomdataset.pas	Interbase Data Set BLOB fields - for internal use

Delphi suggests using TDataSet.CreateBlobStream to create the streams ... but there are no examples.

Examples in the Delphi help

Example	Description
TBlobStream.Create TIBBlobStream.Create	How to copy a blob field from one table to another
TBlobStream.Read	How to read a blob into a PChar Buffer
TBlobStream.Write	How to add data to the end of a blob

This reads data using a Memory stream

procedure TForm1.PlotSpectra();
var
 i:integer ;
 ar: array[0..315] of single;
 xx: TMemoryStream ;
begin

  xx := TMemoryStream.Create;
  try
   (Table1.FieldByName('SPECTRUM_Y') as TBlobField).SaveToStream(xx) ;  // read a blob
   // xx.Seek(0,soFromBeginning);        // eqivalent to xx.position := 0
   xx.Position := 0;
   xx.Read(ar, 316*4);  // 4 bytes per element, copies the data to the array
   Image1.Canvas.FillRect(Rect(0,0,image1.Width, image1.Height));
   Image1.Canvas.MoveTo(0, 0);
   for i := 0 to 315 do begin
     Image1.Canvas.LineTo(i, trunc(ar[i] * image1.Height/100));
   end;

  finally
   xx.Free ;
  end; // try

end;

ASP

These examples show several ways to open an ADO connection to an MS Access database. Though it is not shown in every example, you must always explicitly close and set to nothing every variable initialized with Server.CreateObject.

A Connection object with and without an already defined ODBC connection
A Recordset object with and without an already defined ODBC connection

This example uses a connection object to access an already defined ODBC connection.

Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") objConn.ConnectionString = "DSN=Northwind" objConn.Open Dim rs Set rs = Server.CreateObject("ADODB.Recordset") rs.Open "SELECT * FROM Table1", objConn while (NOT rs.EOF) ' code here processes each record returned by the query rs.MoveNext ' read next record wend ' -------- end of outer loop ' Though not shown, the following 4 lines are required in the other examples rs.Close Set rs = Nothing objConn.Close Set objConn = Nothing

This example uses a connection object to define a new ODBC connection

Dim objConn Set objConn = Server.CreateObject("ADODB.Connection") accessdb="c:\Inetpub\Development\xxxx\Budget.mdb" cn="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & accessdb objConn.ConnectionString = cn objConn.Open Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM Table1", objConn

This example uses a connection string to access an already defined ODBC connection.

Dim ConnectionStr ConnectionStr = "DSN=Northwind" Dim objRS Set objRS = Server.CreateObject("ADODB.Recordset") objRS.Open "SELECT * FROM Table1", ConnectionStr

This example uses a connection string to define a new ODBC connection

FromStatement = "table1" ' In practice, this is very complicated with several joins SQLbudget = "SELECT " & _ " sum([Vendor Jobs].[Proposed Cost]) as tsum1, " & _ " sum([Vendor Jobs].[Actual Cost]) as tsum2, " & _ " sum([Vendor Jobs].[IDT Cost]) as tsum3 " & FromStatement accessdb="c:\Inetpub\Development\xxxx\Budget.mdb" cn="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & accessdb rs2.Open SQLbudget, cn tsum1 = rs2("tsum1") tsum2 = rs2("tsum2") tsum3 = rs2("tsum3") if isNull(tsum1) then tsum1=0 if isNull(tsum2) then tsum2=0 if isNull(tsum3) then tsum3=0 tsum = tsum2 + tsum3 + tsum1 rs2.close set rs2=nothing %> Fiscal Year: <%=since_date%> - <%=end_date%><br> Total Expenditures: <%=formatCurrency(tsum, 2)%> <% ' Additional code goes here %>

In practice, defining the ODBC connection in the ASP file is "better" because you do not need to be a system administrator on the remote web server (ie, it is usually easier to get it working). There may be other reasons not to do this.

If you are opening several recordsets on the same database, it is better to use a connection object than to define the parameters in the recordset.open command - it is better and faster to create one connection instead of 5.

This is an example of a processing loop. The 2 nested loops produce an interesting ouput - most of the code is omitted in this example, there is just enough to give you the idea. The html table is defined outside the loop, only the rows are generated inside the loop.

while (NOT rs.EOF) tempStr=rs("grd_graphic") tempStr="<tr><td colspan=""3"" >" & tempSt & vbNewLine & _ "<td colspan=""4"" align=""right"">Total Expenditures: " & formatCurrency(subsum, 2) & _ vbNewLine Response.write tempStr do while (NOT rs.EOF) 'AND (y<200) '----------- loop AA for (explain why) %> <tr> <td nowrap valign="top"><%=rs("gr1")%> <td> (Additional table columns not show) <% if not rs.EOF then rs.MoveNext ' read next record end if if rs.EOF then exit do ' exit - no more records tempStr2 = rs("grd_graphic") if isNull(tempStr2) then exit do if tempStr2<>tempStr then exit do ' exit if the previous and new records do not match loop '---------------------------------end of loop AA for (should match comment above) wend ' -------- end of outer loop %>

References:

4GuysFromRolla.com provides a lot of information on accessing an MS Access database from ASP, for example
- There is a great FAQ
- Explicitly Freeing your ADO Objects explains how to open and close an ADO connection - including why it is very important to explicitly close the connections
- Database Connectivity provides a list of short articles to read

Restarting IIS

Each time you modify an ASP page, you need to stop and restart IIS - otherwise you can not see your changes. How to Restart IIS explains how to do that. Notice that I have written a program (Replace_dll.exe is free for you to download) that will do this automatically on Windows XP, except that it occasionally hangs IIS (especially when you don't explicitly close the connections). When hung, you should use the Control Panel to restart IIS.

Author: Robert Clemenzi - clemenzi@cpcug.org