Queries not opening in design view

[Josh01]

Hi,

I have a couple of Access 2000 databases that are giving me trouble. When trying to open a query in design view,Access thinks about it then gives you full control back, and nothing has happened!! If you continue trying to view the query this error message appears

"The instruction at "0x77fcb892" referenced memory at "0x0043005". The memory could not be read"

I have searched to no avail for an answer around this problem, does anyone have any ideas???


Regards

Josh

 

[oharab]

It sounds like things are going a bit wrong internally!
I would suggest you import all your objects into a nice clean, new database file.
If that don't work, then you need to be thinking about the undocumented "decompile" feature.

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[burgess1975]

Josh,

Sounds like you might have some corruption in the DB file. I have a couple suggestions:

1) Have you tried to compact & repair the db?

If yes, but that didn't work:

2) Make a new, blank DB and import all of the objects from your troubled db into it. Many times, a corrupt DB that can't be fixed with compact/repair can be fixed by importing the object to a new db.

Hth,
Jeff

 

[Josh01]

all,

Thank you for your comments. I have tried to recreate the database but to no avail. I find that the same problems occur in the new database. I have got a few linked tables and that Access 2000 doesnt want to compact with these tables in. I have gone to the source of these tables and succesfully compacted and repaired all of them! I am quite interested in the undocumented decompile thing, is that when you fix the decompiler? I thought that was just for the VB code? How would i do that? I have one method but would be very interested to hear your ideas.


As you can see this database seems to be my nemesis!!

Cheers

Josh

 

[oharab]

Take a look at

http://www.mvps.org/access/bugs/bugs0008.htm

it's for A97, but I'm pretty sure it works for 2k as well.

Good luck

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[thornmastr]

The undocumented decompile switch works well with A97. it does nothing for A2K or A2K2. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Josh01]

Ben,

Thank you for that, sadly it doesnt seem to have made much difference. I believe that a process is kicked off in the background cause i can usually see multiple instances with task manager but doesnt display in the foreground.

Do you, or anyone, have any other ideas i can try???


Thank you

josh

 

[cheerio]

Is there any possibilty that your query is just stretching Access too far. I find complex queries that choke Access will often run perfectly happily on MS SQL Server.

It may be worthwhile for you to go to view SQL, copy the SQL code and paste it here for us to look at.

Of course your queries could be so simple this would be a waste of time but even simple queries can become corrupted.

Also if you are moving a problem query to a clean database then copying the SQL code rather than the query itself will force Access to reoptimise the query so you will not be bringing across hidden problems in the query design.

Ken

 

[Josh01]

Cheerio,

Thank you for your input, i dont believe that Access is being that stretched as it is quite a simple query but the code is below, believe me it wasnt easy to get!! Which is half the problem the db normally errors when you try to go to design view and so it is hard to even get at the SQL!!

UPDATE [SAP Fields ZPR0 Load Table] INNER JOIN [SAP_fields MAIN : Unvalidated Merge Source] ON [SAP Fields ZPR0 Load Table].Customer = [SAP_fields MAIN : Unvalidated Merge Source].Prev_Account_No SET [SAP Fields ZPR0 Load Table].Customer = IIf([SAP_fields MAIN : Unvalidated Merge Source]![New_Prev_Account_No] Is Null,[SAP Fields ZPR0 Load Table]![Customer],[SAP_fields MAIN : Unvalidated Merge Source]![New_Prev_Account_No]);

I will retry with a clean database and will do as suggested and copy the code over.

will let you know how it goes!!

Cheers

Josh

 

[Josh01]

Okay,

I have tried to recreate the database to no avail. It seems to be a problem with the linked tables in Access 2000. It seems that with the linked tables the database will not compact and i think that this is the core of the problem. My company are not running the full version of Access 2000, i believe anyway, so this may be a problem.

Would be interested in hearing any more ideas if any one has any.


Cheers

Josh

 

[cheerio]

Another idea.

If all the tables are on the server can you turn this into a pass through query? The code will then execute at the server and Access acts only as a messagebox. The server based query would be much quicker as there is no network traffic.

The only problem you will have is that Access SQL syntax is non-standard and the SQL may need a little tweaking to get it to work at the server end.

Ken

 

[Josh01]

Ken,

Unfortunatley it is only some of the tables that are on the server. As a work around I have written VB that grabs the table that was linked, copies the contents to a local table and then deletes the linked one. Seems to be working thus far so i think i will have to live with that.

Thanks for the help so far

Josh