December 7, 2006 at 8:07 am
Not sure exactly where to post this but I am a newbie!
We're moving a project from all Access to Access frontend, SQL backend.
The above error happens in the following circumstances (in Access):
Enter an id number in a field, this is then used to open a form (via a macro) which filters the records down to those matching the entered id. The form doesn't open, just get that error.
The query will run by itself fine, and the form can be opened fine if we don't use the filter - all records are returned.
Also there are other search fields on the front form that work in exactly the same way but operate on different tables - these work fine.
I've found references to the error being a bug in SQL server when the Index Tuning Wizard has been run, but this isn't the case here. We've also tried DROP-ing Indexes and Statistics (and manually removing indexes for the affected table) and this doesn't work.
Also used sp_helpindex on the table, no 'hind_%' indexes. In fact currently no indexes as the drop worked fine.
The query isn't doing any order by, or table joins so there are no actual duplicate column names possible as far as I can see.
Sorry I'm not being very specific, I'm hoping someone has seen (and fixed) this kind of error before and can start pointing us in the right direction.
Thanks for your time,
James
December 7, 2006 at 1:23 pm
James - could you pl. post the actual query sent when the ID filter is applied !
Also, when you say that the query works "fine by itself" you mean in the form right ?! Have you tried running it in QA to see what results you get ?!
Is it always the same ID you use to test or have you been using different IDs ?!
What is the table ddl ?! Could you post some sample data - specifically with one of the IDs being used ?!
**ASCII stupid question, get a stupid ANSI !!!**
December 8, 2006 at 2:13 am
Hi, thanks for reply. Sorry I didn't post any of that stuff!!
Managed to track down the problem in the end - it was really stupid. Turns out the access form had a filter applied and this was clashing the with query used to filter the form from the front page. I didn't pick this up as the guy who built access system oringinally was in and working on that - I was just trying to track down the error he was getting. In the end I made him go through the form with me and found the extra filters! Seems access was fine with the situatyion (possibly for years!) but sql server is a bit more discliplined and didn't like it.
Sory to post a bit of a red herring but I was thrown off by google results - well maybe the company will finally pay for me to do some raining so I actually know something about the software we use instad of winging it 🙂
James
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply