Pulling in a lot of info over network / speed issues

[bigmaxnosauce]

I have a split database, with the backend on a network drive. I'm havong two big issues, the first is with the speed of pulling up menus which are running locally, yet I see that access is pulling info from the network. Why does this happen????

Number 2 is when I edit over the network, it pulling up to 40MB at a time over the network when I finish editing a form. My DB is only 20-30 MB big so whats going on my IT guy says its access but with no explanation to why.

If any can help me solve these problems or better explain them to me I would be most appreciative.

 

[mission2java_78]

If its sitting over a network how is it pulling data locally? Access pulls this data across the network back onto the clients machine. As for 30-40 megs!!! Are you compacting your database?

Jon

 

[The_Doc_Man]

It is indeed possible for Access to send more data than it actually contains. Here is a scenario where that would happen.

1. Most of the data is contained in very few tables. Perhaps no more than one or two tables.

2. The recordsource that drives this form involves a JOIN query.

3. At least one JOIN is involved in a many-to-one relationship, with the bigger table on the ONE side but the driving force of the query is the MANY side.

4. Alternatively, the query is mis-formed such that it joins two tables but doesn't actually specify JOIN criteria. In which case you would get something sometimes called a "permutation join" of the two tables. It is a geometric product that could EASILY make the query return more records than are actually in the two tables.

Unfortunately, your network person is probably right that it is Access that is doing this to you. Access uses shared folders as file-shares, not application-shares. The LOCAL copy of Access is actually running the query based on remotely held data. You face a problem of having to read the entire set of tables involved at least once per query execution.