Unofficial Access Stuff

Use your pdf converter to make your pdf files easy! You can now buy software that makes converting pdf to doc possible! Did you know you can even convert pdf to word?

Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal

Host your Web site with PureHost!

Friday, April 27, 2018 – Permalink –

Add a Table

Drag drop trick

While working with a query in Design view, you may find that you need to add a table or query. The "book" way to do it is to click the Show Table toolbar button, drag the appropriate objects from the list, and then close the dialog box.
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: General, Reference, Tables, Tips, Tutorials

<Doug Klippert@ 3:44 AM

Comments: Post a Comment

Thursday, January 25, 2018 – Permalink –

Quickly Query Table Names

Change by code

If you've ever developed a dozen or more complex queries, then had to change one of the table names, you know how frustrating it can be to all but rebuild the queries in the Query Design view grid by changing the table name in each cell.

One quick alternative is to choose View >SQL View while the query is open and then cut and paste all the SQL code into Word.

Next, do a Find and Replace, changing all the instances of the old table name to the new table name.

Finally, copy and paste the SQL statements back into the SQL view of your Access queries.

When you go back to the QBE, the new table will replace the old one.

See all Topics access
Labels: General, Queries, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:06 AM

Comments: Post a Comment

Sunday, October 01, 2017 – Permalink –

Use a Table Alias

to change names in a query

When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.

To set a table alias in the query's Design view,

Right-click on the table field list and choose Properties.

Then, enter the alias name you want to use in the Alias text box.

See all Topics access
Labels: Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:10 AM

Comments: Post a Comment

Sunday, June 04, 2017 – Permalink –

Automatically Renumber Records

Been looking for this

What happens to a list when one record is deleted?

If you need to correct the numbering, see these instructions.

Automatically renumber the records in an Access table when one is deleted

from Martin Green - FontStuff.com

See all Topics access
Labels: General, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:33 AM

Comments: Post a Comment

Saturday, August 27, 2016 – Permalink –

List Fields in Access Table

Bit o' code

When viewing a table that has many fields in Design view, you have to scroll up and down to review the field names.

This can be tiresome when you're referring to them constantly, and particularly when you're working with several tables.

The following code produces a field listing for a given table. This can then be copied to Notepad and printed for easy reference.

Enter the code into a module, substituting your table's name where appropriate.

Open the Debug/Immediate window, type ListFields,

Press Enter to produce the listing.

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

See all Topics access
Labels: General, Reference, Shortcuts, Tables, Tips, Troubleshoot, VBA

<Doug Klippert@ 3:19 AM

Comments: Post a Comment

Sunday, June 19, 2016 – Permalink –

Copy Paste

Excel tables

One way to create a new table in an Access database from information included in an Excel spreadsheet is to select the pertinent data on the spreadsheet, including the field names.

Copy the selection (Edit>Copy, or CTRL+C)

Switch back to Access .

With Tables objects being shown in the database window, choose Edit>Paste, or use the CTRL+V shortcut.

Access will ask if the first row contains the field names and then will paste the information as a new database table.

See all Topics access
Labels: General, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:21 AM

Comments: Post a Comment

Monday, January 25, 2016 – Permalink –

Duplicate Table Structures

No need to reinvent

When setting up a database, you may find that there is a table structure in another database that would be appropriate. You could Import the table, but then you have to clear out the useless records.

Here's an easier way to do it:

Choose File>Get External Data>Import from the menu bar
(External Data tab in 2007, then select Access in the Import group)

Select the database that contains the tables you want to copy and click the Import button.

Select the appropriate tables on the Tables sheet

click the Options button, select the Definition Only option and click OK

.

To export a table definition from your current database to another:

Select the appropriate table in the Database window.

