# 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

jmoney30 said:


> 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.


----------



## 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


----------



## jmoney30

Wow, the What to do we can say is somewhat implied in the HOW to do. Funny how others understand the HOW to do and you're stuck on the WHAT to do.  Again, this was answered many comments ago and you're still trying to figure out the WHAT to do.
As far as your analogy, analogies just remind you of topic and are not substance to a topic.


----------



## Micron

OK jmoney30. I'll stop giving you _general_ advice since you are only focused on the immediate problem at hand. Sorry for trying to save you some work. Won't happen again because I don't need your insults. 


> just show how smart you are


and you are judgemental (I know of his experience fairly well, and he is quite knowledgeable)


> Again, *the person above* who has less experience


----------



## jmoney30

Oh boy, no need to apologize with a flimsily attempt to make yourself look more judicious by appearing to help me. I only asked a simple question and many people read way into it.


----------



## welshgasman

jmoney30 said:


> 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.


If that was addressed about me?

Please don't think, I think 300 databases are 'normal' ? , especially when they contain the same structure. 

I tend to adress what is being asked, and let you dig your own hole. 

300 databases would sound a wanrning flag to me as well, but then I know that ths thread will blossom to over 30 replies, trying to make you see sense. 

Easier to just hand you the spade.


----------



## welshgasman

I have now amended my signature.


----------



## jmoney30

Wow, is that what you took from that?  I was actually giving you a compliment because you pointed me in the right direction.  As far as over 300dbs, for confidential reason I can't say why there are so many. If I told you why there are so many. You would say oh, and then lay in your own referenced "metaphorical grave".


----------



## welshgasman

Not at all, again if that was addressed at me, just that my signature should be updated somewhat since I joined.

The main thing, is you hopefully have a solution.


----------



## jackd

"this was answered many comments ago"
Perhaps you could record your solution here to help someone else with similar situation.


----------



## jmoney30

Yes, he pointed me in the right direction. So far what I read it should work. I haven't coded it yet because working on other priorities This was some low hanging fruit, I wanted to take care of. Your statement would have more credence and seem well-intended if it was made many comments ago, sorry now it seems more of "toothless" attempt to call someone out.


----------

