Separating the database

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

 

[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

 

[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

 

[Joseph Meehan]

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.

I suggest you consider two things. First normalizing that database.
While you may have a normalized database grow that large, it is rather
unusual. More common is the lack of normalization. If you see a lot of
repeated information like addresses for all the employees of each company
then that address should not be part of the parent table, but should be in a
child table and each address would only appear once. Also if you are
recording a number of like events, like maybe office visits for a doctor
each in a different field, they need to be in their own child table so each
visit is a record not a field.

Next I did not see any reference to compacting your database. Try
running a repair and compact and see if it might shrink a great deal.

Note: normalizing is not something just for large databases, any size
database will work better and easier when properly normalized.

 

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

 

[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

 

[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

 

[lily]

¿¼ÊԳɹ¦£¡

 

[Joseph Meehan]

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

JobNo and ClaimNo Are they a 1:1 relationship or is one a subgroup of
the other? How are they related?

~~~~~~~~~~~~~~~~~~~
This section looks like it should have it's own table. You will need a
Claimant ID number that Access can use to connect the two tables back up.

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

Are all of the above different for each record, with the possible
exception of date and Assessor. BTW do you know that date and time are the
same in Access, you can record both in the same field You don't need two
fields, the difference is how you display it. After all a date is just a
measure of time.


~~~~~~~~~~~~~~~~~~~~~~~~~~~

Photo1 OLE(link only)
Photo2 OLE(link only)
Photo3 OLE(link only)
Photo4 OLE(link only)

These may be in their own table, not sure how important that might be
however in this case.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~