No PK, FK or Saved Relationships, split database

[mjdemaris]

I split my database and performance dropped like a rock - and I'm the only user at the moment!

Some researching found info indicating that I can remove all the PKs, FKs, and saved relationships and use code to manage all this, that way Access does not load all the data to each user every time the front end is opened. This is reported to increase speed significantly.

Can I?
Should I?

Thanks,
Mike

 

[Ranman256]

No. That's not wise.

 

[pbaldy]

I haven't heard that one. I wouldn't do it either. Here are some thoughts:

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

 

[Simon_MT]

Also try persistancy - A file opened from the Front End eg A Menu Form hooked into a back-end table.

Simon

 

[pbaldy]

Also try persistancy - A file opened from the Front End eg A Menu Form hooked into a back-end table.

Sounds like the first suggestion in the link.

 

[The_Doc_Man]

I can remove all the PKs, FKs, and saved relationships and use code to manage all this

If everything has been converted to SQL queries that include all JOIN statements, then you are right that a "relationships" list is probably no longer necessary - until the next time you need to generate a new query, report, or form involving two or more related tables.

If you needed to consider relational integrity, you CANNOT get rid of PK/FK situations because you cannot create associated records so easily Access REQUIRES PK/FK relationships if you need RI. Unless, of course, you want to write your own code in an interpreted VBA module to do RI checking. But it is NOT for the faint of heart...

 

[mjdemaris]

Well, Gentlemen, thank you for your advice. The difference in time it takes to open some forms or run queries from the client is anywhere from 5+ to 60+ seconds, compared to a non-split database. I was excited about having a system that operated much faster than Excel, but such joy lasted mere seconds when I realized the truth.

It is still faster, and much more reliable. And, now that I'll be developing full time, in the very near future, I'll be experimenting more. I might consider more VBA/SQL.

Lastly, I would like to ask someone to take a look at what I've got. It'll take some time, but as a new developer amongst so many skilled ones, I would like some feed back.

Let me know, and I'll get a zipped copy out, minus any private info.

Cheers!
Mike

 

[pbaldy]

You can attach the db here if you want, and anyone so inclined can look at it:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

I wouldn't expect a split db to perform that differently from a non-split one, unless the back end has been put on a slow network. Try some of the suggestions in that link.

 

[Simon_MT]

There is a real performance with persistancy.

Another trick at the current project there are three main values Qty, Weight and Value, Index these and the system is much quicker as heavy analysis is done on them.

Simon