In Access 2000 and above, choose File>Export.
(If you're using Access 2007, choose More in the Export group)

Click OK.

Now, regardless of version, select the target database and press Enter

When the Export dialog box appears, select the Definition Only option button and click OK. See all

See all Topics access
Labels: General, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:47 AM

Comments: Post a Comment

Monday, January 11, 2016 – Permalink –

Linked Table Problems

Lost functionality

Have you found that you cannot update linked Excel tables in Access 2003+?

If you have installed Microsoft Office 2003 Service Pack 2 (SP2):

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.

Support.Microsft.com:
You cannot change data in tables that are linked to an Excel workbook

You might try reinstalling and then only installing SP1.

Also:

PCWorld.com:
Patent ruling costs Microsoft $8.9 million

"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.

See all Topics access
Labels: General, Reference, Tables, Tips, Troubleshoot, Tutorials

<Doug Klippert@ 3:44 AM

Comments: Post a Comment

Wednesday, January 06, 2016 – Permalink –

Toggle Object Views

Use the keyboard

When you are putting a database together you often want to switch between views of Access objects to see the changes.

For instance, you'll switch the view to examine a Table in Design view and then back to data view. It is can be faster to switch between views using keyboard shortcuts, rather than the View menu.

You can cycle through the views of an open object using the period

Ctrl + .

or comma

Ctrl + ,

shortcut keys.
These shortcuts can be used with tables, queries, forms, reports, and data access pages.

See all Topics access
Labels: Forms, General, Queries, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

Sunday, October 18, 2015 – Permalink –

Drag Data

Simple exchange

To transfer data from an Access query or table in another Office program, such as Word, there's no need to manually export the data.

Open the target Office document

Arrange both applications on the screen
(Right-click an empty part of the Task bar and choose Tile Windows Vertically)

Switch to Access and select the fields or records that you want copied

When you've finished selecting the data, move the mouse pointer near the border of the selection until it turns into an arrow

Finally, drag and drop the data to your target document

You can also select a whole table, go to Edit>Copy. Switch to Word or Excel and Paste.
It works in the other direction too. Select some Excel data. Switch to Access. While viewing the Tables Objects, Paste the Excel data. It will form a new table.

See all Topics access
Labels: Entries, General, Reference, Shortcuts, Tables, Tips, Tutorials

<Doug Klippert@ 3:41 AM

Comments: Post a Comment

Friday, October 09, 2015 – Permalink –

Source of Tables

Identify a linked Access table's source

When a database contains linked tables, you may need to find the location of the source file.
One way is to:

Open the table in Design view

Clicking Yes when Access informs you that some properties can not be modified.

Right-click on the window's title bar

Select Properties from the shortcut menu.

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)

See all Topics access
Labels: General, Reference, Tables, Tips, Tutorials

<Doug Klippert@ 3:54 AM

Comments: Post a Comment

Sunday, September 27, 2015 – Permalink –

Text Box Highlights

Change background

It can be difficult to tell which text box on a form you're currently working with.

One solution is to highlight the current position, with a different background.

Access 2000+ allows you to do this with conditional formatting, but you can also get a similar result using code.
To do so, create a new Module and add the following code:

Function Highlight(Stat As String) As Integer Dim ctrl As Control On Error Resume Next Set ctrl = Screen.ActiveControl If Stat = "GotFocus" Then ctrl.BackColor = 65535 ElseIf Stat = "LostFocus" Then ctrl.BackColor = 16777215 End If End Function

Save and close the Module, then open the appropriate Form in Design view.
Click the Code button and insert =Highlight("GotFocus") in each of the Form's textbox control's GotFocus event procedure.
Likewise, add =Highlight("LostFocus)") to each textbox's LostFocus event procedure.

When you've finished, save the changes, close the VBE, and switch to Form view.

When you tab to a field, it's shaded yellow. When you tab away from the field, its background is restored to white.

Also:

Allen Browne:
Field highlighting solutions

See all Topics
Labels: Customize, Entries, General, Reference, Tables, Tips, Tutorials, VBA

<Doug Klippert@ 3:42 AM

Comments: Post a Comment

Sunday, May 17, 2015 – Permalink –

Hidden Tables

Don't peek

If you do not want the ordinary user to see a table in your database, you can hide it in the database windows so that other users cannot select it.

Preface the table's name with the four letters 'Usys'. For example, if your table's name is ProprietaryCosts , change it to Usys ProprietaryCosts. This makes your table into a system object which Access does not display.

To see this table later, in Tools>Options, on the View tab in the Show section, put a check mark by System objects.
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: General, Reference, Tables, Tips, Tutorials

<Doug Klippert@ 3:32 AM

Comments: Post a Comment

Tuesday, April 28, 2015 – Permalink –

Number Entries

Beyond AutoNumber

Embedding information in a Primary key or ID, can lead to trouble in the future.
(If the first three numbers are to represent the warehouse address, what happens if new addresses have four numbers?)

Autonumbering can give a false sense of order. There is an initial tendency to try to keep all database records in some order. This violates the sense of a relational database.

The records can be sorted or filtered as needed.

Still some record numbering scheme may be desired.

Allen Browne's Access tips:
Numbering Entries in a Report or Form

"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?"

See all Topics access
Labels: Entries, General, Reference, Tables, Tips, Tutorials, VBA

<Doug Klippert@ 3:16 AM

Comments: Post a Comment