MS Access Query returns different results dependin

问题:

I've run into problems before where Access behaved poorly with a split database where the table data was stored on a network drive. I work for a very large tech company, so I'm hoping all the IT stuff is done right.

I'm using Access 2010, and running Windows 7 enterprise, service pack 1.

Here's the issue today:

If I write a very simple query that asks for contract ID's, Contract Titles, Contract numbers, etc.... it all works fine. I can even add in the Abstract, which is of memo type, and might be 1000 characters or so.

Then things get hairy. Depending on what else I ask for in the query, the abstract either works, or gets changed into a single Chinese character. Same thing happens with a few other memo fields, some of which are under 100 characters.

If I copy the backend part of the database to my local drive, relink the tables, and run the exact same query, it works like a champ every single time.

It seems as if this is a data timeout issue or something like that. Anybody have any experience with this or know how to fix it? This isn't Web Access. BTW issue before was with a very large table update query - needed to make the backend local for it to work.

Should I just give up and change to SQL server? Are there known workarounds?

Thanks,

Grommit

回答1:

This is an Access - Jet bug.

http://allenbrowne.com/bug-18.html

When using totals, you can't use group by on the memo fields reliably. Not sure why it sometimes worked local and never across the network, maybe a timing thing.

Solution is to set the memo fields to "first".

Chris