VBA CODE ACCESS

[jmoney30]

Hi,
I'm wondering if there is a way to export a query object from one ACCESS DB to multiple separate ACCESS DB via a loop. The only thing I can find is a docmd.transferdatbase but this transfers the data. I only want to transfer the created query without having to open up each individual database and saving the query because I have over 300 databases I would like to update. Or point me in the right direction because I've google this and not getting a hit.

Thanks

 

[jackd]

Are these 300 databases related in some manner? You may investigate Library Databases.

 

[welshgasman]

From copy objects between access databases vba access - Google Search

DoCmd.CopyObject method (Access)

Office VBA reference topic

learn.microsoft.com

 

[Micron]

I have to imagine that if the same query needs to be pushed to 300 databases something is not right. Care to elaborate on that?
It might be the way that 300 front ends are being updated for the same back end. Or it might be that there should not be 300 databases in the first place.

 

[jmoney30]

The other databases are somewhat related. I just want the other databases to all have the query or forms so they can access their data using the same query. The 300 databases have the same table structure, scheme and columns, but the values of the data are different. So the same query will work for everyone.

 

[Micron]

The 300 databases have the same table structure, scheme and columns,

If that's because they approach the 2GB limit and only contain data that's probably to be expected. If it's because they contain attachments, that's not the way to do it. If the structure is the same but it's because you have 300 users/departments that's not the way to do it either. If those db's are not split, that's not the way to do it either, but I can only imagine the notion of making 300 into 600 won't ever become a reality.

 

[jmoney30]

To the last comment, I don't need you to critique because you are off base on what I'm trying to do. All your Assumptions have nothing to do with why I want the query in the individual separate dbs. If someone can point me in the direction of how to do it great, if you want to give an answer without really giving an answer to just show how smart you are there are other forums where you can debate. All I'm asking if it is possible to put a query I created into, a bunch of other db's without opening each db, writing the query and then saving it. I"M NOT TRYING to export the results. I just want to save a WRITTEN QUERY in another db because doing it manually would take a long time.
Funny how access novice understood what I'm asking for and pointed me in the right direction and they humbly use novice in their handle name.

 

[jackd]

When you don't clearly describe the requirement and your environment, readers have to guess/anticipate what it is you are trying to accomplish. Based on their varied experiences they try to address whatever it is that prompted your post and provide some hints and practices they have developed over the years.
300 databases with the same structure would cause any database admin to question things. The first question would be "WHY"?
You could build an add-in, or you could create a Library database. But I don't see a means to put the same query into all 300 databases without opening each database.
You could build 1 consistent front end database with all the forms, queries etc that all users could use, then use that front end and some logic to link to tables in any of the 300 backends. But you haven't mentioned splitting your databases, so perhaps that would be the place to start.
Incidentally, part of responding to posts is to offer criticism - both positive and negative depending on the experience of the OP and the responder. Rest assured you can learn a lot about how not to do things and how to do things with Access based on the threads in various forums. We don't know your experience, your environment nor what exactly you are trying to solve.
Good luck with your project.

 

[jmoney30]

Gee whiz, I clearly describe what I'm looking to do. You are making it more difficult than it really is. The reason why I'm doing it is confidential. All I'm asking is HOW. Exactly you don't see why because that part isn't your business/confidential. The question is how to do it. Again, the person above who has less experience understood and pointed me in the right direction because it can be done. I don't mind criticism so take this criticism from me sometimes it's better to not over think something just to show how much you know because the question/answer is much simpler than you think. Especially when I saw the part about making 300 into 600 queries which is clearly not what I was asking to do, wanting do, or should it do! So, if a person explicitly asks how, don't get so caught up in the why unless a person EXPLICTLY tells you.
Thanks

 

[jackd]

I totally agree. I would adjust your statement a bit to
Before I can tell you HOW, I have to understand WHAT

This is from an old problem analysis lesson years ago

How do I get to your house? What is your current location/where are you now?

Anyway, glad you have a solution that works for you.