MS Access? Sizing Computer? Any other info you have for a project?

[amheck]

Hi group,

I have been givien the task to convert the editing of some unix flat files into an Access DB. In these flat files, we have approx. 35,000 lines, and each of these lines have 14 fields, delimited by a colon. So, busting out my calculator, I guess we have 35K * 14 = 490,000 "pieces" of data to keep track of.

I will want to have 4-5 users accessing this system at one time, each searching out and editing one of the 35,000 lines, also possibly deleting one of the lines, and also possibly adding a line of data.

I guess I'm wondering:

What size CPU/RAM/HDD would I need for this project?
Will there be any problem loading in the ascii flat files to Access?
Will Access handle this kind of project, or do I need to do something with SQL or Oracle?
Any good beginner books you guys recommend?

Thanks!
Aaron

 

[SQL]

From personal experience, 35K rows shared on a network is pushing it with Access. I've seen way too many projects based on the Jet database that basically suck performance wise when thrown on the network. A lot of this is due to poor design, but the Jet database isn't the greatest in the world.

For example, at work I have an Access DB that is over 1 GB in size. It works well, is shared by several people, but actually has only a couple thousand rows. I'm storing OLE objects in it which are huge when stored in Access. Just pointing out that Access can handle large databases, just not a very large number of rows.

You won't have no problem importing the file, Access is very flexible on importing data.

The multiple people in at the same time doing editing/updating/deleting isn't too much of a concern with Access. However, be prepared to get in the habit of running compact/repair operation on it regularly.

My suggestion - If you have a copy of Access, import the table and try it. Won't hurt anything. Unfortunately the step up to a SQL server is a large stepping stone.

 

[EagleKeeper]

You will need a copy of Access on each user system.

As SQL states, if Access can not cut it, step up to MS-SQL will import the Access tables and you can still use Access as a front end.

I have developed a system that had 20K+ rows/records and used Access for maintainence but a Windows App for the production user interface.

 

[Nothinman]

Access should be able to convert the file fine using : as a delimiter, as long as :s aren't use elsewhere in the data. Although I would avoid Access like the plague, the only thing it's good for is designing front ends to real database backends.

 

[AlricTheMad]

Access can handle 35,000 records. if it's a single table it shouldn't be a problem.

If you do a lot of work on it or have many indexes you may notice problems.

I work with one access database myself that currently has about 50,000 records in 15 to 20 fields. searches & sorting is taking longer and long with access.

Another issue to consider is the way Access works with the data. Everytime it needs to do anything with a table, once it is called the whole dataset is pulled across the network (thus the need for access on each PC)

If the work you are doing is simple you may be able to get by, if this has the potential to become a larger project consider a more robust solution.

MS SQL Server is one but there are licensing issues of course, same with Oracle & Informix.

You amy want to look at some open source solutions as well. There are ODBC clients for MySQL to allow you to use Access as a front end but connect to a real DBMS on the back end. MySQL also runs under Windows.
I haven't tried to really work with it myself yet but I have reached the limit of what I can do with Access.

As far as importing to Access. If you set up an Import spec (found from the 'advanced' button on the Import screen). I have a bit of code to run occasional imports on fixed width or delimited files where the spec has been saved. If you PM me I can send it to you.

Good luck

AlricTheMad

 

[BuckleDownBen]

I think Access can handle this. You'll probably only need a Pentium 450 with 128 MB ram. If the number of users double, then you will likely need to look into another solution. If you are at all proficient with Access, you should be able to load the data is a matter of minutes.

Access isn't the best solution but it is pretty convenient since everybody has Office.

 

[UThomas]

I manage a legacy Access database, and one part (archives we report off of) of it has a table with 1.4 million rows and 12 columns. It works well enough on a dual P3 866 with 1 Gig ram and a RAID 5 SCSI hard drive array. The archive database itself is aproaching 500 meg, and that is just 3 fat tables.

Thomas