Huge database

Good afternoon, i have to make a huge database wich have to get data from 
excel file.That is not a big problem problem is that i have to get 
information for about 1000  families with 1000 elements for every family and 
this database have to storage this information at leaset one year. This make 
a huge pack of elements. Can Microsoft Access get so huge pack of data ? If 
yes i would be glad to read some ideas. 
Thanks
0
Utf
3/7/2008 2:53:02 PM
access 16762 articles. 2 followers. Follow

2 Replies
984 Views

Similar Articles

[PageSpeed] 15

If you are talking about 1000 columns in a table, Access can't do it. 255 is 
the upper limit. 

1,000 * 1,000 is potentially a million records. However if the data is 
properly normalized there could be much fewer.

I'd start by building a properly normalized database in Access. If you find 
that it doesn't have the power to handle your needs, you could upsize to SQL 
Server pretty easy.

If the word 'normalized' has you scratching your head, I highly recommend 
getting some relational database training or reading "Database Design for 
Mere Mortals" by Hernandez before proceeding on this database.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"gbogdanov" wrote:

> Good afternoon, i have to make a huge database wich have to get data from 
> excel file.That is not a big problem problem is that i have to get 
> information for about 1000  families with 1000 elements for every family and 
> this database have to storage this information at leaset one year. This make 
> a huge pack of elements. Can Microsoft Access get so huge pack of data ? If 
> yes i would be glad to read some ideas. 
> Thanks
0
Utf
3/7/2008 3:24:02 PM
On Fri, 7 Mar 2008 06:53:02 -0800, gbogdanov
<gbogdanov@discussions.microsoft.com> wrote:

>Good afternoon, i have to make a huge database wich have to get data from 
>excel file.That is not a big problem problem is that i have to get 
>information for about 1000  families with 1000 elements for every family and 
>this database have to storage this information at leaset one year. This make 
>a huge pack of elements. Can Microsoft Access get so huge pack of data ? If 
>yes i would be glad to read some ideas. 
>Thanks

What are the "elements"? As Jerry says, if they're columns in a spreadsheet,
you will need to restructure the data. "Fields are expensive, records are
cheap" - you will need two or more tall, thin tables such as

Families
 FamilyID
 <information about the family as a unit, maybe address>

People
 PersonID
 FamilyID <link to Families, what family is this person in>
 LastName
 FirstName
 <other biographical info>

Elements
 ElementID <primary key>
 Description <text, what is this element>

PersonElements
 ElementID <link to Elements, what element are you discussing>
 PersonID <link to People, who does this element apply to>
 <information about this person/this element>

In terms of size, even if you have a million intersections, it's comfortably
within what Access can handle with good design. You're limited by the total
size of the database - two billion bytes. That's a LOT more than you seem to
have.
-- 
             John W. Vinson [MVP]
0
John
3/7/2008 5:03:50 PM
Reply:

Similar Artilces:

Database Synchronization
I am looking for some details about.. a.How HQ DB and Store DB is synchronized? b.How cross DB queries is done on worksheets?(need help more) I can work with OPENROWSET queries.but i think this way is not implemented on QS data. Can anyone help me in this regard?I really need help.. ...

SQL error as i restored the database
there is an error happened when i restored the original database. The hand under the MSCRM and Metabase disappeared when i finished restoring the database, which means the replication does not work any more. Also, i can't log in to CRM server any more even the admin user. any one get the idea to restore the database in the right way which the hand under both database will not disappear or how to backup the database correctly? thanks When backing up CRM databases you should ensure the CRM services are stopped. Are you restoring to the same machine in the same domain? The followi...

Unsure of database design
Hello all, I hope you can help me! I'm reasonably new to Access having learnt how to use it and VB mostly through trial and error! I'm now trying to set up a new database which will lookup conditions in animals following a search based on symptoms, breeds, age etc. What I'm getting stuck on is the table design for my database. For each of the conditions there may be 5 or 6 clinical signs, several breeds and so on. What is the best way of structuring this? Should I have one big table with maybe eight "Clinical Signs" fields (Clinical Sign 1, Clinical Sign 2 etc....) ...

Huge file size
I have a excel file that has all of a sudden grown to a huge size 19,278,336 . It is stock market data with a worksheet for each year of data (1968 to 2004). So 35 worksheets. For years it has always run about 1.8 meg. Just this year it has all of a sudden grown with no real difference in the data I add each day. Each row has 9 columns of data advances, declines, etc. There are 21 columns that make calculations based on the preceding 9 columns. But years 1968 - 2003 just have data, not formulas. I converted the cells to numbers from formulas. And I had several charts, but I de...

