Data Storage

[Guest]

I have a basic/general question about data storage, to make sure I am
backingup all the data. When you create a database and start loading data
into it is are all the data files stored within the main database file. So
you only have one file to backup? I can only find the one file.

Someone said something about splitting out the data files. How is that done
and when is that advantageous?

Thanks

 

[Dirk Goldgar]

In

I have a basic/general question about data storage, to make sure I am
backingup all the data. When you create a database and start loading
data into it is are all the data files stored within the main
database file. So you only have one file to backup? I can only find
the one file.

Someone said something about splitting out the data files. How is
that done and when is that advantageous?

Unless you take specific action to make it otherwise, Access stores all
its data in one data file, the .mdb file you created. Under this
configuration, that is the only file you have to backup.

If you are sharing a database among multiple users on a network, it's a
good idea -- almost essential, if you want to minimize the chance of
corruption in the database -- to split the database into a "front-end"
database (containing forms, reports, queries, modules, and macros if you
use them) and a "back-end" database (containing just the tables). In a
split-database setup, the front-end contains links to the tables in the
back-end. Then each user has their own copy of the front-end, stored on
their own PC, with the tables linked to the back-end database, which
resides on a shared file server somewhere.

The split-database structure lets all users share the same data while
reducing the amount of network traffic and avoiding having one user lock
others out of the front-end by making some design change that requires
exclusive access. Incidentally, another advantage to having a split
database is that the developer can make modifications to the front-end
without disturbing or locking out the other users. When the
modifications are ready to be deployed, the new front-end can just be
copied to every user's PC and relinked if needed. With a split
database, you still only have to backup one file -- the back-end
database. The user's front-end databases are normally considered
disposable, as they contain no data, so you only have make sure you keep
a copy of the "master" front-end of which they are copies.

There is a Database Splitter wizard on the Tools -> Database Utilities
menu, that will split a database into front-end and back-end for you;
however, it's also quite easy to do manually.

It is possible, but unusual, for there to be multiple different back-end
databases, all linked from the same front-end. You might do that if
your total set of back-end data exceeds the 2GB capacity of the Access
..mdb file format, or if there is a logical partitioning to the data such
that it makes sense for there to be different databases to hold it.

It's also possible for the back-end database not to be an Access
database at all. For example, it's common for there to be Access
front-ends that are linked to SQL Server databases, or other
client/server databases. There are a number of reasons to do this,
including scalability, security, and reliability. It all depends on the
requirements your application has to meet.

 

[Guest]

Hi Ken,

Access stores all objects (tables, queries, forms, reports, DAP's, macros
and modules) in one file, so unless your database has been split, you should
only need to back up the one file. To determine whether you have any linked
data, select the Tables tab. If you see a small black arrow pointing to a
table name, then this indicates a shortcut; the table is not actually present
in the database. It is possible to link to one or more sources of data,
including .mdb, .xls, .txt, etc.

If you see any indications of linked tables, you can determine the path(s)
to all .mdb files with the following query:

SELECT Left(Database,255) AS [Linked Databases],
Count(MsysObjects.Database) AS [Number of Tables]
FROM MsysObjects
WHERE (MsysObjects.Type)<>9
GROUP BY Left(Database,255)
HAVING Left(Database,255) Is Not Null;

To use this SQL (Structured Query Language) statement, create a new query.
Dismiss the Add Tables dialog without adding any tables. Click on View > SQL
View. You should see the word "Select" highlighted. Use the backspace key to
clear this keyword. Copy the above SQL statement and paste it into the SQL
View. Run the query. If it returns any results, then you have linked .mdb
files that should be backed up as well. (You may have other file types, such
as .XLS or .TXT that will not show up with this query, however, the little
black arrows when viewing the tables will let you know that the objects are
linked).

Splitting a database is useful for several reasons. Here is a link that
discusses this in more detail:

Split the Database
http://www.access.qbuilt.com/html/gem_tips.html#SplitDB


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Ken,

I know this isn't the answer you were looking for but it depends.

Good database (db) development involves splitting the database which means
you create 2 parts to your db (the front-end - which house
queries,forms,reports,macros, code ; and the back-end - which houses your
data the tables). This said not all developers do this. Furthermore,
depending on your scenario, you could have several back-end or front-ends
working in conjunction. It is hard to tell just like that.

Obviously, based on the question, I assume you did not develop the db. Can
you not ask the person who did what should be backed-up?

Assuming not, this is what you should start by doing to determine if your db
has been split.

Here some code that will list each table and indicate the source
Function lst()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = DBEngine.Workspaces(0).Databases(0)
For Each tbl In db.TableDefs
If Left(tbl.Name, 4) <> "MSys" Then
Debug.Print tbl.Name; " Source: " & Mid$(tbl.Connect, InStr(1,
tbl.Connect, "=") + 1)
End If
Next
db.Close
End Function

If no source is listed it is because it is housed directly within the db
else it will list where the table is being housed. Simply Backup your
current db and any other dbs listed by the function