Database Size?

[AZ001]

Hello Access Experts,
I'd really appreciate your opinion related to a problem I'm facing with MS Access. Please help me understand the problem and guide me toward a solution.
I run a MS Access database monthly and I have been facing different issues every month.
Here is some information about the database:
- Source information loaded in my database comes from a text file (550 MB, 670,000 rows & 100 columns) extracted from Peoplesoft.
- The Access database contains:
There are 38 tables:
One table that stores all data. (670,000 records and 150 columns)
And additional 34 tables used to map data. The biggest ones have
74,000, 7,000, and 11,000.
There are 70 queries and 30 queries are Update queries that update
the main table that contains all records.
This same database has 2 recorded macros. One of them runs all 26
update queries.
- The columns have no primary key or indexes or an identifier that makes the record unique.
- I also need to constantly compact and repair it.

Problem:
1) The macro takes 1:30 hours to run 26 update queries alone. It takes too long!
2) After this macro runs, some of the queries are successful and some are not but no error message is generated. Note: The same queries worked fine a few months ago.
Please help!


1) Do you think this could be related to the number of records in the database? It will continue to increase every month.
2) Is there a way to make this database more reliable?
3) Is there a way to make the macros run faster?
I'm sorry for the long note. I really appreciate your attention and time to help me.
King regards,

[June7]

Access has a 2 gig size limit, regardless of number of records.

34 tables that 'map' data? - no idea what this is about.

Yes, 1:30 hrs is a long time but without really understanding your data no way to know now to improve this.

Maybe Access is not the appropriate application for your situation.

[pontitt]

Altough I am not an expert, I agree that another database server might be optional for your queries. Try to use Sybase, Oracle, maybe MSOlap, it can turn your query 1/2 seconds to run.

[TheShabz]

I think the biggest issue is:
"One table that stores all data. (670,000 records and 150 columns)"

I can't fathom an occasion where a table that contains 150 fields is normalized. I don't think Access's purpose and your requirements are jiving with each other.