CSCI 220 Topic: Computer Programming with Visual Basic
Week 8: Random Access Files and Databases

---

Custom data types- The TYPE Statement
  What it is
     A compound variable-combines many standard variable
     types
     Usually refers to a RECORD in a database.  -also called
     RECORD variable
     Usually fixed length---ideal for Random access
  The TYPE Statement
     Syntax:
       TYPE newtype
          var1 as datatype
          var2 as datatype
          var3 as datatype
       END TYPE
       GLOBAL Variable AS NewType
     Explanation:
       NEWTYPE is your custom data type.  Example BOOKTYPE,
       ADDTYPE.
       Usually ends in TYPE, some other code
       This is NOT a variable.  It is a new variable TYPE
       (like string, Integer, etc)
       Var1, etc are regular variable names.
       Datatype refers to STANDARD variable types (string,
       integer, single, etc)
     Example:
       Type AddressType
          Name as String * 30
          Phone as String * 15
          Add as String * 50
       End Type
       Global Address as AddressType
  Use of compound variables
     Much like OOP syntax
     Variable.Record
     EG Address.Name = "Andy"
                          
Random Access Files
  Description:
     Fixed - length records
     More like a CD player.  Individual records can be
     accessed directly
     Frequently use custom data types.
  OPEN Statement
     OPEN FileName for RANDOM AS # FileNumber LEN = LEN(RecordVariable)
     Example:
       OPEN "A:Address.dat" for Random as #1 Len = LEN(Address)
     SAME open statement used for reading, writing, and
     appending(!)
  PUT Statement
     Used to Copy information from a variable to the file
     Syntax:
       PUT # FileNumber, RecordNumber,RecordVariable
     Example:  to place the current contents of the address
     variable to the 4th position in the file:
       PUT #1, 4, Address
  GET Statement
     Identical to PUT, except transfers info from database
     to variable
     Syntax:
       GET # FileNumber, RecordNumber,RecordVariable
     Example:  to place the current contents of the 4th
     position in the file to the address variable  :
       GET #1, 4, Address
  Notes on use of GET and PUT
     Both are frequently put in separate procedures to
     simplify use
     Both require an OPEN statement before use, and a CLOSE
     statement after
     Sample GET procedure:
       SUB GETADDRESS
	  OPEN FileName for Random as # 1 len = len(address)
	    Get #1, RecNum, Address
	  Close #1
	  TxtName.text = Address.Name
	  TxtPhone.text = Address.Phone
	  TxtAdd.text = Address.Add
       END SUB
     Notes:
       This code assumes access to variables
       FileName,RecNum, and Address (the record Variable)
       Notice that we first GOT the variable from the file,
       then copied it to the form
       This code would probably go at form-level
     A similar PUT procedure would
       Copy the fields from the form objects to the
       variable
       Open the file
       Put the variable to the file
       Close the file
       
Other Database Concepts:
  Sequential access
     Used when records will be of differing length
     Allow more flexibility, more difficult programming
     Require programmer to keep track of record number
     Can use Custom variable types
     
  Parallel Arrays
     Loads all records into a record array (record variable)
     or series of arrays in memory
     Provides fast execution
     Loads from disk only once
     Only allows number of records that will fit into memory
     at one time.
     Information is volitile until saved.
     
  Linked Lists
     Each record contains a field that points to the next
     record
     Done by adding extra field(s) in custom variable type
     pointer field contains record number of next / previous
     record in field
     Allows for convenient sorting, slow searching
     Used in binary trees for quick searching
     Doubly-linked lists
     
  Indexing
     A seperate file or array is maintained comparing record
     number to position in a sorted list
     Various indices can be created sorting by different
     criterion
     Can be easily created with sorted list box technique in
     VB
     
  Relational databases
     Information is contained in more than one table of
     data.
     One table may track personell, another may track
     payroll
     Links between tables
     Allows flexibility
     
  Sorting Algorythms
     Bubble Sort - Slow and unwieldy, but easy to write
     Shell Sort -more efficient but more complex sort
     Quick Sort - among best overall techniques, uses
     recursion

VB8- Your Random Access Database

---

Description:
  Write a program that creates a random access database.
  It can be a phone book or any other kind of database, but
  keep it down to three fields per record to make the
  program manageable.
  
Preparation:
  Decide what your Record variable will look like.  Which
  fields will go in each record?  How long will strings be?
  (They MUST be fixed length)
  
Create your form.
  Use labels to name your record areas.  Create TEXT BOXES
  for user input.  Be sure to give the text boxes names
  that coincide with your FIELD names (EG TxtPhone for
  Phone field)  This will make your coding MUCH simpler

Create a new module (from the file menu).  Define your
record variable type with a TYPE statement
  Create a global variable of your new variable type
  Create a global constant FileName with the name of your
  data file as its value
  Save the module as VB8.BAS
  
Go back to the form general area.
  Create a form-level variable called RecNum
  Create a GetRec Subprogram
     This subprogram should:
       Open the file for random access
       Get the record in the RecNum position
       Close the file
       Copy the fields in the variable to the appropriate
       text boxes
  Create a PutRec  Subprogram
     This subprogram should:
       Copy the fields from the text boxes to the variable
       Open the file
       Put the variable to the file
       Close the file

Create Forward and back buttons
  Add code to the Forward button to:
     Put the old record
     Increment RecNum
     Get the next record
  Add similar code to the Back button:
     Put the old record
     Decrement RecNum
     If RecNum is less than 1, make it 1
     Get the previous record
Add Code to the Form_Load procedure that does this:
  Set RecNum to 1
  GetRec
Save program as VB8.Mak.  Use whatever form name you think
is appropriate (EXCEPT form1, of course!)