VBA to Import specific queries from dbs1 into dbs2 the currentDb

[EagleOne@microsoftdiscussiongroups]

2003

If I have "Query1" and "Query2" in dbs1 and I want the same queries in dbs2
(which happens to be my currentdb()), what is the VBA syntax to accomplish
the task?

TIA EagleOne

 

[Douglas J. Steele]

From within dbs2:

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Folder\dbs1.mdb", _
acQuery, "Query1", "Query1"

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Folder\dbs1.mdb", _
acQuery, "Query2", "Query2"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"

 

[EagleOne@microsoftdiscussiongroups]

Thank you Doug. (BTW, I accidentially switched dbs1 vs dbs2)

So my current database (at the moment of copy) is dbs1, which has the
queries. dbs2 is created by dbs1's VBA.

I had tried the following in the VBA module of dbs1:
dbs2.close
DoCmd.CopyObject myPath & dbs2.name, , acQuery, "CHOOSE_Add_Fields"

I get the error that the dbs2 is open and the code fails.

I'll try your method, but if you could, what am I doing wrong with my method?

TIA EagleOne

 

[Douglas J. Steele]

Why don't you try using the TransferDatabase method, like I suggested,
rather than have me try and debug your attempt to use a different approach?

From within dbs1:

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Folder\dbs2.mdb", _
acQuery, "Query1", "Query1"

DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Folder\dbs2.mdb", _
acQuery, "Query2", "Query2"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"

 

[EagleOne@microsoftdiscussiongroups]

Doug,

Either way (Export or Import), I still get "file is already open error"
whether I Export from dbs1 to dbs2, or if I Import into dbs2 from dbs1.

This is true even if I preceed the command with:
dbs2.Close

I even attempted to check the file name before and after the .close command
via
dbs2.name (which gives me the Path & Name
dbs2.Close
dbs2.name (fails because of no object)

Therefore I am confused by the file open error?

 

[EagleOne@microsoftdiscussiongroups]

Doug, this may help.

Even though I closed the file with VBA "dbs2.close" the VBA sub routine
still has a "handle" on the file.

How do I know that? If I F8 thorough the code, and stop then I get the
error (On_error_routine), so the code routine is still active, I cannot
delete dbs2 via windows explorer.

Therefore, the VBA module still has a handle on the file. How can I force
VBA to let go so that I can either import or export the Queries?

 

[Douglas J. Steele]

What's the rest of your code?

I suppose another way to go would be to use DAO to create a new query in
dbs2.

Dim dbOther As DAO.Database
Dim qdfHere As DAO.QueryDef
Dim qdfThere As DAO.QueryDef

Set dbOther = "C:\Folder\dbs2.mdb"
Set qdfHere = CurrentDb.QueryDef("Query1")
Set qdfThere = dbOther.CreateQueryDef("Query1", qdfHere.SQL)
qdfHere.Close
qdfThere.Close
Set qdfHere = CurrentDb.QueryDef("Query2")
Set qdfThere = dbOther.CreateQueryDef("Query2", qdfHere.SQL)
qdfHere.Close
qdfThere.Close
Set dbOther = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"

 

[EagleOne@microsoftdiscussiongroups]

Excellent new thought and it works! It adds to all OP's library.

BTW,

Here is what happened with the prior code:

The setup of dbs2 was/is:

Set appAccess = CreateObject("Access.Application")
appAccess.NewCurrentDatabase myPath & "dbs2"
Set dBs2 = appAccess.CurrentDb

To correct the "file in use error" I had to:

db2.close ' then
Set appAccess = Nothing

All was/is fine!

Thanks so much for your time and information

 

[Douglas J. Steele]

You needed

appAccess.CloseCurrentDatabase

before you set appAccess to nothing.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"

 

[EagleOne]

I learn a bit more every day!

Thanks