Skip to content
Tutorials Center
My learning

Topics

Lesson 4 of 36 ยท Getting Started

Access File Formats, Templates and Trusted Locations

Understand ACCDB, ACCDE, ACCDR and MDB files, start from templates, split a database, set up trusted locations and fix the yellow security warning bar.

An Access database is not just one kind of file. The extension tells you what you can do with it: edit it, run it only, convert it or open it in an old version. Access also treats files with suspicion by default, so a downloaded database might open with its code disabled until you tell Access to trust it.

This lesson explains the common file types, how to start from a template, how to choose the right file format when you save and how to set up trusted locations so your own databases open without nagging. You will also see how to keep the file healthy with Compact and Repair.

The file extensions

Extension Name Purpose
.accdb Access database The standard format since Access 2007. Editable, supports multivalue fields, attachments and calculated fields.
.accde Access execute-only A compiled copy with the VBA source code removed. Users can run forms and reports but not change designs or see code.
.accdr Access runtime An .accdb renamed so it only opens in the free Access Runtime. Behaves like .accde for design changes.
.accdt Access template A reusable starting point for new databases.
.mdb Legacy database Access 2003 and earlier. Still opens in current versions, but lacks newer features.
.mde Legacy execute-only The .mdb equivalent of .accde.
.laccdb / .ldb Lock file A small temporary file next to an open database that records who is using it. Access deletes it when the last user closes the file.

Which file do I give people?

.accdb For you, the developer
.accde For users, no design changes
.accdr Runtime only, no Access needed

Keep the .accdb safe; you cannot get it back from an .accde.

1 / 2

You cannot turn an .accde back into an .accdb. Always keep the original .accdb and make the .accde from it. If you lose the source, you lose your code and your design.

Choosing a format when you save

Use File > Save As. Under Save Database As you can pick:

  • Access Database (*.accdb) for the current format.
  • Access 2002-2003 Database (*.mdb) or Access 2000 Database (*.mdb) if you must share with someone on very old software. Newer features such as attachment fields and multivalue fields do not survive the conversion.
  • Template (*.accdt) to make a reusable template.

Under Save Object As you can save a single table, form or report under another name, or as a PDF or XPS.

To build an execute-only copy, choose Database Tools > Make ACCDE (found on the Database Tools tab in recent versions, or through File > Save As > Make ACCDE). Access asks where to save the new file.

Starting from a template

The start screen offers templates that include ready-made tables, queries, forms and reports for common jobs: contacts, tasks, event management, a student list, a sales pipeline and others. Click one, choose a file name and click Create.

Templates are a good way to see how a finished database is organised. They are also a poor choice if you do not understand what you inherit. A template might include more tables than you need, or opinionated field names. A sensible approach is to create the database from a template, study its relationships on the Database Tools > Relationships window and then delete or adapt what you do not need.

Some online templates may be unavailable in your region or version of Access. If the start screen shows none, you can still build everything from a blank database, which is what this tutorial does.

To make your own template, set up a database with the tables and objects you want to reuse, remove sample data and choose File > Save As > Template. Give it a name and description. The next time you open Access, your template appears under the Personal tab on the start screen.

Why Access shows a Security Warning

Access databases can contain macros and VBA code, both of which can run commands on your computer. To protect you, Access disables that active content in any file that is not in a trusted location and not digitally signed by a trusted publisher. You see a yellow bar under the ribbon:

SECURITY WARNING Some active content has been disabled. Click for more details. [Enable Content]

What is blocked? VBA code, macros, some action queries and the use of certain functions. What still works? Tables, regular select queries, and forms and reports that do not depend on code.

Your options:

  1. Click Enable Content for this one session if you trust the file's source.
  2. Move the file to a trusted location (recommended for your own databases).
  3. Sign the database with a digital certificate (best for distributing to many users).

Never enable content from an email attachment or a download you do not recognise. A malicious macro can read your files, delete data or install other software.

Setting up a trusted location

A trusted location is a folder whose files Access opens with all content enabled. To add one:

  1. Go to File > Options > Trust Center > Trust Center Settings.
  2. Select Trusted Locations.
  3. Click Add new location, browse to your folder and, if you need it, tick Subfolders of this location are also trusted.
  4. Click OK three times to close the dialogs.

Choose a folder that only you and people you trust can write to. A shared network folder where any employee can drop files is a bad trusted location. Also avoid Downloads and the Desktop. A good example is C:\AccessApps.

Microsoft blocks macros in files that were downloaded from the internet. Such files show a red Security Risk bar instead of the yellow one. To open them, right-click the file in File Explorer, choose Properties and tick Unblock if you trust the source, then move it to a trusted location.

Compact and Repair

Every time you delete records or objects, an Access file keeps the empty space it used. The file grows but never shrinks on its own. Temporary objects from queries also add bloat. Compact and Repair rewrites the database into a fresh file, reclaiming space, rebuilding indexes and repairing minor corruption.

Run it from Database Tools > Compact and Repair Database with the database closed to other users. To run it automatically, open File > Options > Current Database and tick Compact on Close. This is good for a single-user file, but never for a shared one, since compaction needs exclusive access.

You can think of Compact and Repair as defragmenting your database. Do it before a backup if the file has grown much larger than expected, and always before you hit the 2 GB limit.

Converting older databases

Opening a .mdb file in a recent Access leaves it in its old format. To upgrade, use File > Save As > Save Database As > Access Database (*.accdb). Access creates a new .accdb and leaves the old file unchanged. Test the new file thoroughly, because queries that used deprecated features may behave differently and some old objects, such as data access pages, are gone.

Seeing the structure with SQL

Everything above concerns the file around your data. Inside, the schema is still ordinary SQL. This example shows a small table of "templates" you might store in your own database to track which file type each project ships as. It checks the data with a query and uses a CASE expression, which in Access SQL is written with IIf.

SQL Which projects ship as which file type?

Press Run (or Ctrl + Enter) to see the result here.

In Access SQL, nested IIf(FileName Like "*.accdb", "Editable", IIf(...)) does the same job, and the wildcard is * instead of %. Access also has a Switch function that works like a flat CASE. These differences are the topic of the lesson on criteria and expressions.

A practical checklist

Before you hand a finished database to users:

  1. Run Compact and Repair.
  2. Make a dated backup of the .accdb.
  3. Make an .accde copy for users, or distribute with the free Access Runtime.
  4. Put it in a folder that is a trusted location on their computers, or sign the file.
  5. Test opening it as a normal user, not as yourself.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which file type lets users run forms and reports but hides the VBA source code?

    .accde is a compiled, execute-only copy. It removes the editable source and prevents design changes.
  2. 2. What does the .laccdb file next to an open database indicate?

    The lock file tracks who is using the database. Access removes it when the last user closes the file.
  3. 3. What is the best way to stop the yellow Security Warning bar for your own databases?

    Files in a trusted location open with content enabled. Choose a folder that only trusted people can write to.
  4. 4. Why run Compact and Repair?

    Deleted records leave empty space in the file. Compact and Repair rewrites it into a smaller, healthier file.

Summary

  • .accdb is the editable format, .accde and .accdr are execute-only or runtime copies, .mdb is the legacy format and .laccdb is a temporary lock file.
  • Always keep the original .accdb; you cannot recover it from an .accde.
  • Templates give you a starting point, but study what they create before you rely on them.
  • Access disables macros and code in untrusted files; use trusted locations or digital signatures instead of clicking Enable Content for everything.
  • Compact and Repair shrinks and repairs the file; back up before and after major changes.

Next: Creating Tables in Access