By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

access generates error when i open a query

9815402440
100+
P: 180
hi
i am using acess 2003 as back end of my application.
database was fine a few days back. i have a table named Ac_LedgerMaster.
i have several tables that are related to this table. now relations to a field in this table have reached the maximum limit i.e. i can no more relate this paricular field to any other table. but this database is still fine. after that i added 7 union queries. there are 3 other quereis that are drawing data from the 7 union queries.
now whenever i add a new table or query in the database one of the 3 queries gets currupted. when i double clik on a paricular query (out of aforementioned 3 quereis) then following error message is displayed
"Microsoft access database has encountred a problam and needs to be closed."
on clicking 'Dont Send' button database closes.

any minor change in that query solve the problam. i open it in design view. and replace double quotes with single quotes e.g. numAmount:val(numAmount & "") is replaced with numAmount:val(numAmount & '')

whenever i add a new query or alter an existing query any one of the 3 quereis gets corrupted. now i replace the single quotes with double quotes. it again solve the problam. but now a query is currupted again. i could not resolve the plroblam by all of my efforts.


please help
regards
manpreet singh dhillon hoshiarpur
Jan 1 '09 #1
Share this Question
Share on Google+
5 Replies


puppydogbuddy
Expert 100+
P: 1,923
assuming that numAmount is numeric, change this:
numAmount:val(numAmount & '')

To this:
numAmount:nz(numAmount,0)
Jan 1 '09 #2

nico5038
Expert 2.5K+
P: 3,072
Sounds like your database is corrupted, check:
Corrupt Microsoft Access MDBs FAQ

Personally I would start with importing all objects into a new database with File/Get external data/Import.

Nic;o)
Jan 1 '09 #3

9815402440
100+
P: 180
hi nico5038
i tried importing all objects in new database. when i run a query (only out of 3 queries mentioned above) at first it gives message 'Query is too complex'
when i double click again then same error (Send/Dont Send) reporting box occurs.
i unable to know why only one of these three queries gets corrupted.
please help
Jan 5 '09 #4

nico5038
Expert 2.5K+
P: 3,072
Can you create a small sample .mdb and add that zipped to your comment (click the Advanced button)
so I can have a look ?

Nic;o)
Jan 5 '09 #5

NeoPa
Expert Mod 15k+
P: 31,186
I had a problem similar to that once. I found the query was un-editable.

I got around it by taking the SQL out of the query (CurrentDB.QueryDefs("Query Name").SQL) and checking it manually. I fixed any problems I found then built up a string with the new version. This was then injected back into the query :
Expand|Select|Wrap|Line Numbers
  1. CurrentDB.QueryDefs("Query Name").SQL = strSQL
Hope this is some use.
Jan 6 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.