help with reining my big fat Access database 1

[Ashank4vba]

Hello,

Please advise regarding this situation.

There is a back-end Oracle database from which I am planning to save data into an Access database for around 30 considerably big tables (~100,000 records each). The total size of the access database comes to 1.99 GB. I have to save this data everyday.

Since, the access database is big (for standards of MS Access), it seems to crash frequently. So, I would like to know if this is worth doing it this way and if so, which is the best way. Here are some ways that I can think of. Please feel free to add to this though.

1. Deleting the data in each Access table and then adding data to the table from the linked table.

2. Using a 'SELECT * INTO' type query to delete the old table and create a new table with the saved data everytime.
(I'm currently using this way since it is simpler, but the Access database crashes too often).

3. Finding the changed/new records in the linked table somehow and then making just those changes to the existing Access tables.

Please suggest a suitable solution.
Thank you very much.

-cheers
Arvind

 

[RoyVidar]

The max limit of Access, is supposed to be 2GB, and from what I've read, a bit of corruption/weird behaviour isn't unusual when you're getting close to that - so I would try to think of other solutions than Access if 1.99 GB is the actual size.

Roy-Vidar

 

[mhcruella]

Part of a way around this (which isn't fun) is keeping some of your tables in the back-end. You can make two back end dbs and ave the tables linked into a front end. This will minimize the problems with one db growing too large. The front end will need to be compacted and repaired regularly.

 

[Ashank4vba]

Thanks Roy..I don't have the software or expertise for the other solutions though...

Thats a very good idea mhcruella...I will follow it ..Thanks a lot

 

[easyit]

Hi,

why don't you download MSDE from Microsoft? :

http://www.microsoft.com/downloads/...b5-0b07-4448-8848-dcc397514b41&displaylang=en

Should be easy to install & use, especially with Access.

EasyIT