Split table, now slow?

[Enviva]

Hi all

I was hoping to get a little advice as to my problem. I have a database with many, many reports, forms and queries. I wanted to split the database to secure the tables and important data. So I use the database splitter wizard and ever thing went just fine. So now my database1 looks into database1_be for all tables.

My problem is: my program now runs very slow. The slowest seems to be when I am adding a new record. I can live with the slowness of retrieving an existing record. I do not have a server nor can I afford one at this time, so currently I am running a peer-to-peer network with the database1_be located on one of my computers second hard drive.

Is there something I can do to help speed things up?
Thanks Enviva

 

[neileg]

If the computer with the BE file on it is being used for other tasks, it's bound to be slow.

It will be worse if your database is badly designed. For example, if you are entering data into a form based on a table, this is likely to be slower than a form based on a query, even if the data is identical.

Get hold of another PC, even an obsolete, slow machine, and add this to your P2P network. Use this as a server and you will gain considerably in performance. You don't need dedicated server operating systems.

 

[rhett7660]

Neileg..

Hello..

Just curious as to why the database would run slower if the form is based off a table not a query?

Thanks
R~

 

[Banana]

One more thing- if you have any lookup tables or other tables that has data that won't be changed and is used as a rowsource for listbox or comboboxes, you may want to move them to front end for faster performance.

As to your question, I think this is to do with how Access handles queries; when a query is made, Access also create a "plan" that links together the infromation (even if only one table is used) which makes performance significantly better than a table which is just that- a table.

 

[rhett7660]

Banana..

Thanks for the response. I did not know that... Just changed over my data source to a query...

Question: For the static tables, when you split the database it automaticaly takes all the tables and puts them on the back end.. How can you have them in the front end? If you move them will it interupt the link?

Thanks again...
R~

 

[Banana]

No.

BE shoiuld have all tables anyway, mainly to preserve the relationships among other things.

To move lookup tables to FE, use this

File -> Get External Data -> Import

and select tables from BE you want to move over to FE. Make sure to delete the links and ensure the tables name are exactly same.

 

[rhett7660]

Banana...

AHHH thank you very much for the info!!!

R~