Multiple Queries to excel with Save As...

[BED]

Hello All!

I have a question, and I’ve done some research and I’m not sure its even possible, but you all on this forum have been amazing, and helped me with some pretty hard issues. (In my opinion =O)

The problem:

I have a macro that ends with 3 select queries showing the desired data.



Multiple users want to access this data, and possibly at the same time someone else has them open.

This results in an error saying (in basic terms) "Someone else is using this, try later)

My hypothetical solution:

Have all three queries exported in one excel document, under 3 tabs, with a prompt for Save As.

I’m not asking you guys to write all the code and such, I’m just wondering if it’s possible… With code/transfer spreadsheet…. Etc.

Let me know what you guys think, or if you need any more information.


Thank you in advance.

[TheShabz]

It's easier. Run the database splitter. Keep the backend on a shared drive and give each user their own FrontEnd on their desktop. They can all use those it at the same time.

[BED]

I wanted to avoid that, as there is 21 individual users, but that will solve my problem...

Thank you

[TheShabz]

The more the merrier. Just give each user their own front end.

[BED]

RIght on.

Sometimes i over complicate easy tasks. (im trying to force myself to learn VBA)

As far as updates go how does that work.

If i update the backend, will each of their front ends update as well?

[TheShabz]

It depends on the change. However, many admins always reissue new FEs just to make sure. There really isnt a drawback. You just put the FE out there and email your users to download it.

I'm not the foremost expert on splitting a DB but what I do is, before splitting, make a copy of the DB. I split the copy and issue out FEs. If i need to make a change. I will change the original, make a copy, split it, delete or archive the previous one, and replace the BE while issuing new FEs.

[BED]

Right on.

Ill play around with it and see how it works.

Im going to mark this as solved, but if i have another issue will you notice if i post here again??

Thank you for your help.

[TheShabz]

Yep. Email Updates ftw!