Cannot attach database that MS supplied.
I downloaded and installed the MS SQL report pack for v1.2. It comes with the ..MDF for the Adventure Works Cycle reports that are demo reports. The instructions say to attach the database but I keep getting "Error 1813" Microsoft SQL-DMO (ODBC SQLState:42000) Error 1813: Could not open new database 'name'. CREATE DATABASE is aborted. Device activation error. The physical file name 'c:\program files\..\name_log.ldf' may be incorrect. attaching database has failed I also tried using "sp_attach_single_file_db" in query analyzer but got the sam...

Moving Database location
I am trying to move my priv1.edb or priv1.stm file to a different drive in exchange 2000. I have been successful in moving the public and logs, but everytime I move the priv1, the exchange system manager stops responding. It will copy the file to the new location, but remounts the old files. Thanks for andy suggestions. what do you mean "the old files"? the process should "move" the priv2.edb...are you certain you're waiting long enough for the move to complete? "joss" <joss@discussions.microsoft.com> wrote in message news:2152A9CF-AF19-42BB-804...

A Huge Opportunity . . .
Hello. On April 1, 2007, a new concept in Computer Support will be rolled out across the United States and Europe. We believe that PC-VIP will turn the market for support services to the SMB space <i>ON IT'S EAR</i>. We are looking for qualified support professionals to act as field personnel in New York City, Los Angeles, San Francisco, Chicago, Miami, Austin, Seattle, and Washington DC, as well as in Paris France and Brussels, Belgium, immediately. The money is big, and no kidding, this will be so big that it's entirely likely that anyone who isn't "with us&qu...

clean up database
We have finished customising the CRM system but have a lot of test information in the databse - is it possible to remove the test data but keep the customised fields, workflows etc... in place - start with a blank db but with all out settings intact? If anyone knows, we would appreciate it if you could share how with us. Thanks, John Finnigan The only way to accomplish this would be to manually delete records (unsupported) from the associated tables. -- Matt Parks MVP - Microsoft CRM "JohnF" <JohnF@discussions.microsoft.com> wrote in message news:6BDC1424-8103-447...

new database location question
I am looking into moving our CRM database to a more powerful server in the same domain. When I read through the disaster recovery on how to restore the CRM databases, one of the steps is to restore the master DB. Do I have to back the master database up on the old server, and restore it on my fresh install of SQL Server? I guess the same question goes for the msdb database. Well, I moved my databases and I can see my data no problem. The next question is how do I re-enable replication? The implementation guide says that just pointing CRM at the new server will enable it, but when I look in n...

How do I set up a daily activities journal database?
I want to setup a database that serves as a log of daily activity. Anyone have this type of database already setup? On Thu, 9 Aug 2007 05:48:03 -0700, wtyree <wtyree@discussions.microsoft.com> wrote: >I want to setup a database that serves as a log of daily activity. Anyone >have this type of database already setup? If you have Access, you almost surely have Outlook. How about using the builtin Journal feature in that program? If you do want to roll your own, you'll have to decide what "activity" you want to record and how. It might be as simple as a table wit...

Could not establish SQL database connection using GPConnNET assembly
Hi, When deploying my DexToNet Application, I'm getting the following error whenever I open any of my custom forms: Could not establish SQL database connection using GPConnNET assembly My forms are using the existing GP connection set to Company.Default. Any idea or suggestion on where should I look for a solution? Is this a problem of the Data Source? Thanks! ...

Redesign of the database
Not sure if this is possible as it is based on an incident that was just a bug in the application. But due to the bug cleaning the data is very difficult. The suggestion is to redesign the CRM database structure such that when a bug like this happens, fixing broken data will not be a complicated task. (see Incident 8734534 for all the details) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow...

