The query cannot be completed (1 Viewer)

[Hello1]

I get this error/warning (check attachment) when I make an .accde from .accdb. It jumps after closing the .accdb or .accde when doing compact n repair (turned on to do it on exit). When I open the .accdb and close, nothing like this happens, nor for the .accde after its been made. They work normal, just no idea if there are going to be any problems later on.
I tried it on another computer and I didnt get the error..

 

[pbaldy]

There is no attachment.

 

[Hello1]

There is no attachment.

Sorry, fixed. Thanks

 

[pbaldy]

Hmm, I haven't seen that one. How big are the files? Might the query be that large?

 

[Hello1]

The .accdb is not even 10MB and I have no idea which query to check, however, I dont think any of the queries gives results that big to occupy 2GB

 

[theDBguy]

I agree. Sounds like it could be a file size issue.

Edit. Oops, too slow.

 

[theDBguy]

The .accdb is not even 10MB and I have no idea which query to check, however, I dont think any of the queries gives results that big to occupy 2GB

You said this happens when you do a C&R on exit, right? Would you happen to be opening the file from a network location?

 

[Hello1]

Some more information, tables in the .accdb are mostly linked tables. Some are linked to .mdb and most of them are linked to the SQL Server. Very few are actually in the .accdb which arent linked but made in .accdb but the size is quite small... mostly temporary tables (I write and remove from them)

 

[Hello1]

You said this happens when you do a C&R on exit, right? Would you happen to be opening the file from a network location?

Well, Im not quite sure. It happens when I go in the .accdb and do Save as .accde, this is where it actually happens..but in this process a C&R happens, yes.
However, it doesnt happen when I open the .accdb and just do a C&R or when I open .accde (after its been made) and do C&R, so only when doing Save As .accde

 

[theDBguy]

Well, Im not quite sure. It happens when I go in the .accdb and do Save as .accde, this is where it actually happens..but in this process a C&R happens, yes.
However, it doesnt happen when I open the .accdb and just do a C&R or when I open .accde (after its been made) and do C&R, so only when doing Save As .accde

And this happens on any machine? And you are doing that locally, right?

 

[Hello1]

No, not on any machine. It happens on the server but yes I do it locally on the server.
At my home machine it didnt happen.

 

[CJ_London]

It happens when I go in the .accdb and do Save as .accde

before trying to save as .accde, do you compile your code?

And do you have Option Explicit at the top of each module?

I suspect this error is more to do with attempting to compile the code for .accde. Perhaps your code uses a reserved word or has a broken library reference which is causing the compile to fail and generates a misleading error message.

I also recommend you do a decompile - if your code has had a lot of changes, there will be a lot garbage hidden away which might be causing a problem. See this link https://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

 

[theDBguy]

No, not on any machine. It happens on the server but yes I do it locally on the server.
At my home machine it didnt happen.

Just in case it matters, what are the specs (OS version, etc.) of the server?

 

[theDBguy]

Hi. Searching around the Net, looks like this is not a new issue. One of the questions asked is if the Perform Autocorrect option is not checked. Is it?

 

[Hello1]

before trying to save as .accde, do you compile your code?

And do you have Option Explicit at the top of each module?

I suspect this error is more to do with attempting to compile the code for .accde. Perhaps your code uses a reserved word or has a broken library reference which is causing the compile to fail and generates a misleading error message.

I also recommend you do a decompile - if your code has had a lot of changes, there will be a lot garbage hidden away which might be causing a problem. See this link https://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp

I do run this line with F5 "Set ProP = Db.CreateProperty("AllowBypassKey", dbBoolean, False)" before doing the Save As but I did forget to do compile after doing so, I usually dont and it works. I will try with it.

I do have Option Explicit in almost all modules, I found 3 reports without it. Class Objects are same as Modules?

I will try the decompile now.
Thanks

Just in case it matters, what are the specs (OS version, etc.) of the server?

Windows server 2012, 32GB of RAM and a decent processor i dont know exactly now.

 

[Hello1]

Hi. Searching around the Net, looks like this is not a new issue. One of the questions asked is if the Perform Autocorrect option is not checked. Is it?

Do you mean this? (attachment)
Thats how it currently is.

 

[theDBguy]

Hi. Thanks for posting the server specs. Since the problem doesn't happen on your home machine, this would be hard to troubleshoot. I am hoping someone might have a virtual machine to match your server and maybe get lucky duplicating the issue.

 

[theDBguy]

Do you mean this? (attachment)
Thats how it currently is.

Yes, that's correct. Just making sure... We need to rule out every little bit of suspicious settings.

 

[Hello1]

Very strange. I made the decompiled version and took the old version also. Tried to make the .accde few times from both versions and both sometimes finish without the error and sometimes with the error.. I would say 60% of the times the error occurs.
BTW its access 2010 and its not exactly the same version as mine on my home computer.

Edit: I tried it on my home PC, the exact same .accdb and not even once did the error show up.
Another interesting thing is, if I give a name to the .accde when doing the save as and remove the .accde in the name, it wont make the .accde at all, even though the file type is set to accde..

 

[theDBguy]

Funny thing is, I found a lot of posts with the same issue but none explained the actual source of the problem.