Big Big database

[Aietoe]

Hi!

I have an Access97 database that begin to be very big and that refers to different applications. That database is used by many users at the same time.

Is there a way that i could split that database into 2 or 3 smaller databases and access these new databases from a main one thru a form;for example,
by clicking on a button on form-a of database-a,
- open database-b,
- execute query-b,
- get report-b
- return to database-a after closing report-b
- and so on.....

Hope it is clear....

Thanks in advance for your time and help.

Aietoe.

 

[dhookom]

You might want to quantify what you mean by big. Also, it is often the best practice to divide/split your application into a back-end mdb with just tables and a front-end with everything else. Is this how your application is set up? One front end can link to any number of back-ends.

Are you compacting your database regularly?

Duane
MS Access MVP

 

[dvannoy]

Yes you can split the DB into seperate DB's. but accessing these DB's through a "Master DB" will not imoprove performance that much. depending on how large your DB is you may want to consider SQL server. this will handle a much bigger load and is much more stable.

 

[Aietoe]

Hi Dhookom and Dvannoy

My database contains over 700 queries, 500 forms, 380 reports and over 240 macros.

I do utilise back-end mdb for my tables, and i compact my database regularly.

The reason for my request is not really to get a better performance but rather to make my maintenance easier.

I add queries, reports and macros almost every week, but the oldest applications(75% of the database)are stables and do not need to be modified.

So, i would be more secure to work in little databases then to constantly modify the big one.

Aietoe!

 

[dvannoy]

Man, if I were you I would move to SQL server..

700 queries 500 forms.. you are pushing access big time.

 

[dhookom]

I think you would be making your entire system much more complex by splitting the front end. That is unless you have features/objects that can clearly be separated into use by specific users.

Placing your data into SQL Server won't decrease the complexity of your application. It might help with multiple users and network traffic.

Duane
MS Access MVP

 

[psemianonymous]

If you are uncomfortable with VBA, then keep adding forms/queries/macros every week. Otherwise...


What you need to do is figure out why you're adding new objects every week. You should be able to use one form that is able to display data for this week, then when opened differently is able to display data for last week, or the week before, or last month, etc. Know that you can programmatically change the data source of a form at run-time (i.e. when you open it). Thus you can do code to check the "OpenArgs" parameter of the form, and if it finds something, change its recordsource. An example:

You have a form, let me call it the "Search form" which has two items: Begin Date and End Date. If the user fills in both properly, and clicks on the OK button, they are taken to the next form. This form is your combined 400 forms: it will open up with data *ONLY* from the dates entered. If no dates are entered, it will open with ALL of your data.


Thus you can reduce all your tables/queries/forms to just one of each, or at the least one of each discrete function you need to perform in this database. Access has so *many* built in features to handle this sort of thing, often giving you many ways to do the same thing. For forms and reports, you may switch the data source on the Open() event. You may also set the data source filter on the OpenReport/OpenForm commands. You may set filters after a form has loaded. You may use parameter queries which prompt for variables (such as beginning and end dates) every time they are opened. Check into this.

If anyone has a good example database of how this is done or some before/after database examples, feel free to post below.

I am in "agreeance" with the immediate above: no to SQL server and no to splitting the database. Access should not work significantly worse with 400 forms in a database than it would with 4.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm