Size of Database

[Guest]

How large can a database be?

As it stands, i'm fixing to make a database with 3 tables, a "record" table
which will hold one entry for each record.

there's a "data table" which will hold points of data for each record
(maybe 900 entrys for each record and there could be at least 15 fields in
this table)

and the third table is a "scheduel" table with at most 13 records for each
entry with 9 fields in this table.

There are about 60 entries per day, with data and scheduels, 6 days a week
kept on hand for say 30 days.

so there will be quite a few entries. Now, the main table will have a Key
linked to the other 2 tables. and i'll be running my forms/reports off of
queries.

now, will access be able to handle this much data in a single table? i made
a DB before, but each "record" had seperate data tables and it worked out
(although programming was a PITA, thus my 3 table approach.

any suggestions?

 

[Douglas J. Steele]

A single MDB file can be 2 Gb. However, if necessary, your front-end
database (which is supposed to contain the queries, forms, reports, macros
and modules) can be linked to multipl back-end databases.

That having been say, it sounds like you're saying that your "record" table
will need to contain 60*6*30 = 10,800 rows, which implies that the data
table will need to contain 9,720,000 rows, and the schedule table 140,400
rows. While I've seen tables in excess of 10 million rows, you do seem to be
running the risk of nearing the 2 Gb limit. You may want to consider using a
more robust DBMS (such as SQL Server or Oracle) for your back-end, rather
than keeping it in an MDB.

 

[Guest]

That's what i was thinking. But although the ammount of data we are taking,
the functionallity of the DB is very simple. spending time to create
front/back ends, might be too much. especially considering our DB knowledge
is quite limited.

/sigh

Thakns for the response.

 

[Douglas J. Steele]

As far as I'm concerned, all Access applications should be split, even if
they're single-user applications. Moving the back-end to another DBMS
shouldn't add that much complexity.

 

[Jeff Boyce]

I agree with Douglas, a "split" design is a great starting place, regardless
of the back-end.

I'll offer my experience moving JET data to a SQL-Server back-end ... there
were some approaches that worked quite well in a single user, single .mdb
approach that I had to revise for performance when I moved the data over.
Give it a try as split, but leave in some time for some "tuning".

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[aaron.kempf]

DOug

count the number of steps in setting up a linked SQL Server database,
including creating tables and writing connection strings

now do the same thing using Access Data Projects

your resolution is like 100 steps.

ADP = plug and play

 

[aaron.kempf]

re:
I agree with Douglas, a "split" design is a great starting place,
regardless
of the back-end.



IS SPLIT APPROPRIATE FOR A DATABASE UNDER DEVELOPMENT?

ROFL

GROW UP KIDS, ADP IS MUCH MUCH SIMPLER