Bit o' code

Sub ListFields()
Dim dbs As DATABASE
Dim dbfield As Field
Dim tdf As TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!NAMEOFYOURTABLE

Debug.Print ""
Debug.Print "Name of table: "; tdf.Name
Debug.Print ""

For Each dbfield In tdf.Fields
Debug.Print dbfield.Name
Next dbfield
End Sub

Labels: Tables, Tips, VBA

Click to Word, PowerPoint, Excel

1. Open the target document and the Access table that contains a hyperlink field.
   
   
2. Select some of the text at the beginning where you want the hyperlink to jump.
   
   
3. Hold down the Ctrl key, drag the selection to the Access hyperlink field you want to set up.
   
   
4. When you release the mouse button, the previously selected text is used as the hyperlink text and the link becomes active.

Labels: Shortcuts, Tables, Tips

Excel tables

Labels: Tables

Transfer data

Some simple guidance of how to transfer data from Excel or Access into HTML web pages using XML data files. VBA programs can be used to export data tables from Excel or Access into simple XML files. There are several examples of using different methods to display the XML and XSL files on web pages in order to quickly share your data with others.

Labels: Tables, Tutorials

Link in a snap

1. Choose File>Open from the menu bar
   
   
2. Select the appropriate data format from the Files Of Type dropdown list
   
   
3. Open the file as you would any Access database

Labels: General, Tables, Tips

Table fields on the fly

Labels: Tables, Tips

Multi-Field Keys

1. Hold the Ctrl key and click each field that you want to make up the primary key.
   
2. Choose Edit>Primary Key from the menu bar, or click the Primary Key button on the toolbar

Labels: Tables, Tips

No need to reinvent

1. Choose File>Get External Data>Import from the menu bar
   (External Data tab in 2007, then select Access in the Import group)
   
2. Select the database that contains the tables you want to copy and click the Import button.
   
3. Select the appropriate tables on the Tables sheet
   
4. click the Options button, select the Definition Only option and click OK

1. Select the appropriate table in the Database window.
   
