Copy Access Query

bz · Joined: 04 Oct 2007 Posts: 55

Hi,

I am trying to copy a bunch of queries from one MDB to another MDB.
Would you guys show me how to do this with VB6 please?

Thanks!

Archived from group: microsoft>public>vb>enterprise

AMDRIT · Joined: 04 Oct 2007 Posts: 13

You can make use of DAO to do this.

The easiest way would be to loop over the QueryDefs collection.

It has been a while for me, but in essence it is like

dim qd as Querydef

for each qd in sourceDB.QueryDefs

dim newQD as QueryDef
set newQD = targetDB.CreateQueryDef

with newQD
.name = qd.Name
.source = qd.Source
end with

targetDB.queryDefs.add(newQD)

next

"bz" wrote in message @TK2MSFTNGP03.phx.gbl...
> Hi,
>
> I am trying to copy a bunch of queries from one MDB to another MDB.
> Would you guys show me how to do this with VB6 please?
>
> Thanks!
>
>

bz · Joined: 04 Oct 2007 Posts: 55

Thanks for the tip... but can I use ADO? I don't remember how to do DAO
anymore.

"AMDRIT" wrote in message @TK2MSFTNGP02.phx.gbl...
> You can make use of DAO to do this.
>
> The easiest way would be to loop over the QueryDefs collection.
>
> It has been a while for me, but in essence it is like
>
> dim qd as Querydef
>
> for each qd in sourceDB.QueryDefs
>
> dim newQD as QueryDef
> set newQD = targetDB.CreateQueryDef
>
> with newQD
> .name = qd.Name
> .source = qd.Source
> end with
>
> targetDB.queryDefs.add(newQD)
>
> next
>
>
> "bz" wrote in message
> @TK2MSFTNGP03.phx.gbl...
>> Hi,
>>
>> I am trying to copy a bunch of queries from one MDB to another MDB.
>> Would you guys show me how to do this with VB6 please?
>>
>> Thanks!
>>
>>
>
>

AMDRIT · Joined: 04 Oct 2007 Posts: 13

I don't believe you can, at least not laid out like I suggested, you would
have to work against the system tables in Access. Perhaps you could do
something with ADOX, but I don't know for sure.

There are still a lot of good reference material on DAO out on the web, the
choice is yours.

"bz" wrote in message @TK2MSFTNGP02.phx.gbl...
> Thanks for the tip... but can I use ADO? I don't remember how to do DAO
> anymore.
>
>
> "AMDRIT" wrote in message
> @TK2MSFTNGP02.phx.gbl...
>> You can make use of DAO to do this.
>>
>> The easiest way would be to loop over the QueryDefs collection.
>>
>> It has been a while for me, but in essence it is like
>>
>> dim qd as Querydef
>>
>> for each qd in sourceDB.QueryDefs
>>
>> dim newQD as QueryDef
>> set newQD = targetDB.CreateQueryDef
>>
>> with newQD
>> .name = qd.Name
>> .source = qd.Source
>> end with
>>
>> targetDB.queryDefs.add(newQD)
>>
>> next
>>
>>
>> "bz" wrote in message
>> @TK2MSFTNGP03.phx.gbl...
>>> Hi,
>>>
>>> I am trying to copy a bunch of queries from one MDB to another MDB.
>>> Would you guys show me how to do this with VB6 please?
>>>
>>> Thanks!
>>>
>>>
>>
>>
>
>

Douglas J. Steele · Joined: 04 Oct 2007 Posts: 80

The QueryDef object doesn't have a Source property. Perhaps you're thinking
of the SQL property:

With newQD
.Name = qd.Name
.SQL = qd.SQL
End With

Don't forget, too, that there could be pass-through queries. In that case,
you'd also want to copy the Connect property.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

"AMDRIT" wrote in message @TK2MSFTNGP02.phx.gbl...
> You can make use of DAO to do this.
>
> The easiest way would be to loop over the QueryDefs collection.
>
> It has been a while for me, but in essence it is like
>
> dim qd as Querydef
>
> for each qd in sourceDB.QueryDefs
>
> dim newQD as QueryDef
> set newQD = targetDB.CreateQueryDef
>
> with newQD
> .name = qd.Name
> .source = qd.Source
> end with
>
> targetDB.queryDefs.add(newQD)
>
> next
>
>
> "bz" wrote in message
> @TK2MSFTNGP03.phx.gbl...
>> Hi,
>>
>> I am trying to copy a bunch of queries from one MDB to another MDB.
>> Would you guys show me how to do this with VB6 please?
>>
>> Thanks!
>>
>>
>
>