Huge file size
If I add an image to my Pub 2002 publication, the file size increases (that's normal)--but if I remove the image, the file size does NOT decrease---it remains the same. Apparently, it is cumulative---right now I am working with a Godzilla 339MB file size. Even if I remove all of my images from the publication, and I have nothing but text, the file size remains huge. Can anything be done to decrease the file size of an existing publication? Dave Block Tech Writer Long Island, NY Dave Block <DaveBlock@discussions.microsoft.com> was very recently heard to utter: > If I add ...

Link to database on intranet
I'm in the middle of converting all of our databases from Access 97 to XP. On our intranet, we have "links" to databases in 97. I can open the database from a shortcut with command lines. I am however unable to the command line options with the link. Just some additional information: I'm using IE 6.0, dual environment with Access 97 & XP, Windows 98, and FrontPage. Can someone help please? I've searched and I can't find a solution. Just a point in the right direction would be greatly appreciated. Thank you! Kathy ...

What is an .swp file? And why is it so huge?
131,072kb And is it a friend or foe? It's a file that Windows creates. Think of it as a way to leave files out on the desk so you can find them easier. When you reboot, the file will shrink, then grow again as you work. In other words, it's normal. Melissa "Carol" <carol@willapabay.org> wrote in message news:129cf01c3f6a8$b3e2f500$a001280a@phx.gbl... > 131,072kb > > And is it a friend or foe? Carol wrote: > 131,072kb > > And is it a friend or foe? What has this to do with Publisher?! If a file is large enough to exhaust your computer'...

corrupt database
any idea on how to recover a corrupt database? eseutil, isinteg and exmerge wont work Is there a previous backed up database in good condition where transaction logs can be replayed. Try this article for a brief overview http://www.microsoft.com/technet/prodtechnol/exchange/guides/UseE2k3RecStorGrps/3534bd97-7eda-4a81-ba4f-d8d48bd16cc0.mspx "bankie" <bankie@discussions.microsoft.com> wrote in message news:DCFF1234-B4F0-4883-952B-B07F10133CFE@microsoft.com... > any idea on how to recover a corrupt database? > eseutil, isinteg and exmerge wont work ...

An exception is thrown when my Access 2002 database file size reaches 1 GB
Hi guys The case is as follows: Convert our MS Access 97 database template to an MS Access 2002 template and test that an exception is not thrown when the database reaches 1 GB. The file size limit for MS Access 2002 database is 2 GB. Test environment: Clean computer with XP SP 1 MS JET 4.0 SP7 MDAC 2.8 Our DAO application writing data to our converted database. (The DAO application is written in VC++, unmanaged code). After some failing tests also Visual Studio.NET was installed, but that did not help. When the database is filled with records an reaches 1 GB an exception is thrown from t...

Huge Queue
I've noticed that our Exchange Server 2000 for a small organization (less than 5 clients) has a huge, long list in the SMTP queue when I look at it using Administrator. When I click on Queues, it takes about 10 minutes for the queue to finally show on the screen (I'm guessing we end up with a list in the thousands). Is this normal? If not, how do I eliminate this long queue. If I make the assumption that all users email has been delivered, I would also assume that nothing should be in the queue (and that therefore we should be able to delete anything in the queue), correct? Thanks...

Stop a user from opening an already-open database
Hello! Any way so that when the database is use, if a second user tries to access it, they get a message that says "The database is already being used by User_______. Please try again later" and then the database does not open. I don't want more than one user at a time, and I would like the 2nd user to be able to pointpoint who the other user is so they can talk. Thanks! VR/Lost One approach would be to have a table to which users write when they open the database. http://support.microsoft.com/kb/285822/ shows how to read the ldb file to determine who'...

MY Tasks page not in sync with project server database
Hi, We are using Project server 2007 with Sp2 Project web access started behaving abnormally.. all the tasks which are closed some time in 2009 and early 2010 are suddenly marked as not done in project web access, "My Tasks" Page of many users. We verified status of these tasks in the Project plan and database, but they are reflecting actual values. It looks like there is some issue with the sycronization part. Could anyone help us with some information on this issue. -- Ramesh ...

Working with huge cells
I'm pseudo-using Excel as a database with some huge cells such as 150 lines including LFs (I stripped the CRs). I'd like to see as much as possible, navigate with the keyboard, and (hey, I can dream) if possible find and land cursor on a search term (ideally repeating with a button). Highlighting them would be the final victory - or at least enlarging or emboldening - even if I could only do one at a time. At a comfortable font I can see about 30 lines in the auto-expanding Formula Bar while selecting the unwrapped cell so that's a start. It looks clean with the LFs. I can then cl...

Databases
Is there any way to create a database with Excel?? I currently use Pc-File to sort and print the contacts to make label and other reports but I would like to switch to Excel if I can. Is it possible?? Thanks Cadman2 ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements In Excel, any list of similar values* is treated like a database. It is a flat-file database (not relational), but...

Battery huge
With Lithium-ion battery I get the values listed here below during discharge. Any suggest in how to compute this curve to supply a reasonable-good battery level percentage? (i.e. I want that 60% of charge means an estimated remaining duration time of 60%) Also the data below are obtained testing a new battery: have I to consider tests after 10-100-1000 discharge cycles, in other words have I to consider the average of discharge curves tested in different conditions in your opinion? T.I.A., Massimo 1023,5 (full charge) 1023,5 1023,5 1023,5 1023,5 1023,5 1023,5 1023...

FRX access to Databases other than Dynamics GP
I was wandering there is a way for FRX to access databases (which is running on SQl2005) other than Dynamics GP? Please advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?...

How to: Change Exchange Databases
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3BD99.83BEC770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I want to change databases (.edb and .stm) from the default to an older = copied database. =20 Here is what happened. Instead of doing a regular backup, I just copied = over the Exchvr (spelling) folder from the system drive. After a format = and re-install, I found the location of the old Public info store files = and tried to mount them. I can create a new admin store and manipulate = the location ...