Separating the database

G

Gary

I have a backend Database with just one main table in it (no form/queries
etc)
The network pc's have the front end database (with all the forms,
queries,macros etc). These front ends are linked to the table belonging to
the backend database.
Records have been entered since January this year. However, as soon as the
size of the backend database exceeded 1 gigabyte I got concerned that it
would possibly encounter a corruption of the data at some stage soon as
Access apparently "max's out" at 2 gig.
So I simply moved almost half the records and placed it into a seperate new
database - calling it ArchiveJan-Apr 05.
People on the network will still need to retrieve records in this archived
database. This seems like it will be a fixed procedure from now on...
creating new databases every couple of months to relieve the size of the
main backend database.
I don't want to be creating new front ends for all pc's everytime I create a
new archived database.
I know I could simply (in each existing front end) create a linked table to
the table of a newly created archived database but I don't know which macro
command to create.
But instead of opening the archived databases seperately, ideally a user
would press a command button from their existing front end database and then
it would access the appropriate linked table to the respective archived
database table(if that makes sense).
Or, is there a better solution ?
thanks for any assistance.
 
N

Nikos Yannacopoulos

Gary,

Your BE exceeding 1GB in a few months, with just one table, seems
suspicious... either your data is not normalized, so you have one flat
table with repeating data in many fields (and very heavy data creation
in terms of number of records created), or, more likely, you do not
compact your BE regularly! IMHO, undestanding why the BE is getting so
big and taking the appropriate measures to prevent this from happening
is the first thing to do, before looking at linking the FE to multiple
BE's; chances are you don't even need to archive data, at least not so
often.
So, look at BE compaction first. If that doesn't dramatically decrease
the size, then look at your data structure; if in doubt, post your table
design, and give us an idea of the number of records entered daily/monthly.

HTH,
Nikos
 
A

Arvin Meyer [MVP]

Unless you are creating the New York telephone book or storing images or
other OLE objects, your database shouldn't be that big. Have you ever
compacted it? (Tools ... Database Utilities ... Compact and Repair) The
backend is what needs compacting.

If it's still that large, I'd first consider moving it to SQL-Server. If
that's not an option, you can build a separate database for each archive and
link them all to the front-end. Build a form that asks the user which data
table to connect to. Say your tables are named Jan-Apr05, etc., using a
combobox to read the table names, your code would be something like (air
code):

Sub cboTableName_AfterUpdate()
If Len(Me.cboTableName)>0 Then
Me.Recordsource = Me.cboTableName
End If
End Sub

Have a look at this KB article to fill a combo box with table names:

http://support.microsoft.com/default.aspx?scid=kb;en-us;126946
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
G

Gary

Thanks for the assistance.
Below are all the fields in this one main table of the backend.
Its a database recording insurance claims - dealing in both insurance
companies (referer) and the insured (claimant) as well as site inspections
made by the inspectors (assessors).
There is no primary key. There are no child tables (as i am not that
familiar with them). I do compact the databases each day.
i am now thinking of adding another table with exactly the same fields that
deals with only private claims.

JobNo
JobCreatedDate
ClaimNo
InsuranceCo
ClaimantName
RiskStreetAddr
RiskSuburb
RiskPostcode
ClaimantPh1
ClaimantPh2
ClaimantPostalAddr
ClaimantPostalSuburb
ClaimantPostcode
ContactName
ContactPhone1
ContactPhone2
InitialContactDate
InitialContactTime
Assessor
InspDate
InspTime
InspComments
ReferrerComments
ClaimantComments
InspRptComplete
AssessmentRpt
TypeOfLoss
ScopeofWorks
EstimatedCost
OwnersRepairs
OwnersCost
ExcessAmount
Photo1 OLE(link only)
Photo2 OLE(link only)
Photo3 OLE(link only)
Photo4 OLE(link only)
AssmtRptSubmitted
RprRptSubmitted
Status
AuthorizDate
EstRepairsStart
RepairsStarted
RepairsCompleted
FaxedQuote
FaxedReport
BldType
BldTypeArea
DesignType
DesigntypeArea
ConstType
ConstAge
RoofType
AbGrndPool tick box
BBQArea tick box
BelGndPool tick box
BrickCarPort tick box
BrickGar tick box
BrickShed tick box
Bungalow tick box
CarPortMetal tick box
CubHouse tick box
DogKen tick box
ExtLaund tick box
ExtWC tick box
Fence tick box
FibroGar tick box
FibroShed tick box
GranFlat tick box
MetalGar tick box
MetalShed tick box
Pergola tick box
PoolRoom tick box
PumpRoom tick box
ScreenEncl tick box
StoreRoom tick box
TimberCarport tick box
PropertyCondAcceptable yes/no
InsRepReq yes/no
InsNotifProcess yes/no
InsAwareRepairs yes/no
TransferDate

Note: There is no repetition of addresses for the claimant as these are
individual residences that are almost never repeated during data entry of
claims.
 
N

Nikos Yannacopoulos

Gary,

This is definitely a non-normalized database. I wouldn't try to suggest
a design as I'm not familiar with how insurance works, and I don't
understand much from your field names, but I'm convinced you can do much
better. I would suggest you start with a Google search on "Entity
Relationship Model" and "Relational Database Design", so you can do some
reading on the fundamentals of designing a relational database, and then
try to revisit your design. A next step might be analyzing your existing
table with Tools > Analyze > Table; Access will come up with some
suggestions of how to break it up into a more normalized design. It's no
gospel, mind you, but it will get you started, and you can then improve
on it, having grasped the basic concept of normalizing.
On the other hand, it may be that some other respondent is more familiar
with insurance, and can give you something more to start with!

Using another table (the existing one or the new Claims table after
normalizing) of the same structure for private claims is not a good
idea. Using different tables with the same structure effectively
"stores" information in the table name, in a sense; the table a record
is found in tells you where it belongs, so this is a bit of data in
itself, only "stored" the wrong way. You should have on Claims table,
and use an extra field to differentiate private claims. If you will only
ever have private and non-private (or whatever) claims, then a yes/no
field is enough; if there may be more than two types of claims, then the
proper way to do it is have a claim types table with TypeID and type
description, have a TypeID field in your Claims table, and join the two
on the TypeID field, so you only store the TypeID in the Claims table.

Finally, you haven't told us how many records you add daily or monthly;
even with a table like this, 1GB after compaction sounds too much! Not
sure about the OLE fields...

Nikos
 
A

Arvin Meyer [MVP]

Gary,

You need to normalize this database. It has serious structure problems that
if fixed will allow you to use this database for many years. I'd suggest
looking for an Access book like:

http://www.amazon.com/exec/obidos/A...0/sr=2-3/ref=pd_bbs_b_2_3/102-5677388-5092960

Additionally, you need to store photos on the hard drive with a path to them
stored in the database. That alone will reduce the size of your database to
a tiny fraction of what you have. Have a look at:

http://www.datastrat.com/Download/Picture2K.zip

to see one method of doing that. If you must have them stored in the
database, (I highly recommend that you don't) at least store them
efficiently. Have a look at DBPix:

http://www.ammara.com

for 1 good method of doing so. Definitely do not add another table with the
same structure. That will only make a much bigger mess.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top