Slow split database on network

[riteoh]

I have a small business database that is accessed of two pc's in my home office. I have split the database into back end and front end, with the back end stored on a shared network drive.

Accessing the database is very slow, and to run a query from either pc can take several minutes. The database has been compacted, repaired, no change. It's also starting to get reasonably large (if you call 5 MB large).



Question - Should I persist, or would a better option be to convert it to MySQL and store it on my hosting service. What are the pros and cons of doing so, and does this limit my ability to run and write new queries in need? Can I use an Access front end (my hosting service cannot host access databases)

Thanks.

[June7]

I have split database on our network and no issue with speed. My backend is much larger than 5mb. Must be an issue with your network server.

Does each user open their own copy of frontend?

Access can connect to MySQL but that's all I know about MySQL.

[riteoh]

Hi June,

Yes - each user has their own frontend. Could it be slow network speeds through my router?

[June7]

I am inclined to think so.

If you want to provide fe/be files I will test. Follow instructions at bottom of my post.

[riteoh]

Thanks so much

[June7]

Which form/procedure should I focus on for test?

[riteoh]

Try any of the queries

[June7]

I am testing on my home pc, be/fe both in the same folder. Started at the top of the query list. The third query is taking forever! I finally just terminated it. So, the issue appears not to be your server.

Why does that query have copies of the Order table that are not joined to anything? Why is this a GROUP BY query with no aggregate calcs? I removed the GROUP BY and the 4 copies of Order table. Then the query opens immediately.

If those corrections don't fix your issue, I will test further on network server.

Can't open any of the 'Holy' queries - missing table.

[riteoh]

Thanks. I've also found it very slow to even add anything to the database. If it's not too much trouble, try adding a new cusotmer order from the switchboard - as soon as I start to add new details, the length of time it takes to add anything is frustratingly slow!

[June7]

Tested on network server. Speed not an issue. So, back to possibility it is your network.

Did you fix the query? Is that query used anywhere?