Is Access up to it?

[PhillPaige]

Hi All - firstly my compliments on what, in my view, is the definitive community for all us developers...

Now then - onto the problem - I am building a large (>400 Meg) database and have a lot of fields - I am at 150 fields at present before adding any calculated fields. I think the limit is 156? (Office 2k user at the moment - moving to XP soon)

so - is the limit 156 and if so is there any way around it??
Or can anyone recomment a very powerful, very fast, flexible and WIDE alternative to Access?

Many thanks
Phill

 

[LTunnicliffe]

Why not create more than one table? Is there any reason why all the data has to be in the same table?

 

[PhillPaige]

Unfortunately yes - I'm importing this data from an asc file which has all these fields semi-colon delimited in it

 

[Dugantrain]

Try to come up with some scheme of importing this into a huge Temp Table and then breaking these fields up into smaller, related tables (Append Queries, Update Queries, etc). That way you can get the full power of a relational database. If you're going to keep things in this format, you might as well just stick with Excel. By the way, research I've done seems to indicate that Access tends to break down when you get into the 100's of megs and has been known to start getting weird with as few as 20 megs. I'd go ahead and import this all into Access in one ugly table and then Compact and Repair just to see the "true" size that you're dealing with. I would imagine that you have a lot of repeating data that you can get rid of. You'd probably do well to snoop around and research more on Access's limitations before committing to anything, though.

Or can anyone recomment a very powerful, very fast, flexible and WIDE alternative to Access?

There's a ship-ton more powerful db's on the market, all much more expensive (SQL Server, Oracle, etc). I don't know one faster in terms of application development than Access, but all of the more powerful db's are faster in terms of speed. Access is pretty flexible; you can connect to just about any data source you can think of. Wide? What?

 

[mdmilner]

Why not consider using named ranges within the xls and automating the import process to target them instead. You could overlap the xls ranges with a planned primary key to allow you to make it a relational database without requiring you to break it all apart from a single massive table.

Mike