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
|
![]() ![]() |
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.
|
![]() ![]() |
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.
|
![]() ![]() |
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 |
![]() | 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:
|