Move query 1

[briglass]

Hello all!

Is there a way to move a query from one MDB to another MDB? It is a crosstab query.

Another solution for me would be to just move the DATA in the crosstab query from one MDB to the other, since they both have the same structure, it's just that one is empty.

I know this is not possible with SQL, but is there a way to use ADO or VBA?

Any help would be greatly appreciated! Thanks, (-:
Brian

 

[dpimental]

You can do a Transfer Database Macro / Procedure.

Try doing a Transfer Database Macro on your own. You specify which type of database object (table, query, ect) that you will import / export.

David I'm Your Huckleberry!

 

[briglass]

David-

Thanks for the reply. I don't think I made this clear with the first post, but I need to perform this operation from within Visual Basic. Is it possible to do this Transfer Database macro / procedure from within VB6?
Thanks, (-:
Brian

 

[JeremyNYC]

Brian,

There may be some simple way to copy an object across databases using code, though I don't know of it.

But it would be pretty simple to gather the sql property of the querydef object in the first mdb and create a query in the second mdb and assign that sql to it. Look into the sql property of a query def in help and you should be able to figure it out from there. If not, post back.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[foreveryoung]

manually - have both databases open and next to each other side by side then drag the query from the source database to the target database

Visual Basic - make a reference to your access database (Microsoft Access 9.0 Object Library) then enter the following code.

Dim app As Access.Application

Set app = New Access.Application

app.DoCmd.TransferDatabase acExport,........ see the syntax in the help for the exact parameters.

set app = nothing

Regards

David

 

[briglass]

David-

Thanks for the reply!

I can't seem to find anything about the TransferDatabase command... what is the acExport parameter? Is that a variable?

I'm a bit confused here. Thanks, (-:
Brian

 

[dpimental]

Brian, try this. But change the input / output query name, as weell as the full path and name of the output db.


Function transfer_query()
On Error GoTo transfer_query_Err

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:/My Documents/DBS/outputdb.mdb", acQuery, "myquery_qry", "mynewquery_qry", False


transfer_query_Exit:
Exit Function

transfer_query_Err:
MsgBox Error$
Resume transfer_query_Exit

End Function

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[briglass]

dpimental,

Thanks for the reply!

I am a little confused here, the reserved words docmd and transferdatabase (and apparantly acexport) do not automatically capitalize, and I get an error 424 at that line. (object required)

Also, I don't understand where I put the file location of the second database, since I am trying to move the query from one database to another (not within one database.)

Any ideas? Thanks, (-:
Brian

 

[dpimental]

1. open any visual basic module, and go to the menu, to the tools, references, and let me know what you see.

2. Here is the code I am using ...


DoCmd.TransferDatabase acExport, "Microsoft Access", "C:/My Documents/DBS/outputdb.mdb", acQuery, "myquery_qry", "mynewquery_qry", False


Here is an explanation:
"C:/My Documents/DBS/outputdb.mdb" This is the database that you are copying the query to. There is no need to specify the database you are copying from. It assumes that it is the database you are currently in.

"myquery" is the query you are copying.

"mynewquery" is the name that you want the query to have in the database you are copying it to.

Let me know if you need any more info.

David

 

[briglass]

Thanks! It worked! Thanks, (-:
Brian