A unique problem With DoCmd.Output into Excel

[JITU]

hi

Would be glad if somebody could help me on this issue that been nagging a set a databases that we use.

The Access databases that we use are around 200 MB in size..
Its multiuser say maybe around 40-60 users.The database is split to enhance performance.

We use DoCmd.OutputTo ...statement to output the query into Excel sheet.

Example :

DoCmd.OutputTo acOutputQuery, "qryAllPrems", acFormatXLS, , False

The problem that we are facing is that the after the execution of the statment the query ( qryAllPrems) gets wiped out....i.e the entire sql in the query gets wiped out clean ..

It's driving us crazy as to what could be the cause of the problem...and I would appreciate it if somebody could help us..in this regard.

 

[boblarson]

Does each user have their OWN copy of the front end or are all of your users opening the same front end file on the server?

 

[JITU]

Thanks for the early reply Bob .

All of the users are opening the same front end file on the server
The shortcut is placed on their desktops.

 

[boblarson]

Thanks for the early reply Bob .

All of your users are opening the same front end file on the server
The shortcut is placed on their desktops.

That's what I was afraid of. I would highly suggest that each user have their OWN copy of the front end on their local machine so that you are less likely to suffer corruption. If anyone as the database front end open when a network disruption occurs (no matter how small) it can, and frequently does, corrupt the database that is being used.

If you give each user their own copy of the front end, it then will be only their copy that will become corrupt if that occurs. So you don't have to "touch" every desktop when updates occur to the front end, you can use my tool I created (did it for our work here and we haven't had any corruption issues with any of the databases we've implemented this procedure on, and it's been a year since we did so). The tool will enable Auto Updating of your Frontends so that if you make design changes to a master, the next time a user opens their front end, it will tell them it is out of date, close, delete the old file, copy the new one from your master location, and reopen it for them all automatically.

You can get it for free here:
http://www.access-programmers.co.uk/forums/showthread.php?t=111132

 

[JITU]

Thanks a lot for the reply ...will try it out .