2. In Access 2000 and above, choose File>Export.
   (If you're using Access 2007, choose More in the Export group)
   
3. Click OK.
   
4. Now, regardless of version, select the target database and press Enter

Labels: Tables, Tips

Lost functionality

MORE INFORMATION
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.

"A jury in U.S. federal court found that Microsoft infringed on a Guatemalan inventor's 1994 patent on technology linking the company's Access and Excel programs, and ordered the world's largest software maker to pay $8.9 million in damages.

Labels: Tables, Tips

Data bits and pieces

"Data modeling is probably the most labor intensive and time consuming part of the development process. Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.

The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language, it can be reviewed and verified as correct by the end-users.

The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements."

Labels: General, Relationships, Tables, Tutorials

"In general, a key field should have these characteristics:

Should be One Field

It is possible to define multiple fields as the key fields of a table, but a single field is preferable. ...Far better to have a CustomerID number than a combination of other fields to define a customer.

Should be Numeric

Access offers an AutoNumber field that is a Long Integer which is ideal for key fields. These values are automatically unique for each record and supports multi-user data entry as well.

Should Not Change Over Time

A key field should not change over time. Once identified, like a social security number, it should never change.

Should be Meaningless

To ensure a key field doesn’t change over time, it should have no meaning and therefore no reason to change over time.

• What Data Do You Need?
  
• What Are You Going to Do with the Data?
  
• How Is Your Data Related to Each Other?
  
• What Is Going to Happen to the Data Over Time?
  
• Learn How to Use Queries

Labels: General, Tables

Simple exchange

1. Open the target Office document
   
   
2. Arrange both applications on the screen
   (Right-click an empty part of the Task bar and choose Tile Windows Vertically)
   
   
3. Switch to Access and select the fields or records that you want copied
   
   
4. When you've finished selecting the data, move the mouse pointer near the border of the selection until it turns into an arrow
   
   
5. Finally, drag and drop the data to your target document

Labels: General, Tables, Tips

Identify a linked Access table's source

1. Open the table in Design view
   
   
2. Clicking Yes when Access informs you that some properties can not be modified.
   
   
3. Right-click on the window's title bar
   
   
4. Select Properties from the shortcut menu.
   
   
5. The location will appear in the Description text box in the Table Properties dialog box.

(Use Shift+F2 to open the Zoom box, if necessary)



ComputerBooksOnline

See all Topics

access

Labels: Tables, Tips

Excel Mock-up

"This article explains how to use Microsoft Excel to create prototypes of your Access databases and how to make sure your data adheres to a set of rules called normal forms. Normal forms aren't hard to understand, and they're critical to designing useful databases."

Design Access databases with normal forms and Excel

Also see:

Database normalization Webcast

An Introduction to Database Normalization

Database normalization basics

Database Normalization Basics for Developers

Also see:

Tushar-Mehta.com:
Building and using a relational database in Excel


See all Topics

access

Labels: Tables, Tutorials

Don't peek

If we agree among ourselves not to tell the uninitiated, no one need be the wiser.

Microsoft Office Online:
How to Hide Objects in the Database Window

Here's how it works in 2007:
Hide tables as system tables in Access 2007



See all Topics

access

Labels: Queries, Tables, Tips

As Tables

1. Select File>Get External Data Import from the menu bar.
   (External Data tab, Import in 2007)
   
2. Select the appropriate database and click Import.
   
3. Select the queries you want to import on the Import Objects dialog box's Queries sheet.
   
4. Next, click the Options >> button and select the As Tables option button on the Import Queries panel.
   
5. Finally, click OK

Labels: Queries, Tables

Beyond AutoNumber

"In relational database theory, the records in a table cannot have any physical order, so record numbers represent faulty thinking. In place of record numbers, Access uses the Primary Key of the table, or the Bookmark of a recordset. If you are accustomed from another database and find it difficult to conceive of life without record numbers, check out What, no record numbers?"

Labels: Tables

Outlook Contacts in Access

1. Choose File >Open from the menu bar.
   (Office button>Open in 2007)
   
2. Under Files Of Type choose Outlook().
   
3. Locate your Outlook PST files.
   
4. Choose Contacts, or if you have set up separate files for different groups choose an appropriate one.
   
5. The wizard walks you through the process of creating an Access database with a linked Contact table.

Labels: Relationships, Tables, Tutorials

More data

An introduction to the benefits of online analytical processing (OLAP)

"Every day we create reams of data in customer relationship management applications, order entry applications, and warehouse management systems. We're drowning in a sea of data. However, even with all that data we don't have a large amount of information. We have the ones and zeros of the transactions, but we don't have the answers we need to simple questions like:

• "Why was March better than February?"
  
• "Where is the sales force having the most success?"
  
• "In what conditions does the sales team struggle with making sales?"

• Oracle 9i makes data warehousing easy to implement
  
• Seven highly effective steps to a smooth data warehouse implementation
  
• Business intelligence is just a few steps away for SAP R/3 users
  
• Resources for designing, planning, and implementing a data warehouse strategy
  
• Making the operational case for data warehousing
  
• TechRepublic Tutorial: Data warehousing defined

Also see:
OLAP Cube

Data Warehousing and OLAP
A Research-Oriented Bibliography

FAQ Excel 2007 – OLAP

Microsoft:

OLAP



See all Topics

access

Labels: List, Tables, Tips

Drag drop trick

There is another way to do this.

Drag the table or query object's icon from the Database window/Navigation pane directly to the top half of the query design grid.

You can also use this technique in Access's Relationships window




See all Topics

access

Labels: Queries, Tables, Tips

Speed up table creation with default field settings

When you add fields to a table, Access assumes you want to use a 50/255-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones.

You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so:

1. Choose Tools> Options from the menu bar and switch to the Tables/Queries sheet.
   
   
2. Select the data type you use most from the Default Field Type dropdown list.
   
   
3. Set the Text and Number sizes you usually want to use in the Default Field Sizes panel and
   
   
4. Click OK.

Labels: Customize, Tables

Labels: General, Tables, Tips

to change names in a query

1. To set a table alias in the query's Design view,
   
2. Right-click on the table field list and choose Properties.
   
3. Then, enter the alias name you want to use in the Alias text box.

Labels: Queries, Tables

Don't be smart

1. Create your table with an AutoNumber type field, but don't enter any records.
   
2. Create another table with only a single Long Integer Number type field.
   
3. This field must have same name as the AutoNumber field in the first table.
   
4. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.
   
5. Now create an append query to append the record in the second table to the first table and run the query.

You can now delete the second table and begin entering your data into the first table.

Also:

Access AutoNumber Reset

"This is some sample code that shows how to programmatically reset all AutoNumber fields in an Access Database to a correct value (whether it be 0 or the max value + 1). In addition, it contains code for Compacting and Repairing an MS Access Database. This is perfect for people who are working with a complicated Access Database and have experienced AutoNumber bugs!

And:

Creating an AutoNumber field from code



See all Topics

access

Labels: Tables

Been looking for this

Labels: Tables