Access Table Topics

Form Topics | Query Topics

Report Topics| Module Code and APIs | General Issues

General

General Table Design Suggestions

Check out and Use the "Look Up" Feature for Fields When Designing Tables in Access 95 and 97

How To Set a Table's Visible Attribute Via Code

Setting A Field's Format/Decimal Places Via Code Either During Table Creation or After a Make Table Query




More to Come…. .

© 1998 ATTAC Consulting Group



















Back to the Tips List

General Table Design Suggestions
There are a number of general design issues with tables which you should be mindful of when you are starting a new database design.
  1. Don't use spaces in either table names or field names. This is important for a number of reasons:

    1. When writing VBA code, if you have spaces in your table or field names, then you must bracket "[ ]" your table and field names because of the space. This takes up more storage space in code, and slows down your writing.

    2. If you are going to "up size" your application to MS® SQL Server, SQL server doesn't support spaces in table names, most other client server dbs don't either. (Note: if you've already set up table with spaces and are going to upsize, then get one of the find and replace add ins noted on our " Other Web Site" page and fix this before you run the upsize wizard.)

  2. Avoid using Access' VBA reserved words in field names. A key example of this is nameing a field "Name" This will cause Access to return the error "?Name" when this field is referenced in a form or report, especially via code.

  3. Don't over index your table. The fewer indexes which the table has, the faster forms will open and queries will run. Learn about multiple field indexes if you often use two or more fields to find records. This can lower the overall number of indexes you use and speed queries.

  4. Do prefix your table names with a naming convention standard prefix such as "tbl", this will make your vba code easier to read especially if you have forms of the same name.

  5. Take the time to fill out field descriptions it will make maintaining your db easier.

  6. If you are going to use replication, avoid auto number fields since you can end up with some bizzare numbers in the design master as replication occurs.

  7. Normalize your tables. For beginners, this means setting up tables to avoid records which have identical data in multiple fields. A normalized table design will usually only have identical data in at most one field in a table. For example if you have multiple orders per customer, set up a separate orders table and then relate each order to customer (using something like a customer ID). If you need to learn more then buy a good book and read up!

  8. Keep look up tables which might contain non-changing information (e.g. state abbreviations,) as local tables in applications where the data is split from the front end application. This will reduce network traffic, speed from loading and enhance query speed.


Back to the Tips List

How To Set a Table's Visible Attribute Via Code
You may desire to have certain of your tables, such as set up information, hidden in normal use. Here's a couple of ways to do it.
  1. The non-code way is to prefix your table name with "USys_", this will make the table visible only if the user has "Show System Objects" set to true.

  2. The other way to hide a table in Accesss 95 or 97 is to set its attribute property to hidden. Unlike many other attributes, this one is read/write so it can be changed at any time. In addition in Access 95 and 97 if you hide your tables using this method, the user can't see it, even if they set the "Show Hidden Objects" option to true. Here's the code to do it:
    Function HideTbl(strTable As String, intHide As Integer) As Integer
    
    '*********************************
    
    'Purpose:   Hides or Shows Tables
    
    'Accepts:   intHide: True (-1) to hide table, false (0) to unhide
    
    'Returns:   True on Success, False on Failure
    
    'Note: Don't compact the db with a table hidden with this method, 
    
    '           it will delete the table.  The table needs to be unhidden 
    
    '           prior to compacting.
    
    '********************************
    
    On Error GoTo HT_ERR
    
    
    
    Dim TDef As TableDef
    
    Dim CurDB As Database
    
    
    
    Set CurDB = CurrentDb()
    
    Set TDef = CurDB.TableDefs(strTable)
    
    
    
    Select Case intHide
    
        Case True
    
            If Not (TDef.Attributes And DB_HIDDENOBJECT) Then
    
                TDef.Attributes = TDef.Attributes + DB_HIDDENOBJECT
    
            End If
    
        Case Else
    
            If (TDef.Attributes And DB_HIDDENOBJECT) Then
    
                TDef.Attributes = TDef.Attributes - DB_HIDDENOBJECT
    
            End If
    
    End Select
    
    
    
    HideTbl = True
    
    
    
    EXIT_HT:
    
        Exit Function
    
    HT_ERR:
    
        HideTbl = False
    
        MsgBox "Error: " & Err & " " & Error, 48
    
        Resume EXIT_HT
    
        
    
    End Function
Note: Be sure not to compact a db with a table hidden in this manner if the table is a permanent table. Both Access 95 and 97 have a "bug" which will result in the table being deleted if its "hidden" attribute is set to true when the db is compacted.

Back to the Tips List

Setting A Field's Format Via Code Either During Table Creation or Afterwards
When you are creating a table using code you may want to set a field's format or number of decimal places.

Alternately if you run a make table query using already formated fields as an input, you will find that the new table does not carry over the formatting of your input fields. Therefore in each situation, you need to set the format for the field.

The format and decimal places properties of a field do not exist until they are created, so if you query a field's "format" property before it is created, you'll get an error saying there is no such property. So here's some code which creates a simple table, and then sets the format and decimal places properties for a couple of fields. You can strip out the code for the format section to create a new function for setting the format for a table after running a make table query.

Function acg_CreateTable(strTable As String) As Integer

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

'Purpose:  Creates A new table and sets field format

'Accepts:  strTable, the name of the new table

'Returns:  True (-1) on success, False on failure

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

On Error GoTo ErrCT



Dim TDB As Database

Dim fld1 As Field, fld2 As Field, fld3 As Field

Dim fFormat2 As Property, fFormat3 As Property

Dim idxTbl As Index

Dim idxFld As Field

Dim Newtbl As TableDef

Dim Newtbl2 As TableDef



acg_CreateTable = True



'First Create the table



Set TDB = CurrentDb()

Set Newtbl = TDB.CreateTableDef(strTable)

Set fld1 = Newtbl.CreateField("MyStringField", DB_TEXT, 75)

Newtbl.Fields.Append fld1

Set fld2 = Newtbl.CreateField("MyNumberField", DB_SINGLE)

Newtbl.Fields.Append fld2

Set fld3 = Newtbl.CreateField("MyDateTimeField", DB_DATE)

Newtbl.Fields.Append fld3

TDB.TableDefs.Append Newtbl



'Create an index for our table.  Need to use a new tabledef 

'object for the table or it doesn't work



Set Newtbl2 = TDB.TableDefs(strTable)

Set idxTbl = Newtbl2.CreateIndex("PrimaryKey")

idxTbl.Primary = -1

idxTbl.Unique = -1

Set idxFld = idxTbl.CreateField("MyStringField")

idxTbl.Fields.Append idxFld

Newtbl2.Indexes.Append idxTbl



'Format the single field to have two decimal places

'and the datetime field to be a medium time.

'Note that decimal places has no space in the name



Set fld2 = Newtbl2.Fields("MyNumberField")

Set fFormat2 = fld2.CreateProperty("DecimalPlaces", DB_BYTE, 2)

fld2.Properties.Append fFormat2

Set fld3 = Newtbl2.Fields("MyDateTimeField")

Set fFormat3 = fld3.CreateProperty("Format", DB_TEXT, "Medium Time")

fld3.Properties.Append fFormat3



TDB.Close

    

ExitCT:

    Exit Function

ErrCT:

    If Err <> 91 Then TDB.Close

    acg_CreateTable = False

    Resume ExitCT

End Function

Back to the Tips Index

Checkout and Use the "Look Up" Feature for Fields When Designing Tables in Access 95 and 97
One of the more powerful features added to Access 95 and 97 was the ability to specify a "Look Up" query for fields in tables, to get a value from another table as an option for the target table's field value.

What this capability provides is the ability set a query as a source for a field in a table, so that you can reference look up tables. (e.g. state abbreviations, specific product types etc.) You can also set a limit to list property for the look up field, which is generally recommended, since you can't respond to an event where the user enters a value directly in the table other than what's in the look up list. (You can respond on a form using vba.)

Here's some practical advantages and ways to use this new capability:

  1. First, unlike Access 2, the Look Up specification allows you to use combo boxes in a datasheet view of a table to provide prespecified values and limit entry in a field.

  2. You can save data storage space in your tables by storing only numeric values for look up strings which are in your look up table. To do this:

    1. Add a corresponding numeric number for each string value in your look up table.

    2. Set the field type of the target table to number.

    3. Add both the string and numeric values to the query for Look Up.

    4. Set the bound column for the Look Up in the target table to the number field in the look up query.

    5. Set the column width for the number field as zero width to make it invisible, and the column width for the string field to something appropriate to accomodate its width.

      Now when your user uses the combo box to select a value from the Look Up only the string value which they understand will be shown, but a number will be stored in the target table.

  3. In similar fashion, if you create a query based on the table with a predefined lookup, and have set up the look up field as a number as shown above, only the corresponding string value will be displayed in your query as Access automatically will create a subquery for the look up.

    This eliminates many of reasons to have to use the "Choose" function of Access which was tremendously slow when used in queries.

  4. When you set a look up field query, then when you create a form based on the table or query which includes the table, a combo box will be the default control type created rather than a text box.

Back to the Tips List