Advice on Access DB Setup... 2

[Waxaholic]

I have a data source which outputs about 70 Tables worth of unique data hourly. I am working on a front-end that will allow access to this data. I want to be able to view the data in hourly increments as well as 24 hour, Monthly, Quarterly, and Semi-Annual increments. My question is...

What would be the most efficient way to store the data within access while allowing easy querying of the data from seperate tables/sources through SQL Queries?

1. Should each of the 70 Tables be stored in 1 Access db with new hourly data appended to it?

2. Should all 70 Tables be stored in a new Access db every hour named according to Source, Date, HourMin?

3. Should each of the 70 Tables have its own Access db with hourly data written to Tables within according to Source, Date, HourMin?

Hope that makes sense. The front-end will most likely be built in .NET and retrieve the data from one of the above scenarios. What will be the best approach to this problem? If you have a better idea please share.

Thank you,

Brian

 

[JeremyNYC]

Put everything in one database, no question. There's no reason to do anything different. You're going to have to do plenty of work to make sure your appends are stepping on your users' toes, and that they're happening correctly. Do the appends have to happen every hour (do you need that much concurrency)?

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Waxaholic]

The data is for network performance monitoring so i need every single hour available. Of course a date-time stamp would be in each record to seperate them. All 70 tables together amount to about 2.5megs while in Access per hour. I was concerned this might pose a problem with the db growing too large within a matter of days. Not to mention the performance hit it might cause when trying to access this data.

 

[JeremyNYC]

Youch! That's a lot of ones and zeros. I would have to say that I'm out of league in advising you with a data set that large. Can this not be done with a back end other than Access? I would certainly want to use something bigger than Access for the back end of this.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Waxaholic]

What would you recommend? I am still in the design phase as far as storing this data, so i am open to suggestion on how to handle it.

 

[dvannoy]

I currently do something a bit similar to this now. But am converting to SQL 2000 as we speak. I pull down data from an AS400 every 15min and the data goes into only 5 tables. Not as much as you would be doing. but I will tell you, you will be very pissed when you have to deal with table corruption due to access(jet Engine) not be able to handle that many appends at once. not to mention that you will have a size limitation with access. .Net front end is by far the way to go. with a SQL or Oracle back end you'll be pleased with the results. using ADO.NET OLEDB is the best way for you to go.

Hope this helps

 

[Waxaholic]

This is the way i will proceed then. I will have to investigate the SQL versions though. I want to keep this as simple as possible. A small SQL server footprint is what i want. Have you had any experience with MySQL as a server. I have SQL2000 Installed now on my machine. Guess i will try both to see which one fits my needs. Free (MySQL) is always good .

I want to thank both JeremyNYC and dvannoy for the excellent advice here.

Brian

 

[kenadamskc]

If you have Office 2000 Pro or higher you might look into using MSDE 2000 that is included (I do not believe it is installed by default). Essentially, it is the SQL Server 2000 engine (no UI). Then you could use Access Project as your front end. Be aware though, there is a size limit of 2 GB on each Access Project database.

 

[Waxaholic]

Appreciate the advice. I will indeed check out this and other versions of SQL. Thanks again.