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!

Sunday, September 10, 2017 – Permalink –

Split Access Database

Separate tables

You don't need to keep all of your data in one file. You can split your MDB file into data and application files.

"Even if all your data is in Access itself, consider using linked tables. Store all the data tables in one MDB file - the data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the application file.
In multi-user situations, each user receives a local copy of the application file, linked to the tables in the single remote data file."

Maintenance: To update the program, just replace the application file.
Since the data is in a separate file, no data is overwritten.

Network Traffic: Loading the entire application (forms, controls, code, etc)across the network increases traffic making your interface slower.

In some cases you will link additional files:

Static look-up data such as postal codes might be kept in its own file.

Linked temporary tables might avoid the need to compact the application file.

From Allen Brown's tips for Access users
Access has a tool to do the splitting for you, go to: Tools>Database Utilities Database Splitter
In Access 2007+:

On the Database Tools tab, in the Move Data group, click Access Database.

In the Database Splitter dialog box, click Split Database.

Type a name for the back-end database, and then click Split.

Also:
Knowledgebase:
How to manually split a Microsoft Access database

MSDN:
About sharing an Access database on a network

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

<Doug Klippert@ 3:47 AM

Comments: Post a Comment